Query open, If Or And condition not working

[ljungers]

Have a couple of conditions going on that I can't seem to get working correctly.

Have 1 Form only with 3 text boxes that are used in a query, when a command button is clicked using 'Event Procedure'. The VBA Sub that runs (code below) checks to see if 1 or more text boxes had something entered, and if OK the list box row source should be poplulated with a query results. Only need 1 or more of the 3 boxes filled in. For some reason it wants all 3 filled in. Not sure why this is not working the way I want!

If Me.WhatLastName.Value = "" Or IsNull(Me.WhatLastName) And _
Me.WhatFirstName.Value = "" Or IsNull(Me.WhatFirstName) And _
Me.WhatMedRecNo.Value = "" Or IsNull(Me.WhatMedRecNo) Then

MsgBox ("Please enter 1 or more Search values")
GoTo Exit_SearchQuery_Click
Else
Me.SelectPrintItems.RowSource = "Lukup_Query"
Me.SelectPrintItems.Requery

End If

Also when this Form is opened for the 1st time the list box is populated with the complete table. Row Source type = Table/Query, Row Source = Lukup_Query. List box should not populate untill after the button has been clicked and the If test passes with no error.

Should I change the Row Source to a VBA coded SQL Query, or is there a way to do this with the button and list box properties!

[NeoPa]

You really mean :

If (Me.WhatLastName.Value = "" Or IsNull(Me.WhatLastName)) And _
(Me.WhatFirstName.Value = "" Or IsNull(Me.WhatFirstName)) And _
(Me.WhatMedRecNo.Value = "" Or IsNull(Me.WhatMedRecNo)) Then ...

This ensures the ORs are processed first and the ANDs only connect the results of those ORs.
In which case you should really code it something like :

If Nz(Me.WhatLastName,"")="" _
And Nz(Me.WhatFirstName,"")="" _
And Nz(Me.WhatMedRecNo,"")="" Then ...

This shorter version would also work :

If Nz(Me.WhatLastName,"") & _
   Nz(Me.WhatFirstName,"") & _
   Nz(Me.WhatMedRecNo,"")="" Then ...

[ljungers]

Works, Thank You.

Have any idea why the list box is loading 1st thing when the form is opened? Is that because the list box is tied to a Query and if so should I remove that query reference and place the SQL query in my VBA code under the _Click code?

[NeoPa]

Probably because you have the Row Source property set to the query in the design of the form itself.
You haven't given any indication of when your code runs so it's hard to guess what you're expecting to happen.
What do you want to happen exactly?