Help with Nested If Statements for Access Form

[davidb88]

Hello Access Wizards...

I have a userform stored in Access that is supposed to perform a lookup using a number inputted by the user and it pulls in stored data that corresponds to that number. Recently, however, it was requested that the user perform the lookup based on a choice of 3 different numbers instead of just the one. I'm trying to find a way to do this but am running into problems. I had thought that if I used an IF statement I could get it to work, checking if the first number was populated and if not move on to the second and third. The following code is what I have come up with but it is not working in the way I want it to. It is only looking for the first number, but when the first number is not populated it is not moving onto the second. If anyone could help with this I would be most appreciative.

Thank you,
davidb88

On Error Resume Next
If IsNull(Me.cd_number_lookup) Then
    If IsNull(Me.tcsf_number_lookup) Then
        If IsNull(DLookup("[Packet #]", "tblclstrack", "[Packet #]=" & Forms![Form_Lookup].packet_number_lookup)) Then
       
        MsgBox "That Packet # is not associated with a logged request. Please check the number and try your search again.", vbCritical, "Packet # Not Found"
        Me.packet_number_lookup = ""
        packet_number_lookup.SetFocus
       
        Else
       
        Me.input_date = DLookup("[Today's Date]", "tblclstrack", "[Packet #]=" & Forms![Form_Lookup].packet_number_lookup)
        Me.email_date = DLookup("[Email Date]", "tblclstrack", "[Packet #]=" & Forms![Form_Lookup].packet_number_lookup)
        Me.email_sender = DLookup("[Email Sender]", "tblclstrack", "[Packet #]=" & Forms![Form_Lookup].packet_number_lookup)
        Me.email_subject = DLookup("[Email Subject]", "tblclstrack", "[Packet #]=" & Forms![Form_Lookup].packet_number_lookup)
        Me.cd_number = DLookup("[CD #]", "tblclstrack", "[Packet #]=" & Forms![Form_Lookup].packet_number_lookup)
        Me.inquiry_type = DLookup("[Inquiry Type]", "tblclstrack", "[Packet #]=" & Forms![Form_Lookup].packet_number_lookup)
        Me.tcsf_number = DLookup("[TCSF #]", "tblclstrack", "[Packet #]=" & Forms![Form_Lookup].packet_number_lookup)
        Me.state = DLookup("State", "tblclstrack", "[Packet #]=" & Forms![Form_Lookup].packet_number_lookup)
        Me.comments = DLookup("Comments", "tblclstrack", "[Packet #]=" & Forms![Form_Lookup].packet_number_lookup)
        Me.request_number = DLookup("[Request Number]", "tblclstrack", "[Packet #]=" & Forms![Form_Lookup].packet_number_lookup)
        Me.request_assign = DLookup("[Assigned To]", "tblclstrack", "[Packet #]=" & Forms![Form_Lookup].packet_number_lookup)
        Me.request_status = DLookup("[Request Status]", "tblclstrack", "[Packet #]=" & Forms![Form_Lookup].packet_number_lookup)
        Me.action_taken = DLookup("[Actions Taken]", "tblclstrack", "[Packet #]=" & Forms![Form_Lookup].packet_number_lookup)
           
        End If
           
    ElseIf IsNull(DLookup("[TCSF #]", "tblclstrack", "[TCSF #]=" & Forms![Form_Lookup].tcsf_number_lookup)) Then
        MsgBox "That TCSF # is not associated with a logged request. Please check the number and try your search again.", vbCritical, "Packet # Not Found"
        Me.tcsf_number_lookup = ""
        tcsf_number_lookup.SetFocus
       
    Else
   
    Me.input_date = DLookup("[Today's Date]", "tblclstrack", "[TCSF #]=" & Forms![Form_Lookup].tcsf_number_lookup)
    Me.email_date = DLookup("[Email Date]", "tblclstrack", "[TCSF #]=" & Forms![Form_Lookup].tcsf_number_lookup)
    Me.email_sender = DLookup("[Email Sender]", "tblclstrack", "[TCSF #]=" & Forms![Form_Lookup].tcsf_number_lookup)
    Me.email_subject = DLookup("[Email Subject]", "tblclstrack", "[TCSF #]=" & Forms![Form_Lookup].tcsf_number_lookup)
    Me.cd_number = DLookup("[CD #]", "tblclstrack", "[TCSF #]=" & Forms![Form_Lookup].tcsf_number_lookup)
    Me.inquiry_type = DLookup("[Inquiry Type]", "tblclstrack", "[TCSF #]=" & Forms![Form_Lookup].tcsf_number_lookup)
    Me.tcsf_number = DLookup("[TCFS #]", "tblclstrack", "[TCSF #]=" & Forms![Form_Lookup].tcsf_number_lookup)
    Me.state = DLookup("State", "tblclstrack", "[TCSF #]=" & Forms![Form_Lookup].tcsf_number_lookup)
    Me.comments = DLookup("Comments", "tblclstrack", "[TCSF #]=" & Forms![Form_Lookup].tcsf_number_lookup)
    Me.request_number = DLookup("[Request Number]", "tblclstrack", "[TCSF #]=" & Forms![Form_Lookup].tcsf_number_lookup)
    Me.request_assign = DLookup("[Assigned To]", "tblclstrack", "[TCSF #]=" & Forms![Form_Lookup].tcsf_number_lookup)
    Me.request_status = DLookup("[Request Status]", "tblclstrack", "[TCSF #]=" & Forms![Form_Lookup].tcsf_number_lookup)
    Me.action_taken = DLookup("[Actions Taken]", "tblclstrack", "[TCSF #]=" & Forms![Form_Lookup].tcsf_number_lookup)
   
    End If
   
ElseIf IsNull(DLookup("[CD #]", "tblclstrack", "[CD #]=" & Forms![Form_Lookup].cd_number_lookup)) Then
        MsgBox "That CD # is not associated with a logged request. Please check the number and try your search again.", vbCritical, "Packet # Not Found"
        Me.cd_number_lookup = ""
        cd_number_lookup.SetFocus
 
Else
 
Me.input_date = DLookup("[Today's Date]", "tblclstrack", "[CD #]=" & Forms![Form_Lookup].cd_number_lookup)
Me.email_date = DLookup("[Email Date]", "tblclstrack", "[CD #]=" & Forms![Form_Lookup].cd_number_lookup)
Me.email_sender = DLookup("[Email Sender]", "tblclstrack", "[CD #]=" & Forms![Form_Lookup].cd_number_lookup)
Me.email_subject = DLookup("[Email Subject]", "tblclstrack", "[CD #]=" & Forms![Form_Lookup].cd_number_lookup)
Me.cd_number = DLookup("[CD #]", "tblclstrack", "[CD #]=" & Forms![Form_Lookup].cd_number_lookup)
Me.inquiry_type = DLookup("[Inquiry Type]", "tblclstrack", "[CD #]=" & Forms![Form_Lookup].cd_number_lookup)
Me.tcsf_number = DLookup("[TCFS #]", "tblclstrack", "[CD #]=" & Forms![Form_Lookup].cd_number_lookup)
Me.state = DLookup("State", "tblclstrack", "[CD #]=" & Forms![Form_Lookup].cd_number_lookup)
Me.comments = DLookup("Comments", "tblclstrack", "[CD #]=" & Forms![Form_Lookup].cd_number_lookup)
Me.request_number = DLookup("[Request Number]", "tblclstrack", "[CD #]=" & Forms![Form_Lookup].cd_number_lookup)
Me.request_assign = DLookup("[Assigned To]", "tblclstrack", "[CD #]=" & Forms![Form_Lookup].cd_number_lookup)
Me.request_status = DLookup("[Request Status]", "tblclstrack", "[CD #]=" & Forms![Form_Lookup].cd_number_lookup)
Me.action_taken = DLookup("[Actions Taken]", "tblclstrack", "[CD #]=" & Forms![Form_Lookup].cd_number_lookup)
   
End If

 

[TerryHogarth21]

Just to re-iterate to see if I'm understanding...the expected result is that you want each of numbers to display each of their results?
so for example the user types in 3 numbers then 3 results appear on your form? or do you mean the number in your table "tblclstrack" is something like 125255355 and that the user doesn't want to enter that as 1 number of 125255355 but now enter 125 then 255 then 355?

 

[davidb88]

Hi Terry -

Sorry, I should have explained a little better. Basically the userform gives the user the choice to search by 3 different numbers. So there are three different input boxes where the user can enter in numbers. So for instance if the user knows the CD # he/she can enter that. If they don't know that number they can search by the 2 other numbers. Every record that is stored in the table has each of those numbers so I wanted to give the user 3 different options for pulling in that record. Does that make sense?

Thanks for your help!

 

[TerryHogarth21]

Ok I understand now - just 1 other question, how is the text boxes(the results) being triggered on your form - or what do you intend? Are you updating the text boxes using the afterupdate event on the input boxes or is a user putting items into the input boxes for the numbers, clicking on a button that fires the code to check for which input box has numbers?

 

[davidb88]

The code is being triggered by a search button that is on the form. So all of the code resides under a module called button_lookup_click(). That part I know is working because when the form just had the one number search option everything worked well. It is when I tried to add the option to search by 3 different numbers that the problem began to occur!

Thank you again!