My inquiry is this: How do I make the input field this button is connected to automatically refresh after it's been used to input the selection, which is displayed in listbox 149?
I'm having the string from Where clause be displayed in another text field (how it was in the sample I code I got online), and the listbox shows patient's first name, last name and gender.
Expand|Select|Wrap|Line Numbers
- Private Sub cmdSearch_Click()
- On Error Resume Next
- Dim ctl As Control
- Dim sSQL As String
- Dim sWhereClause As String
- Dim db As DAO.Database
- Dim rs As DAO.Recordset
- 'Dim strSQL As String
- Set db = CurrentDb
- 'Initialize the Where Clause variable.
- sWhereClause = " Where "
- 'Start the first part of the select statement.
- sSQL = "select * from Patients "
- 'sSQL = "INSERT INTO Patients (MedRec,Gender) VALUES (1121, 'R');"
- 'Loop through each control on the form to get its value.
- For Each ctl In Me.Controls
- With ctl
- 'The only Control you are using is the text box. However, you can add as many types of controls as you want.
- Select Case .ControlType
- Case acTextBox
- .SetFocus
- 'This is the function that actually builds the clause.
- If sWhereClause = " Where " Then
- sWhereClause = sWhereClause & BuildCriteria(.Name, dbText, .Text)
- Else
- sWhereClause = sWhereClause & " and " & BuildCriteria(.Name, dbText, .Text)
- End If
- End Select
- End With
- Next ctl
- 'Set the forms recordsource equal to the new select statement.
- Me.txtSQL = sSQL & sWhereClause & ";"
- 'Me.RecordSource = sSQL & sWhereClause & ";" 'Me.Query = sSQL & sWhereClause 'Me.Requery
- 'db.Execute sSQL & sWhereClause & ";", dbFailOnError
- 'Me.List149.RowSource = db.OpenRecordset("select * from Patients;")
- Set rs = db.OpenRecordset(sSQL & sWhereClause & ";")
- 'Do While Not rs.EOF
- ' Me.List149.AddItem (rs![First Name] & ";" & rs![Last Name])
- ' rs.MoveNext
- 'Loop
- 'Dim db As DAO.Database
- 'Dim rs As DAO.Recordset
- Dim lst As ListBox
- 'strBuild = ""
- Set lst = Me![List149]
- 'Set up the Listbox correctly, if it isn't already
- lst.RowSourceType = "Value List"
- lst.ColumnCount = 3
- lst.BoundColumn = 3
- lst.ColumnWidths = "1 in;1 in;1 in"
- 'Set db = CurrentDb
- 'Set rs = db.OpenRecordset("SELECT * FROM AnswerT WHERE QuestionID =" & QuestionID)
- Do While Not rs.EOF
- strBuild = strBuild & rs!Gender & ";" & rs![Last Name] & ";" & rs![First Name] & ";"
- rs.MoveNext
- Loop
- lst.RowSource = Left$(strBuild, Len(strBuild) - 1)
- rs.Close
- Set rs = Nothing
- Set db = Nothing
- End Sub