Results 1 to 6 of 6
  1. #1
    Join Date
    Mar 2012
    Posts
    68

    Unanswered: dlookup after update syntax error

    I have been working at this for hours. Have searched many threads and still havent had any luck.

    I have 2 boxes

    Category: CategoryText
    Item: ItemCombo

    ItemCombo is populated using a query from "Inventory" Table, "material" Field. When the ItemCombo is updated i want the CategoryID for the item to show up. I am using the data from a table "Inventory", Columns "Material" and "CategoryID". Material is populated with Text of the specific item and CategoryID is populated with numbers which reference another table "Category". Hopefully i explained that well enough.

    Here is the code i am currently using, in which i am getting an error.

    Code:
    Private Sub ItemCombo_AfterUpdate()
        
        CategoryText = DLookup("[CategoryID]", "[Inventory]", "Item =" & [ItemCombo])
    
                
            
        
    End Sub
    Error: Syntax error (missing operator) in query expression 'ItemCombo =16 x 16 Cam Access Door'.

    Anyone that can offer some help. i thank you in advance!

  2. #2
    Join Date
    Jun 2005
    Location
    Richmond, Virginia USA
    Posts
    2,763
    Provided Answers: 19
    Your Items are Text and you're using the syntax for a Field that is defined as a Number. The correct syntax would be

    Code:
    Me.CategoryText = DLookup("[CategoryID]", "[Inventory]", "[Item] ='" & Me.ItemCombo & "'")
    Linq ;0)>
    Last edited by Missinglinq; 03-10-12 at 12:28.
    Hope this helps!

    The problem with making anything foolproof...is that fools are so darn ingenious!

    All posts/responses based on Access 2003/2007

  3. #3
    Join Date
    Mar 2012
    Posts
    68
    Thanks for the quick response. Used the code with No error, but Category text box does not update with anything.

  4. #4
    Join Date
    Jun 2005
    Location
    Richmond, Virginia USA
    Posts
    2,763
    Provided Answers: 19
    You need to check your names/spelling of names:

    • Is CategoryText the Control you're trying to populate?
    • Is Inventory the correct name of the Table
    • Is Item the name of the Field in the Table you're trying to match? From your description I would have thought the Field was named Material.

    Also, I missed it earlier, but the Table/Query name should not have Square Brackets around it, it should be


    "Inventory"


    not


    "[Inventory]"

    Code:
    Me.CategoryText = DLookup("[CategoryID]", "Inventory", "[Item] ='" & Me.ItemCombo & "'")


    In Access Square Brackets generally denotes a Field name and can confuse the Access Gnomes when used around other objects.

    Linq ;0)>
    Hope this helps!

    The problem with making anything foolproof...is that fools are so darn ingenious!

    All posts/responses based on Access 2003/2007

  5. #5
    Join Date
    Mar 2012
    Posts
    68
    Thanks for your help.
    Working now, heres the code.

    Code:
    Private Sub ItemCombo_AfterUpdate()
        
        Category = DLookup("[CategoryID]", "Inventory", "[Material] ='" & Me.ItemCombo & "'")
    
                
            
        
    End Sub

  6. #6
    Join Date
    Jun 2005
    Location
    Richmond, Virginia USA
    Posts
    2,763
    Provided Answers: 19
    The devil, as they say, is in the details!

    Glad you got it working!

    Linq ;0)>
    Hope this helps!

    The problem with making anything foolproof...is that fools are so darn ingenious!

    All posts/responses based on Access 2003/2007

Posting Permissions

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