Nested IF Statement to Check if Input Box Is Null on Form

[davidb88]

Hello -

I have a form in my Access database that has 3 input boxes by which a user can locate a record by. The problem I am running into is that I can not get a nested IF statement to work properly to first check which of the search boxes are filled in and second search by whichever is filled in. I have come up with the following code that seems to be only searching by the first input box. If the first input box is blank it does not move to the second or third numbers so I imagine there may be something wrong with the order of my IF statement or the syntax is off. Any help is greatly appreciated.

Thanks!

 

[pr2-eugin]

Showing a bit of code would be a start for us to look into ?!?

 

[davidb88]

My apologies! I completely forgot to paste it in!

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.e_date = DLookup("[E Date]", "tblclstrack", "[Packet #]=" & Forms![Form_Lookup].packet_number_lookup)
        Me.e_sender = DLookup("[E Sender]", "tblclstrack", "[Packet #]=" & Forms![Form_Lookup].packet_number_lookup)
        Me.e_subject = DLookup("[E 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.e_date = DLookup("[E Date]", "tblclstrack", "[TCSF #]=" & Forms![Form_Lookup].tcsf_number_lookup)
    Me.e_sender = DLookup("[E Sender]", "tblclstrack", "[TCSF #]=" & Forms![Form_Lookup].tcsf_number_lookup)
    Me.e_subject = DLookup("[E 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.e_date = DLookup("[E Date]", "tblclstrack", "[CD #]=" & Forms![Form_Lookup].cd_number_lookup)
Me.e_sender = DLookup("[E Sender]", "tblclstrack", "[CD #]=" & Forms![Form_Lookup].cd_number_lookup)
Me.e_subject = DLookup("[E 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

 

[namliam]

The best solution to do what you want to is to use some VBA under a button which can first easlily check box by box if its filled then alter and run your query.

 

[davidb88]

Pr2-eugin: Sorry about that! Completely forgot to paste the code! I submitted a reply but it says that a moderator needs to approve it first. So hopefully that will appear on the thread soon!

namilam: Thank you for your suggestion. Can you help me with an example of that that would look like? I've never done anything like that before. Thanks!

 

[pbaldy]

Not sure why it was moderated, but it's approved now.

 

[pr2-eugin]

Can you explain the logical flow of the process? The code is too much clunky and makes very little sense. Explain in simple English !

 

[davidb88]

Pr2-eugin:

Sorry, I know the code is clunky and difficult to follow! The basic flow of the process that I would like to follow is:

1. Check if CD# Input box is filled in.
a. If No: Skip to step 2
b. If Yes: Check if that CD # is associated with a logged request.
If No: Display message box to user. Exit Sub.
If Yes: Fill in the other text boxes on the form through using a lookup based on CD#. Exit Sub.

2. Check if TCSF# Input box is filled in.
a. If No: Skip to Step 3
b. If Yes: Check if TCSF # is associated with a logged request.
If No: Display message box to user. Exit Sub.
If Yes: Fill in the other text boxes on the form through using a lookup based on TCSF#. Exit Sub.

3. Check if Packet # Input box is filled in.
a. If No: Display message box alerting the user that one input number needs to be filled in to perform search. Exit Sub.
b. If Yes: Check if Packet # is associated with a logged request.
If No: Display message box to user. Exit Sub.
If Yes: Fill in the other text boxes on the form through a lookup based on Packet #. Exit Sub.

Does that make more sense laid out like that?

 

[Brianwarnock]

But you have not coded it like that but have coded all three null tests at the beginning.

Brian

 

[davidb88]

Hi Brian:
I coded it with the three null tests at the beginning because I wasn't sure how to code it the way I logically thought it out - with saying if the first condition is not met skip to the next test. Is there functionality like that in VBA that I could employ here?

 

[Brianwarnock]

You could always use a goto but how about

If not isnull(test1) Then
.
.
End
ElseIf not isnull(test2

Etc
If the test is null it skips down to the next test

Brian

 

[davidb88]

I was actually able to figure it out! After much research I was able to find that if a textbox had a value in it previously using IsNull will not work properly for whatever reason. As a workaround, I then used Len(NZ(...)) to do a count of the number of characters in the textbox. If that was less than 0 it skipped to the next test.

Thanks all for your help and input!

 

[davidb88]

Not sure if I should post a completely new thread or not on this issue, but because it is related I will just include it a continuation of this thread from earlier today....


As it can be seen the code I put together I am performing multiple Dlookups to pull in the data to the form from the table, tblclstrack.

As I am testing the database and trying to find potential issues, I have noticed that there is a potential issue for when there are records that have the same CD, TCSF, or Packet #s. In these cases I believe the Dlookup is pulling in the record that appears in the table first (i.e. the oldest record).

If I wanted to pull in the most recent record, is there a way to do that? So instead of pulling in the oldest record, I could pull in the most recent? I have an ID field so I am wondering if I can leverage that. Perhaps is there a condition I can add so that when searching it finds the record with the maximum ID?

Thank you again for your help.

 

[namliam]

The best solution to do what you want to is to use some VBA under a button which can first easlily check box by box if its filled then alter and run your query.

You dont want to do a bunch of DLookups, Dlookups are the devils playground and once your tables start growing in size will cause considerable slow down of your database.
Each DLookup essentially is a query on your database thus you are doing 10 of those where instead you could be doing only ONE!
Instead of 10 times something like:

DLookup("[Today's Date]", "tblclstrack", "[Packet #]=" & Forms![Form_Lookup].packet_number_lookup)

Only once use something like:

Dim RS as dao.recordset
set RS = currentdb.openrecordset("Select * from tblCLSTrack where [Packet #] = " & Forms![Form_Lookup].packet_number_lookup & " order by ID desc")
debug.print rs!ID
Debug.print rs![Today's Date]
Debug.print rs![E Date]
etc....

Edit:
It may be a good idea for you to review this thread:
http://www.access-programmers.co.uk/forums/showthread.php?t=225837&highlight=name+convention

 

[davidb88]

Thank you, Namliam. That makes a lot of sense. My one question is, when you do the debug.print for each of the fields, do those debug.prints print to the textboxes on the lookup form? If not automatically, is there a way to debug.print to a specific location on a form?

Thanks again!

EDIT: I was able to figure it out, it was a dumb question on my part . For anyone reading this thread in the future, rather than using the debug.print function I just assigned the unbound textbox values to the value of the RS function (i.e. me.today_date.Value = rs![Today's Date]). Thank you, Namliam!

 

[namliam]

debug.print prints the contents to the immediate window (CTRL + G)
I wrote that just to give you the idea how to "get" the fields.

If you want the values on the form you do it the same way you did with the Dlookup
Me.input_date = RS!Input_Date