Results 1 to 7 of 7
  1. #1
    database_1 is offline Advanced Beginner
    Windows 10 Office 365
    Join Date
    Mar 2020
    Posts
    34

    Post Multiple criteria in a Dlookup help

    Hello, I am trying to find a certain ID depending on the values of a couple comboboxes I have on my form. This is my code right now:



    Dim lookup As String
    Dim emp As Integer
    emp = Me.TXT_EMP.Column(2)
    Dim trng As Integer
    trng = Me.TXT_TR.Column(1)
    lookup = DLookup("[TRAINING]", "[EMP_TRNG_TBL]", "[TRNG_ID] =" & trng)
    MsgBox lookup

    Right now it is returning the training based on the TRNG_ID chosen on the form. The problem is I want to include an EMP_ID criteria which would filter only the trainings with that certain ID and that certain EMP_ID. I have a combobox for EMP_ID and I have it set as the interger emp and it is returning the correct value. I just can not filter both EMP_ID and TRNG_ID at the same time. Any assistance would be appreciated, thank you.

  2. #2
    Bob Fitz's Avatar
    Bob Fitz is offline Access Developer
    Windows 10 Access 2016
    Join Date
    May 2011
    Location
    Essex UK
    Posts
    3,530
    You will need something like:

    Code:
    Dim lookup As String
    Dim emp As Integer
    emp = Me.TXT_EMP.Column(2)
    Dim trng As Integer
    trng = Me.TXT_TR.Column(1)
    lookup = DLookup("[TRAINING]", "[EMP_TRNG_TBL]", "[TRNG_ID] =" & trng & " AND [EMP_ID] = " & emp)
    MsgBox lookup
    If this helped, please click the star at the bottom left of this posting and add to my reputation . Many thanks.
    Bob Fitzpatrick

  3. #3
    ranman256's Avatar
    ranman256 is online now VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    if EmpID is numeric

    lookup = DLookup("[TRAINING]", "[EMP_TRNG_TBL]", "[TRNG_ID] =" & trng & " and [EMP_ID]=" & emp )

    or do combos:

    if isnull(emp) then
    lookup = DLookup("[TRAINING]", "[EMP_TRNG_TBL]", "[TRNG_ID] =" & trng )
    else
    lookup = DLookup("[TRAINING]", "[EMP_TRNG_TBL]", "[TRNG_ID] =" & trng & " and [EMP_ID]=" & emp )
    endif


  4. #4
    database_1 is offline Advanced Beginner
    Windows 10 Office 365
    Join Date
    Mar 2020
    Posts
    34
    Thank you. It seems like your solution worked. My only other problem is I was going to use the dlookup to determine if there was a value found or not found. I want to have it where if there is no value found in the dlookup, add the information on my form to a table. If the dlookup does return a value, I want to inform the user that the information is already entered. I have this code but I am getting an invalid use of Null error.

    If IsNull(lookup) Then
    Set rst = CurrentDb.OpenRecordset("EMP_TRNG_TBL")
    With rst
    .AddNew
    .Fields("TRAINING") = Me.TXT_TR.Value
    .Fields("TRNG_ID") = Me.TXT_TR.Column(1)
    .Fields("EMP_ID") = Me.TXT_EMP.Column(2)
    .Update
    End With
    Me.TXT_TR.Value = Null
    Else
    MsgBox "Training already submitted"

  5. #5
    Bob Fitz's Avatar
    Bob Fitz is offline Access Developer
    Windows 10 Access 2016
    Join Date
    May 2011
    Location
    Essex UK
    Posts
    3,530
    Change lookup to a integer and use DCount() instead of DLookup()
    Then test for lookup >0
    If this helped, please click the star at the bottom left of this posting and add to my reputation . Many thanks.
    Bob Fitzpatrick

  6. #6
    database_1 is offline Advanced Beginner
    Windows 10 Office 365
    Join Date
    Mar 2020
    Posts
    34
    That worked like a charm, you are a savior.

  7. #7
    Bob Fitz's Avatar
    Bob Fitz is offline Access Developer
    Windows 10 Access 2016
    Join Date
    May 2011
    Location
    Essex UK
    Posts
    3,530
    Quote Originally Posted by database_1 View Post
    That worked like a charm, you are a savior.
    Thank you for your kind words. Alway glad to help if I can
    BTY
    Are you using this validation code in the forms Before Update Event?
    If this helped, please click the star at the bottom left of this posting and add to my reputation . Many thanks.
    Bob Fitzpatrick

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

Similar Threads

  1. Dlookup with multiple criteria
    By jeffhanner10@gmail.com in forum Programming
    Replies: 2
    Last Post: 02-05-2020, 02:07 PM
  2. multiple criteria in a dlookup
    By xmattxman in forum Programming
    Replies: 1
    Last Post: 04-04-2019, 08:21 AM
  3. Dlookup multiple criteria
    By Zoona in forum Programming
    Replies: 5
    Last Post: 01-04-2017, 04:42 PM
  4. DLookup with multiple criteria
    By amai in forum Access
    Replies: 2
    Last Post: 12-20-2015, 02:10 PM
  5. Multiple criteria in DLookup
    By crowegreg in forum Forms
    Replies: 7
    Last Post: 06-22-2011, 01:47 AM

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