Searching query displaying results in listbox and generating report.

[henricirc]

Ok so here it goes, I'm not sure if this can be done but I'm trying.

I am trying to create a form "pickpatien ts" with a button (newsearch) and a listbox (list2) to run a query of my Visits1 table where all my patient visit data is stored. I want to be able to generate the results of this query in list2. I would then like to be able to click or highlight a result from the query and press another button on the form to generate a report with all of the person's data from the Visits1 table. Can it be done?

So far I set the rowsource of list2 to the query and created the button newsearch to run the query. Unfortunately list2 will only generate the results of the query once when the form is open, not each time the button is pressed. This is the vba code of the command button:

 
Private Sub newsearch_Click()

On Error GoTo Err_newsearch_Click

    Dim stDocName As String

    stDocName = "PatientQuery1"
    DoCmd.OpenQuery stDocName, acNormal, acEdit

Exit_newsearch_Click:
    Exit Sub

Err_newsearch_Click:
    MsgBox Err.Description
    Resume Exit_newsearch_Click
    
End Sub

And here's the rowsource of list2:

SELECT [PatientQuery1].[Patient ID], [PatientQuery1].[Medical Records ID], [PatientQuery1].[Last Name], [PatientQuery1].[First Name], [PatientQuery1].[Sex], [PatientQuery1].[Diagnosis Code 1], [PatientQuery1].[Date of Visit] FROM PatientQuery1 ORDER BY [Patient ID];

Thanks for any and all help in advance!

[jimatqsi]

It's not really clear what you want. Why do you want to generate the list more than once? What is changing to make the list contents different each time the button is clicked?

The code you want is simply

me!list2.requery

That will force the list box content to be refreshed.

But it seems to me maybe you want to add a Where clause to your list2 rowsource so that the content of the listbox changes based on something you have entered or selected on the form.

In that case you would do something like this
strSQL="select somestuff where someotherstuff" and then
me!list2.rowsou rce = strSQL
me!list2.requer y

I hope that helps

Jim