in MS Access, after creating a dynamic query, how does one update the textboxes on the form with the corresponding values from the recordset?

Question

Following the example here: https://support.microsoft.com/en-us/help/304302/how-to-build-a-dynamic-query-with-values-from-a-search-form-in-access I have created a search button that searches a table and seem to pull in the correct SQL statement. However, on this form I have several textboxes (UserID, FirstName, LastName, Department) which are tied to their respective columns in the database. How to I update these textboxes inside the form frmSearchUsers to reflect the results of the filtered/queried table?

I would have thought a Me.Requery would be sufficient, however the textboxes remain blank (save for txtSQL)

Private Sub cmdSearch_Click()
On Error Resume Next

Dim ctl As Control
Dim sSQL As String
Dim sWhereClause As String

'Initialize the Where Clause variable.
sWhereClause = " Where "

'Start the first part of the select statement.
sSQL = "select * from customers "

'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.Requery

End Sub

there are 4 textboxes, UserID, FirstName, LastName, Department.

Let's say I knew that in table Customers there was a Jane Doe, but did not know her ID or department.

Typing Jane into the FirstName textbox and Doe into the LastName textbox, and hitting search seems to yield the appropriate SQL query (and have confirmed that this correctly filters in SQL view on the table): SELECT * FROM Customers Where FirstName="Jane" and LastName="Doe"

However the additional fields will not update - what am I doing wrong here? Is it because of how I have the control source for the textboxes I have tied to the table columns?

CharlieTango92 CharlieTango92 · Accepted Answer · 2018-07-31T21:31:38

Figured out the answer. I had to define the recordset and db, and then reference the recordset results to display to the textbox after button update. The Code was largely the same:

Private Sub btnSearch_Click()

On Error Resume Next

Dim strSQL As String
Dim ctl As Control
Dim strWhereClause As String
Dim db As Database
Dim rs As DAO.Recordset
Dim textBox As Control
Dim finalSQL As String
Set db = CurrentDb

'Init beginning of SQL select statement
sWhereClause = " Where "
sSQL = "SELECT * FROM Customers "

'Loop through filled in Controls on form to get value
For Each ctl In Me.Controls
    With ctl
        Select Case .ControlType
            Case acTextBox
            .SetFocus
            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

Me.txtSQL = sSQL & sWhereClause
finalSQL = sSQL & sWhereClause

Set rs = db.OpenRecordset(finalSQL)
If rs.RecordCount > 0 Then
'If Matching Record(s) are found, pull result into appropriate fields
    Me.UserID = rs!UserID
    Me.FirstName = rs!FirstName
    Me.LastName = rs!LastName
    Me.Department = rs!Department

MsgBox "No Users Found Matching Specified Search Criteria.", vbOKCancel, "No Results Found"
End If

in MS Access, after creating a dynamic query, how does one update the textboxes on the form with the corresponding values from the recordset?

1 Answers