List box multiple queries as row source (1 Viewer)

JPR · Aug 9, 2019

Hello,

Recently I had a great help form you with a similar topic.

I am now facing a new problem with a form on which I have placed a list box that I would like to populate with 4 different queries.

These queries will get their criteria from different text boxes. A cmd button should then run the code below and populate the list box. All four queries have the same table (table1) as records source.

The data I am trying to query is different (SSN, Lname, Fname and DOB).

The code I am using is the following:

If me.txtSSN.value "*" then
Me.lstbox.rowsource = "qrySSN"

elseif me.txtFName.value = "*" then
me.lstbox.rowsource = "qryFname"

elseif me.txtLName.value = "*" then
me.lstbox.rowsource = "qryLname"

elseif me.txtDOB.value = "*" then
me.lstbox.rowsource = "qryDOB"

End If

I have placed the "*" because I am not sure what to write in VBA to indicate if text box has a value, then run the appropriate query.

Thank you for any help with this issue.

Gasman · Aug 9, 2019

Probably better to test the length I think?

Code:

If Len(ControlName) & "" > 0 then

HTH

arnelgp · Aug 9, 2019

why not use 1 query:

Code:

select SSN, Lname, Fname, DOB from yourTable;

on the button click:

Code:

Dim strCriteria As String

strCriteria = strCriteria & "[SSN] Like " & Switch(Trim(Me.txtSSN & "") = "", "*", True, "'" & Me.txtSSN & "'")
strCriteria = strCriteria & " Or [LName] Like " & Switch(Trim(Me.txtLName & "") = "", "*", True, "'" & Me.txtLName & "'")
strCriteria = strCriteria & " Or [Fname] Like " & Switch(Trim(Me.txtFName & "") = "", "*", True, "#" & Format(Me.txtDOB, "mm/dd/yyyy") & "#")
strCriteria = strCriteria & " Or [DOB] Like " & Switch(Trim(Me.txtSSN & "") = "", "*", True, "'" & Me.txtSSN & "'")

With Me.lstbox
    .RowSource = "select SSN, FNAME, LNAME, DOB from qryName WHERE " & strCriteria
    .Requery
End With

List box multiple queries as row source (1 Viewer)

JPR

Registered User.

Gasman

Enthusiastic Amateur

arnelgp

..forever waiting... waiting for jellybean!

Similar threads

Users who are viewing this thread