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
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
Rich (BB code):
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