Results 1 to 4 of 4

Thread: DLookup issue!

  1. #1
    Join Date
    Mar 2002
    Posts
    2

    Question Unanswered: DLookup issue!

    I need help with DLookups. I have an order form (called Order Details Subform) on which I select a product (from a combo box) and then a contract type for that product (1,2,3 or 4). The category determines how the product is priced, either monthly, annually or every two years. When I select the category from the combo box I want the pricing fields in the form to update automatically based on the product ID and the contract type. I have been using a series of DLookups behind the contract type combo box that look like this:

    Private Sub ContractType_AfterUpdate()
    If Me![ContractTypeID] = 1 Then
    Me![MonthlyFee] = DLookup("[ProductMonthlyListPrice]", "[Products]", "[ProductID]= " & Forms![Order Details Subform]![ProductID])
    Me![OneYear] = 0
    Me![TwoYear] = 0
    Else
    Me![MonthlyFee] = 0
    End If

    If Me![ContractTypeID] = 2 Then
    Me![MonthlyFee] = 0
    Me![OneYear] = DLookup("ProductOneYearListPrice", "Products", "ProductID = " & [ProductID])
    Me![TwoYear] = 0
    Else
    Me![OneYear] = 0
    End If

    If Me![ContractTypeID] = 3 Then
    Me![MonthlyFee] = 0
    Me![OneYear] = 0
    Me![TwoYear] = DLookup("ProductTwoYearListPrice", "Products", "ProductID = " & [ProductID])
    Else
    Me![TwoYear] = 0
    End If

    If Me![ContractTypeID] = 4 Then
    Me![MonthlyFee] = 0
    Me![OneYear] = 0
    Me![TwoYear] = 0
    End If


    End Sub

    The problem is, the pricing boxes (monthly fee, one year and two year) don't populate individually. Say I choose product a that is contract type b. It should be priced monthly at 6,000 per month. That shows in that record. However, all the rest of the records on the form also show $6000 monthly. When a subsequent record is changed, all the previous records change to that amount also. I am sure there is an easy way to do this - I hope someone can help me. Thanks!

  2. #2
    Join Date
    Oct 2001
    Location
    Bay Area, CA
    Posts
    117
    I suggest you throw in a couple of variables so you can debug your code better.

    dim curListPrice as Currency
    dim intType as integer

    curListPrice = DLookup ("[ProductMonthlyListPrice]", "[Products]", "[ProductID]= " & Forms![Order Details Subform]![ProductID])

    intType = me.ContractTypeID
    ' reset all values first
    me.MonthlyFee = 0
    me.OneYear = 0
    me.TwoYear = 0

    Select Case intType
    Case 1
    Me!MonthlyFee = curListPrice
    Case 2
    Me!OneYear = curListPrice
    Case 3
    Me!TwoYear = curListPrice
    End Select


    Hope this helps....

  3. #3
    Join Date
    Mar 2002
    Posts
    2

    Thank you.

    I will try that. Thank you for helping an admitted coding neophyte.

    richake


    Originally posted by Torgue
    I suggest you throw in a couple of variables so you can debug your code better.

    dim curListPrice as Currency
    dim intType as integer

    curListPrice = DLookup ("[ProductMonthlyListPrice]", "[Products]", "[ProductID]= " & Forms![Order Details Subform]![ProductID])

    intType = me.ContractTypeID
    ' reset all values first
    me.MonthlyFee = 0
    me.OneYear = 0
    me.TwoYear = 0

    Select Case intType
    Case 1
    Me!MonthlyFee = curListPrice
    Case 2
    Me!OneYear = curListPrice
    Case 3
    Me!TwoYear = curListPrice
    End Select


    Hope this helps....

  4. #4
    Join Date
    Sep 2001
    Location
    Chicago, Illinois, USA
    Posts
    601
    Richake,

    With some careful design, logic like this can be simplified tremendously. You may already be beyond this point, but if you are still in the early stages of your desing, here is what I suggest.

    In the combobox where you select the product, bring all three prices (monthly, annual and bi-annual) along in the rowsource query. You don't have to display these columns (set their column width properties to 0"), but bring them along anyway. By doing this you don't have to do any DLookUps to get your pricing information. Even though you are returning all these pricing values, it is still faster that using DLookUps each time you select a product from the combobox.

    Now, the rowsource for the pricing category combobox should be a two-column value list with the first column being the description and the second, bound column being the number associated with the pricing type:

    Code:
    Monthly    1
    Annual     2
    Bi-annual  3
    The number should be in the same order as the data is returned in the query which is the rowsource for the product combobox.

    Now, on the AfterUpdate event of the category combobox, simply assign the price to your price field using the following assignment statement.

    [Price]= [PriceCombobox].column([Category])

    That's it. By carefully using the power of the Access combobox control we've reduced this task to a single line of code.

    Good luck.

    Ken

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •