I am trying to create a form "pickpatients" 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:
Expand|Select|Wrap|Line Numbers
- 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
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!