Button searches subform, then finds record in main form (Access VBA)

Question

I have this ACCESS form that contains a subform:Form & Subform I'm trying to create a search button (using VBA) to find a student by name (students' names are not in the table behind the main form).

I've done the first step, which is to search the subform for a student's name, but I'm having trouble with my desired second step. I'd like the code to then take the CWID number of the student and find the matching record in the main form. How would I do this? (My current code is below)

I've tried DoCmd FindRecord and GoToRecord, but it is completely stumping me. I'm Google-learning how to do this and think I'm fundamentally misunderstanding something and am thus unable to search for or understand the answer. Any help would be greatly appreciated.

Private Sub btnSearch_Click()
Dim SQL As String
SQL = "SELECT [AWN Banner].CWID, [AWN Banner].FirstName, [AWN Banner].LastName, [AWN Banner].Freshman, [AWN Banner].Instructor, [AWN Banner].Course " _
    & "FROM [AWN Banner] " _
    & "RIGHT JOIN [AWNEntry] ON [AWN Banner].CWID = [AWNEntry].CWID " _
    & "WHERE [LastName] LIKE '" & Me.txtKeywords & "*' " _
    & "ORDER BY [AWN Banner].LastName "

    Me.subAwnObj.Form.RecordSource = SQL
    Me.subAwnObj.Form.Requery
    End Sub

YHoffman YHoffman · Accepted Answer · 2018-02-09T21:18:47

When I create my search boxes I put in the underlying form source a WHERE clause WHERE [field] LIKE "*" & [MySearchBox] & "*" I put such a clause in the subform and the main form I create a JOIN to the subform record with the same WHERE clause defined. Then on click I request all recordsources.

Button searches subform, then finds record in main form (Access VBA)

1 Answers