Results 1 to 6 of 6
  1. #1
    2 Star Lounger
    Join Date
    Jan 2001
    Location
    Pennsylvania, USA
    Posts
    144
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Just need code checked (97,2000,2002)

    Below is code that I am useing to populate the field in a table, I know that I have something wrong or out of place. Just not seeing where.

    ItemNumber is a Text field

    Option Compare Database
    Option Explicit

    Private Sub txtItemNumber_AfterUpdate()
    Dim strFilter As String

    strFilter = "ItemNumber =" & Me!ItemNumber
    'Lookup Item Retail Price and assign it to the RetailPrice Control.
    Me!RetailPrice = DLookup("RetailPrice", "tblInventory", "ItemNumber=" & "ItemNumber")
    'Lookup Item retail discount and assign it to the RetailDisc Control.
    Me!RetailDisc = DLookup("RetailDisc", "tblInventory", "ItemNumber=" & "ItemNumber")
    'Lookup Item Supplier Cost and assign it to the SalesCost Control.
    Me!SalesCost = DLookup("SuplirCost", "tblInventorySupplier", "ItemNumber=" & "ItemNumber")
    'Lookup Item SalesAccount and assign it to the GLAccount Control.
    Me!GlAccount = DLookup("SalesAccount", "tblInventory", "ItemNumber=" & "ItemNumber")

    Exit_ItemNumber_AfterUpdate:
    Exit Sub

    End Sub

    Thank You
    threecrow

    Don't make excuses. It's what you do, not why you didn't.

  2. #2
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts

    Re: Just need code checked (97,2000,2002)

    When you refer to a text field in a criterion for a lookup, you need to enclose it in quotes. Chr(34) is the code for double quotes.

    Having declared strfilter and set its value, you might as well use it.

    so try

    strFilter = "ItemNumber =" & chr(34) & Me!ItemNumber & CHR(34)
    'Lookup Item Retail Price and assign it to the RetailPrice Control.
    Me!RetailPrice = DLookup("RetailPrice", "tblInventory", strFilter )
    'Lookup Item retail discount and assign it to the RetailDisc Control.
    Me!RetailDisc = DLookup("RetailDisc", "tblInventory", strFilter )
    'Lookup Item Supplier Cost and assign it to the SalesCost Control.
    Me!SalesCost = DLookup("SuplirCost", "tblInventorySupplier", strFilter )
    'Lookup Item SalesAccount and assign it to the GLAccount Control.
    Me!GlAccount = DLookup("SalesAccount", "tblInventory", strFilter )
    Regards
    John



  3. #3
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,430
    Thanks
    1
    Thanked 33 Times in 33 Posts

    Re: Just need code checked (97,2000,2002)

    What is datatype of ItemNumber, is it really a number or is it a text string? This will affect how you form strFilter.
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

  4. #4
    2 Star Lounger
    Join Date
    Jan 2001
    Location
    Pennsylvania, USA
    Posts
    144
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Just need code checked (97,2000,2002)

    ItemNumber is a text string

    if ItemNumber were actually a number this would be easy.
    threecrow

    Don't make excuses. It's what you do, not why you didn't.

  5. #5
    2 Star Lounger
    Join Date
    Jan 2001
    Location
    Pennsylvania, USA
    Posts
    144
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Just need code checked (97,2000,2002)

    There must be a difference, between using actual " and using chr(34).

    What is the difference
    threecrow

    Don't make excuses. It's what you do, not why you didn't.

  6. #6
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,430
    Thanks
    1
    Thanked 33 Times in 33 Posts

    Re: Just need code checked (97,2000,2002)

    You have to look at what you want to produce. You want to produce a string like this: ItemNumber="12345"

    To do that, you have to place the quotation marks in your string, that is why chr(34) is used, that is what John's formula does:
    strfilter = "ItemNumber" & chr(34) & Me.ItemNumber & chr(34)
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

Posting Permissions

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