Search Form with Multiple Fields all Different Data Types

[shelby55]

Hello

I am using Access 2010.

I have a table called tblTransfers that contains fields called patient_name (text field), MRN (number field) and Date_Tsfr_Req (date field).

I want to create a search form that allows the user to enter in one or all of the above and then the entries meeting the criteria will show in a list box. Then the user double clicks on an item in the list box and it opens up the form with the selected patient.

How can I go about doing this? Thanks very much.

 

[Andrzejek]

What I would do, on the Form place:
Patient_Name - text box
MRN - text box (allow numbers only)
Date-Tsfr-Req - date picker

Fill list box with the outcome from this SQL:

strSQL = "SELECT DISTINCT * FROM tblTransfers " & _
" WHERE 1 = 1 "
If Len(Trim(Patient_Name.Text)) > 0 then
  strSQL = strSQL & " AND Patient_Name LIKE '" & Patient_Name.Text & "'* "
end if
If MRN.Text > 0 then
  strSQL = strSQL & " AND MRN = " & MRN.Text 
end if
If Date-Tsfr-Req.Text > 0 then
  strSQL = strSQL & " AND Date-Tsfr-Req = #" & Date-Tsfr-Req.Text & "#"
end if
strSQL = strSQL & " ORDER BY XYZ"

Have fun.

---- Andy

 

[shelby55]

Hi Andy

Thanks so much for your response! This is great but how do I also get the double click of the item in the list to take me to the form opened up with the selection?

Thanks!

 

[dhookom]

The strSQL from the code Andy suggests would be applied to the Row Source of your list box although you want to make sure the fields are spelled correctly and I expect you want to include only a couple critical fields including the primary key of the table your form is based on. Your double-click event might look something like:

Dim strWhere as String
[COLOR=#4E9A06]'Assuming your primary key field is numeric[/color]
strWhere = "[PrimaryKeyFieldName] = " & Me.lboYourListBoxName
DoCmd.OpenForm "frmYourFormName", , , strWhere

Duane
Hook'D on Access
MS Access MVP

 

[shelby55]

Hi Guys

Sorry but my VBA is kind of rusty...where do I put the code Andy advised of? Within the list table beside Row Source there isn't the option to build code, only a pull down of queries.

Thanks.

 

[dhookom]

Maybe the "Then the user double clicks on an item in the list box" which suggests the double-click event of the list box.

Duane
Hook'D on Access
MS Access MVP

 

[shelby55]

Hi

What I want is a the user to enter information in one or more of the search boxes and then hit a button for "find" and a list box shows all applicable entries based on what was entered in the search criteria boxes. Then when the user double clicks on an item in the search box, that form is opened up.

I can't see from the information given how this will occur. Can you please assist? Thanks.

 

[dhookom]

Do you have a find button? Does the code behind it look something like Andy's?

Duane
Hook'D on Access
MS Access MVP

 

[shelby55]

Hi

Yes I have a find button but Andy's code isn't complete for the button to apply the SQL for the selections and then have the results show up in the list.

Duane, your cryptic single answers aren't helpful. If you don't wish to help then don't respond.

 

[dhookom]

I guess I was hoping you would provide:
- your code for your find button
- the exact names of the controls and their related fields and data types that you want to use to filter the list box
- The table name and exact column names you would like to display in the list box (so we can assist with writing the SQL code)
- The name of the form you want to open
- The field in the form's record source that is filtered with the value of the list box
- The data type of the field used to filter the form

Duane
Hook'D on Access
MS Access MVP

 

[Andrzejek]

My code is an assumption that you do work with recordsets (I can't stand bound controls :-( )

So the code would look something like that:

Private Sub cmdFind()
Dim rst As recordset
Dim i As integer

strSQL = "...." [green]'from above post[/green]

ListBox.Clear

with rst
    .Open strSQL, Conn

    For i = 1 to .RecordCount
        ListBox.AddItem !AFieldFromRST.Value
        .MoveNext
    Next i
    .Close
End With

Set rst = Nothing

End Sub

BTW. Duane does want to help you. If you answer his questions, you will get a lot better responce to your problem. And probably with the code you can copy / paste and it will work.

Have fun.

---- Andy