Results 1 to 9 of 9
  1. #1
    Vanz is offline Novice
    Windows 10 Access 2016
    Join Date
    Feb 2021
    Posts
    12

    Dlookup not working

    Hi all,



    I am trying to lookup for my product description based on the product category selected in the form. I have look through some of the tutorial video and found out that i can use dlookup to achieve that. But after building the dlookup function, i encounter errors in the textbox. Seek everyone help in guiding me. thanks. Dlookup.zip

  2. #2
    orange's Avatar
    orange is online now Moderator
    Windows 10 Access 2016
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    Does every ProductCategory have only 1 ProductDescription?

    A ---Apple
    B ---Banana

  3. #3
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    This may be of assistance, and would be more efficient than a DLookup():

    http://www.baldyweb.com/Autofill.htm
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  4. #4
    Vanz is offline Novice
    Windows 10 Access 2016
    Join Date
    Feb 2021
    Posts
    12
    Quote Originally Posted by orange View Post
    Does every ProductCategory have only 1 ProductDescription?

    A ---Apple
    B ---Banana
    Nope, it could be one product description match with multiple product category

    Product category ProductDescription
    A Apple
    C Apple
    D Apple

  5. #5
    Gicu's Avatar
    Gicu is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    Please have a look at the updated file for some guidance.
    Cheers,
    Attached Files Attached Files
    Vlad Cucinschi
    MS Access Developer
    http://forestbyte.com/

  6. #6
    Vanz is offline Novice
    Windows 10 Access 2016
    Join Date
    Feb 2021
    Posts
    12
    Thank you! Thats what i need. But i have an issue now whereby when i want to my record in the form based on the selected category it shows the ID number instead of the product category. How do i make my code read the product category instead of the ID number?

    For instance:
    The code only read cbopcat as the ID, instead of apple

    Code:
    Dim sSQL As String
    'Insert into banana
    If Me.cbopcat = "Banana" Then
     sSQL = "Insert into [Banana] (ReceivedDate,ProductCategory,ProductDescription,ReceivedQuantity,[Currency],UnitPrice,TotalAmount,Remarks) " & _
     "Values ('" & Me.txtrcvdate & "','" & Me.cbopcat & "','" & Me.txtpdes & "','" & Me.txtrcvquantity & "','" & Me.txtcurrency & "','" & Me.txtunitprice & "','" & Me.txtttlamount & "','" & Me.txtremarks & "')"
    'Insert into Apple
    ElseIf Me.cbopcat = "Apple" Then
     sSQL = "Insert into [Apple] (ReceivedDate,ProductCategory,ProductDescription,ReceivedQuantity,[Currency],UnitPrice,TotalAmount,Remarks) " & _
     "Values ('" & Me.txtrcvdate & "','" & Me.cbopcat & "','" & Me.txtpdes & "','" & Me.txtrcvquantity & "','" & Me.txtcurrency & "','" & Me.txtunitprice & "','" & Me.txtttlamount & "','" & Me.txtremarks & "')"
    end if

  7. #7
    Gicu's Avatar
    Gicu is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    Try this:
    Code:
    Dim sSQL As String
    'Insert into banana
    If Me.cbopcat.Column(1) = "Banana" Then
     sSQL = "Insert into [Banana] (ReceivedDate,ProductCategory,ProductDescription,ReceivedQuantity,[Currency],UnitPrice,TotalAmount,Remarks) " & _
     "Values ('" & Me.txtrcvdate & "','" & Me.cbopcat.Column(1) & "','" & Me.txtpdes & "','" & Me.txtrcvquantity & "','" & Me.txtcurrency & "','" & Me.txtunitprice & "','" & Me.txtttlamount & "','" & Me.txtremarks & "')"
    'Insert into Apple
    ElseIf Me.cbopcat.Column(1) = "Apple" Then
     sSQL = "Insert into [Apple] (ReceivedDate,ProductCategory,ProductDescription,ReceivedQuantity,[Currency],UnitPrice,TotalAmount,Remarks) " & _
     "Values ('" & Me.txtrcvdate & "','" & Me.cbopcat.Column(1) & "','" & Me.txtpdes & "','" & Me.txtrcvquantity & "','" & Me.txtcurrency & "','" & Me.txtunitprice & "','" & Me.txtttlamount & "','" & Me.txtremarks & "')"
    end if
    Vlad Cucinschi
    MS Access Developer
    http://forestbyte.com/

  8. #8
    Vanz is offline Novice
    Windows 10 Access 2016
    Join Date
    Feb 2021
    Posts
    12
    It works. thank you.

  9. #9
    Gicu's Avatar
    Gicu is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    You're welcome!
    Cheers,
    Vlad Cucinschi
    MS Access Developer
    http://forestbyte.com/

Please reply to this thread with any new information or opinions.

Similar Threads

  1. DLookup not working
    By RJB in forum Access
    Replies: 4
    Last Post: 06-14-2018, 12:37 PM
  2. WTF? DLookup not working...
    By dniezby in forum Programming
    Replies: 17
    Last Post: 03-26-2017, 04:05 PM
  3. DLookup not working
    By Lou_Reed in forum Access
    Replies: 29
    Last Post: 03-13-2017, 01:25 PM
  4. DLookup not working,HELP!!
    By riocobre in forum Access
    Replies: 2
    Last Post: 03-12-2017, 11:36 AM
  5. Dlookup not working
    By shaunacol in forum Forms
    Replies: 8
    Last Post: 06-12-2015, 09:28 AM

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums