Results 1 to 10 of 10
  1. #1
    bronson_mech is offline Advanced Beginner
    Windows 7 64bit Access 2010 64bit
    Join Date
    May 2013
    Posts
    94

    DLookup Function Using Two Combo Boxes

    I have two combo box, if I choose company name & Job Title then ST Code and OT code should appear

    Form:frmEmployeeDetails


    Click image for larger version. 

Name:	Access.JPG 
Views:	20 
Size:	45.2 KB 
ID:	35797

    Table: tblCraft
    Click image for larger version. 

Name:	Capture.JPG 
Views:	20 
Size:	16.5 KB 
ID:	35798

  2. #2
    orange's Avatar
    orange is offline Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    Please show the code behind these combos and for filling the St Code, OT code controls.
    You may also get some info from this Allen Browne article.

  3. #3
    Missinglinq's Avatar
    Missinglinq is offline VIP
    Windows 7 64bit Access 2007
    Join Date
    May 2012
    Location
    Richmond (Virginia, not North Yorkshire!)
    Posts
    3,016
    Just to be clear...am I correct in assuming that the 'Job Title' comes from the 'Craft' Field in your Table?

    Linq ;0)>
    The problem with making anything foolproof...is that fools are so darn ingenious!

    All posts/responses based on Access 2003/2007

  4. #4
    bronson_mech is offline Advanced Beginner
    Windows 7 64bit Access 2010 64bit
    Join Date
    May 2013
    Posts
    94
    Quote Originally Posted by orange View Post
    Please show the code behind these combos and for filling the St Code, OT code controls.
    You may also get some info from this Allen Browne article.
    Private Sub Company_AfterUpdate()
    Me.STCode = DLookup("STCode", "tblcraft", "Company=" & Me.Company)
    Me.OTCode = DLookup("OTCode", "tblcraft", "Company=" & Me.Company)
    End Sub

  5. #5
    bronson_mech is offline Advanced Beginner
    Windows 7 64bit Access 2010 64bit
    Join Date
    May 2013
    Posts
    94
    No the "Job Title comes for "tblEmployeeDetails" its a different table

  6. #6
    kd2017 is offline Well, I tried at least.
    Windows 10 Access 2016
    Join Date
    Jul 2017
    Posts
    1,142
    Should probably look more like this assuming I understand your relationships:

    Quote Originally Posted by bronson_mech View Post
    Private Sub JobTitle_AfterUpdate()
    Me.STCode = DLookup("STCode", "tblcraft", "Company=" & Me.Company & " AND Craft=" & me.jobtitle)
    Me.OTCode = DLookup("OTCode", "tblcraft", "Company=" & Me.Company & " AND Craft=" & me.jobtitle)
    End Sub
    Some if the field/object names may not be right but to answer the subject title of the post: you can use multiple criteria in the dlookup function, the syntax would look just look the where clause in a SQL query. Eg "field1=123 AND field2='abc' OR field3<#12/25/2018#" so on and so forth...

  7. #7
    bronson_mech is offline Advanced Beginner
    Windows 7 64bit Access 2010 64bit
    Join Date
    May 2013
    Posts
    94
    I have tried with this code but no luck

    Private Sub Job_Title_AfterUpdate()
    Me.STCode = DLookup("STCode", "tblcraft", "Company=" & Me.Company & " AND Job_Title=" & Me.Job_Title)
    Me.OTCode = DLookup("OTCode", "tblcraft", "Company=" & Me.Company & " AND Job_Title=" & Me.Job_Title)
    End Sub

    Click image for larger version. 

Name:	Error.JPG 
Views:	14 
Size:	47.6 KB 
ID:	35801

  8. #8
    kd2017 is offline Well, I tried at least.
    Windows 10 Access 2016
    Join Date
    Jul 2017
    Posts
    1,142
    Look at the string that you built for the cirteria:
    Company=AYTB AND Job_Title=Lead

    Because company and job_title are string fields the values need to be surrounded by quotation marks
    Company="AYTB" AND Job_Title="Lead"

    So your code would look like
    Code:
    Me.STCode = DLookup("STCode", "tblcraft", "Company=""" & Me.Company & """ AND Job_Title=""" & Me.Job_Title & """")
    However, is Job_Title = Me.Job_Title even correct? From the original post tblCraft's field name is Craft, not Job_Title. I would expect it to look more like this:
    Code:
    Me.STCode = DLookup("STCode", "tblcraft", "Company=""" & Me.Company & """ AND Craft=""" & Me.Job_Title & """")

  9. #9
    bronson_mech is offline Advanced Beginner
    Windows 7 64bit Access 2010 64bit
    Join Date
    May 2013
    Posts
    94
    Quote Originally Posted by kd2017 View Post
    Look at the string that you built for the cirteria:
    Company=AYTB AND Job_Title=Lead

    Because company and job_title are string fields the values need to be surrounded by quotation marks
    Company="AYTB" AND Job_Title="Lead"

    So your code would look like
    Code:
    Me.STCode = DLookup("STCode", "tblcraft", "Company=""" & Me.Company & """ AND Job_Title=""" & Me.Job_Title & """")
    However, is Job_Title = Me.Job_Title even correct? From the original post tblCraft's field name is Craft, not Job_Title. I would expect it to look more like this:
    Code:
    Me.STCode = DLookup("STCode", "tblcraft", "Company=""" & Me.Company & """ AND Craft=""" & Me.Job_Title & """")
    Prefect thank you soo much it's working....
    As you said this code was right " Me.STCode = DLookup("STCode", "tblcraft", "Company=""" & Me.Company & """ AND Craft=""" & Me.Job_Title & """")"

  10. #10
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,824
    You can use an apostrophe instead of the two quote marks.
    Me.STCode = DLookup("STCode", "tblcraft", "Company='" & Me.Company & "' AND Craft='" & Me.Job_Title & "'")"
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

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

Similar Threads

  1. Replies: 3
    Last Post: 07-09-2018, 05:12 PM
  2. Changing text boxes to combo boxes
    By Lou_Reed in forum Access
    Replies: 8
    Last Post: 09-15-2015, 11:09 AM
  3. Replies: 11
    Last Post: 08-06-2014, 09:47 AM
  4. Replies: 9
    Last Post: 06-04-2014, 10:45 AM
  5. Replies: 16
    Last Post: 08-19-2010, 01:40 PM

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