populate a list box based on user input

Question

Hello, I am not a programmer, I need to populate a list box based on user input. I have Access form that has 5 Entry fields, that user enter proper information in one field and selecting “Search” icon.

Created 5 different queries that runs according to user selection. My codes are not populating in the text box, not sure why. Also is it possible to show the result of the query on just one "list box" on the form? I do not want to create 5 different List Box to accomplish this. (Please see attached screen print)

Field name are:

txtBoxNumber

txtCaseNumber

txtFileNumber

txtBuildingNumber

txtFloorNumber

List Box name “ListWindow”

Search icon name “cmdSearch”

‘**************************

Private Sub cmdSearch_Click()

Dim db As DAO.Database

Dim qdf As DAO.QueryDef

Dim rs As DAO.Recordset

 

 Set db = CurrentDb

 If txtBoxNumber <> "" Then

Set qdf = CurrentDb.QueryDefs("qryDataBoxSearch")

    qdf.Parameters(0) = Me.txtBoxNumber

 

Set rs = qdf.OpenRecordset()

Me.ListWindow.RowSource = rs("Box Nbr")

 

If rs.RecordCount <> 0 Then

    Dim i As Integer

    Me.ListWindow.ColumnCount = 8

    If Me.txtBoxNumber.Value = i And i > 0 And i < 50 Then

         Me.ListWindow.AddItem "Col1" & "," & "col2" & "," & "Col3" & "," & _

         "Col4" & "," & "Col5" & "," & "col6" & "," & "col7" & "," & "col8" & ";"

    End If

End If

'Cleanup

Set qdf = Nothing

Set db = Nothing 

End Sub

Answer 1

As the list box's RowSource use a query which references the five unbound controls in the form as parameters.  In the Search button's Click event procedure you then merely have to requery the list box with:

Me.ListWindow.Requery

You say that the user enters a value in one control, but you could enable a combination of values to be used as the basis for the search. The best basis for restricting a query on multiple parameters where the parameters might be used singly or in combination, allowing one or more to be Null, is that in the WHERE clause each parameter is tested in this way:

WHERE (SomeColumn = [some parameter]

OR [some parameter] IS NULL)

AND (SomeOtherColumn = [some other parameter]

OR [some other parameter] IS NULL)

AND etc

The following can of course be used:

(SomeColumn LIKE "" & [some parameter] & "" OR [some parameter] IS NULL)

where it is appropriate to use pattern matching rather than testing for equality. Bear in mind, however, that even when applied to a single column this can result in specious mismatches. Moreover, the LIKE operator does not allow use of the indexes, so can reduce performance significantly. In most cases it is better to reference a combo box in an unbound dialogue form, or in a bound form's header, as the parameter. The user can then select a value from a fixed set of known values, or by entering the first few characters in the combo box, progressively go to the first match by virtue of the control's AutoExpand property. The following query is an example:

SELECT [FirstName] & " " & [LastName] AS FullName, Address, City, Region, Country, Employer, LastName, FirstName, Contacts.ContactID FROM (Countries INNER JOIN Regions ON Countries.CountryID = Regions.CountryID) INNER JOIN (Employers INNER JOIN ((Cities INNER JOIN Contacts ON Cities.CityID = Contacts.CityID) INNER JOIN ContactEmployers ON Contacts.ContactID = ContactEmployers.ContactID) ON Employers.EmployerID = ContactEmployers.EmployerID) ON Regions.RegionID = Cities.RegionID WHERE (Cities.CityID = Forms!frmReportDialogue!cboCity OR Forms!frmReportDialogue!cboCity IS NULL) AND (Employers.EmployerID = Forms!frmReportDialogue!cboEmployer OR Forms!frmReportDialogue!cboEmployer IS NULL);

This example is taken from the section on 'Retrieving data from the database' in my DatabaseBasics demo file, which can be found in my public databases folder at:

https://onedrive.live.com/?cid=44CC60D7FEA42912&id=44CC60D7FEA42912!169

The logic behind this approach is extremely simple and consequently, given good indexing in the table, very efficient. It also has the advantage of not having to bother about the data type of the column in question, so unlike when building an SQL statement in code, consideration does not need to be given as to whether the values need delimiting or not.

Each OR operation is enclosed in parentheses to force it to evaluate independently. These parenthesized expressions are then tacked together with AND operations. The way it works is that each parenthesized OR expression will evaluate to TRUE for each row where the value in the column is that of the parameter or, if the parameter is left empty (NULL), for every row. By virtue of the AND operations the WHERE clause as a whole will evaluate to TRUE for those rows where all of the parenthesized expressions evaluate to TRUE, so those rows will be returned.

Note that when you do this, parameters should only be declared in a PARAMETERS clause if they are of DateTime data type. If other types were declared they could never be Null. DateTime parameters are unusual in this respect, and it's always advisable to declare them to avoid their being misinterpreted as arithmetical expressions rather than dates.

When building a query like this, the basic unrestricted query can be built in query design view, but the WHERE clause should always be written and, most importantly, saved in SQL view. This applies to the initial saving of the query, and if any subsequent amendments are made. If it's saved in design view Access will move things around and at best the logic will be obscured, at worst it might become too complex to open. It's a good idea to save the SQL of such queries in a text file in Notepad or similar, as if anything does go wrong you then have something to copy and paste back into the query designer in SQL view.

Note BTW that if searching on the basis of a date range this can be made a closed range or open ended in either direction by treating the start and end date parameters independently, rather than within a BETWEEN….AND operation:

WHERE (DateColumn >= [start date parameter]

OR [start date parameter] IS NULL) AND (DateColumn < [end date parameter]+1 OR [end date parameter] IS NULL)

This also allows for date/time values in the table where the time of day element is not zero. Any rows with such values would not be returned by a BETWEEN....AND operation if the value is on the final day of the range.

Answer 2

I figured it out, the product is working well with no issue. I am using very simple way to do this since I am not a programmer. LOL

What I am doing is, checking to see which field user entered the data since they just can use one, no multi selection.

Then according to that, run proper QueryDefs. I have 5 different queries for each selection like "qryDataBoxSearch", "qryDataCaseSearch", and so on.

Then use "Me.ListWindow.Recordset = rs" to populate data on the list box "Me.ListWindow".

Private Sub cmdSearch_Click()

Dim db As DAO.Database

Dim qdf As DAO.QueryDef

Dim rs As DAO.Recordset

 Set db = CurrentDb

      If txtBoxNumber <> "" Then

             ' *** use proper query for queryDefs   and "txt name" like "txtBoxNumber",     txtCaseNumber,  txtFileNumber and so on

             Set qdf = CurrentDb.QueryDefs("qryDataBoxSearch")

                    qdf.Parameters(0) = Me.txtBoxNumber  

                Set rs = qdf.OpenRecordset()



             ' *** use proper RowSource like "("Box Nbr"), (Case Num)". "(File Num)" and so on

                Me.ListWindow.RowSource = rs("Box Nbr")  

               If rs.RecordCount <> 0 Then

                    txtTotal = rs.RecordCount            

                Set Me.ListWindow.Recordset = rs

                        Me.ListWindow.Requery

                        Me.ListWindow.Visible = True

                End If                

    ElseIf txtCaseNumber <> "" Then

      'Use the same concept above.

    ElseIf txtFileNumber <> "" Then

       'Use the same concept above.