Textbox as source criteria for multiple queries (1 Viewer)

[JPR]

Hello,

I would appreciate your help in solving this issue.
1I was thinking to redesign a search form and remove the multiple search boxes and buttons and just leave one of each.

My form will have:

- 1 combo box (cboqueries) which will display the names of the queries
- 1 search textbox (txtsearch) in which users will type the criteria
- 1 cmd button (cmdsearch) that will run the specific query
- 1 list box (lstsearchresults) which will display the query results

I have written this code but only works for the first query. When I change the query from the cbo box and get the No records found message.

If Me.cboqueries.Value = "FILENO" Then
Me.lstsearch.RowSource = "qryFILENO"
else
MsgBox "Sorry, no records were found.", vbInformation, "Test Search Menu"
Me.txtsearch.Value = Null
Me.txtsearch.SetFocus
End If

If Me.cboqueries.Value = "LASTNAME" Then
Me.lstsearch.RowSource = "qryLASTNAME"
else
MsgBox "Sorry, no records were found.", vbInformation, "Test Search Menu"
Me.txtsearch.Value = Null
Me.txtsearch.SetFocus
End If

If Me.cboqueries.Value = "FIRSTNAME" Then
Me.lstsearch.RowSource = "qryFIRSTNAME"
else
MsgBox "Sorry, no records were found.", vbInformation, "Test Search Menu"
Me.txtsearch.Value = Null
Me.txtsearch.SetFocus
End If

Thank you for your help.

 

[Edgar_]

Try this code:

Private Sub cmdsearch_Click()
    Me.lstsearch.RowSource = "qry" & Me.cboQueries.Value
    Me.txtsearch = Null
    Me.txtsearch.SetFocus
    If Me.lstsearch.ListCount = 0 Then
        MsgBox "Sorry, no records were found.", vbInformation, "Test Search Menu"
    End If
End Sub

Since the difference between your query names and the items in the combobox is the "qry" prefix, you can simply set it programmatically and save a few lines.

The if...then logic you were using didn't reflect the message, so I added a ListCount check to return the records found, and if it's 0 records, then show the message.

Lastly, you said your listbox was named lstsearchresults, but in your code it is lstsearch

Here's a test file for you to play.

 

[JPR]

Hello Edgar,
appreciate your help. Sorry for the typo. I now understand the code line

    Me.lstsearch.RowSource = "qry" & Me.cboQueries.Value

I have tested your sample but still cannot display the results in the lstbox after typing the criteria in txtsearch and selecting the qry from cboqueries.
= "qry" & Me.cboQueries.Value
Thank you

 

[Gasman]

How can you say 'No records found' when you have not even run the query?
Edgar's example works for me?

Show your code.

 

[JPR]

Hello,

Obviously I have tried the queries and they do work fine. No problem with them.
I was referring to the records in the list box which does not appear to refresh. Thank you and sorry for not being more precise.

 

[DHookom]

Replace the multiple If Then statements Select Case.

 

[Gasman]

Hello,

Obviously I have tried the queries and they do work fine. No problem with them.
I was referring to the records in the list box which does not appear to refresh. Thank you and sorry for not being more precise.

So what happens if no records appear for a search?
Your logic would not display that error message?
That only gets displayed if nothing is selected in the combo?

 

[Ken Sheridan]

I have tested your sample but still cannot display the results in the lstbox after typing the criteria in txtsearch and selecting the qry from cboqueries.

You also need to click the command button to execute the code in its Click event procedure which undertakes the search. It works exactly as intended for me.

One change I'd suggest would be to change the cboQueries control's RowSource property to:

"FILENO";"File Number";"FIRSTNAME";"First Name";"LASTNAME";"Last Name"

and its ColumnCount property to 2 , and its Columnwidths property to 0cm

Access will automatically change the unit of the last to inches if you are not using metric units. These changes will present the user with a list of plain English descriptions of the queries, but the value of the control when a selection is made will be the partial object names without spaces. As the user won't see these names you could include the qry prefixes, removing the need to concatenate this to the partial name in the code.

 

[Edgar_]

Hello Edgar,
appreciate your help. Sorry for the typo. I now understand the code line

    Me.lstsearch.RowSource = "qry" & Me.cboQueries.Value

I have tested your sample but still cannot display the results in the lstbox after typing the criteria in txtsearch and selecting the qry from cboqueries.
= "qry" & Me.cboQueries.Value
Thank you

Hello, @JPR
I did my best to understand your intent, and I created a database schema with a form and queries that match your request. The data I used is completely fictional, but it should give you enough information to draw your own conclusions and adapt everything to your actual dataset.

The reason it may not work for you is most likely that it is not based on your real data. However, if you examine the tables or click the button without entering any criteria in the txtsearch textbox, you will be able to see the sample data that I used.

From that point, you can run any queries you wish. I hope it helps.