Search for subform record based on text box

carmens 6 Reputation points
2022-12-12T18:31:50.943+00:00

I am trying to bring up a form record based on text entered in a text box.

The form "Furnace + Header Data Form" is a parent form, with 2 subforms called "Header Data Form" and "Surrogate Data Form". This question is in relation to the "Header Data Form" subform.
The parent form is used to enter data on different furnace runs, which is connected to the table "Furnace Run Data". Each furnace run will have several headers in it, and each header has a serial number. I added a subform called "Header Data Form", which will allow the user to enter information specific to each header serial number. Data that is entered into the "Header Data Form" subform adds data to the "Header Data" table and is connected to the furnace data table via the Furnace Run ID. The Furnace Run ID is on both the parent form and as an invisible field on the Header Data subform. All fields on the Header Data Form are connected to the Header Data Table.

As a side note, there is a button within this subform to add a new record. Once the button is clicked, a new record with the furnace data and header data is saved and the header data subform is cleared. When you enter more data into the header data form and click the add button, another line (with the same Furnace Run ID) will be added to the header data table. No data is added to the furnace table. This all works great. There are multiple records in the Header Data table for each Furnace Run ID.

Initially, I was trying to find the record based on a text field named "Header SN Field" on the subform named "Header Data Form". This text field is connected to the "Header SN" Field from the "Header Data" table. Upon clicking the search button (on the Header Data subform), I wanted to use the text typed into the Header SN Field to search for the previous record in the Header Data table that has Header SN (field in Header Data table) = Header SN Field (field in form where user enters text). I could get this to run with no errors, but I kept getting the message saying that no matching record was found, even though there definitely is an existing record with the matching Header SN. My code for this is below.

Private Sub Search_SN_Button_Click()
If ([Header SN Placeholder] & vbNullString) = vbNullString Then Exit Sub
Dim strCriteria As String
Dim rs As DAO.Recordset 

Set rs = Me.RecordsetClone  
  
strCriteria = "[Header SN]='" & Me.[Header SN Field] & "'"  
  
rs.FindFirst strCriteria  
  
If rs.NoMatch Then  
    MsgBox "Sorry, no such record '" & [Header SN Field] & "' was found.", _  
           vbOKOnly + vbInformation  
Else  
    Me.Bookmark = rs.Bookmark  
End If  
rs.Close  
txtGoTo = Null

End Sub

After searching a bit, I thought the solution might be to have a text box and search button on the main form instead. The text box on the main form is called "HeaderSN". I am still getting the message that there is no existing record that matches. My code for this is below.
Private Sub Search_Header_SN_Record_Click()
If (HeaderSN & vbNullString) = vbNullString Then Exit Sub
Dim strCriteria As String
Dim rs As DAO.Recordset 

Set rs = Me.[Header Data Form].Form.RecordsetClone  
  
strCriteria = "Header SN = '" & Me.HeaderSN & "'"  
  
rs.FindFirst strCriteria  
  
If rs.NoMatch Then  
    MsgBox "Sorry, no such record '" & [HeaderSN] & "' was found.", _  
           vbOKOnly + vbInformation  
Else  
    Me.[Header Data Form].Form.Bookmark = rs.Bookmark  
End If  
rs.Close  
txtGoTo = Null

End Sub

Can someone please help me with this? I have tried a million different ways to do this and have been searching for days on different forums but I can't figure it out.

Microsoft 365 and Office | Access | Development
Developer technologies | Visual Basic for Applications

Locked Question. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

31 answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Ken Sheridan 3,581 Reputation points
    2022-12-20T21:09:01.393+00:00

    You can forward it to me at the following (munged) email address:

    kenwsheridan<at>yahoo<dot>co<dot>uk

    All subsequent correspondence will be via this forum.

    Was this answer helpful?

    2 people found this answer helpful.
    0 comments
  2. Ken Sheridan 3,581 Reputation points
    2022-12-20T16:47:53.16+00:00

    All I can suggest is that you post your file, stripped of any personal or otherwise sensitive data, to a site such as Microsoft's OneDrive, and post the link in a reply here so that we can take a look at it.

    Was this answer helpful?

    2 people found this answer helpful.
    0 comments
  3. Ken Sheridan 3,581 Reputation points
    2022-12-20T13:20:59.673+00:00

    The only obvious problem I can see with your latest code is that there is inconstancy with regard to the data type of the Header SN column. In the command button's Click event procedure it is treated as a text data type and wrapped in single quote characters. In the subform's Current event procedure it is treated as a number data type and not delimited with quote characters:

    .RecordsetClone.FindFirst "[Header SN] = " & Me.[Header SN Placeholder]

    As you are already filtering the subform, however, the code in the Current event procedure is not necessary and can be deleted. In my demo the code in the Current event procedure is used in the example where the subform is not filtered.

    Was this answer helpful?

    2 people found this answer helpful.
    0 comments
  4. Ken Sheridan 3,581 Reputation points
    2022-12-19T22:20:36.457+00:00

    You'll find the Name property of the subform control on the Other tab of its properties sheet.

    Both examples in my demo use a control on the parent form. The difference is that the second filters the subform as well as the parent form, while the first navigates to the matching record in the subform.

    If you wanted to change it to use an unbound control in the subform then the code would in essence be the same, but each reference to the form in the combo box's After Update event procedure would be changed to a reference to Me.Parent, and in the code in the subform's Current event procedure would be changed to reference to Me. The Me keyword actually returns a reference to the current instance of the class in which the code is executing, but think of it as a shorthand way of refereeing to the current form or subform.

    One other point I'd mention is that trying to use the same control as a bound control and a navigational control, as you mentioned earlier, is not a good idea. For a navigational control use an unbound control. You can synchronize this with the current record in the form's Current event procedure. The first option in my FindRecord demo does this. So you could omit the corresponding bound control(s), but you'd then have to include a mechanism to insert a value when entering a new record in the form.

    Was this answer helpful?

    2 people found this answer helpful.
    0 comments
  5. carmens 6 Reputation points
    2022-12-19T18:07:06.277+00:00

    @Ken Sheridan
    Update - got it to work a bit more. Instead of having a button and the text box on the subform, I added a text box named HeaderSN and a search button on the parent form. The subform control in my case is the same as the name of the subform (Header Data Form). I used the code you used (see below), but now I am only getting data to fill in the fields in the parent form and not the subform. What am I missing here? Is this where the bookmark action would come into play? Why does the main form not need to be bookmarked but the subform does?

    For future reference, is it possible to do this where the text box and/or the search button are on the subform instead of the parent form?

    Private Sub Search_Header_SN_Record_Click()
    Const MESSAGETEXT = "No matching records found."
    Dim ctrl As Control
    Dim strFilter As String 

     Set ctrl = Me.[HeaderSN]  
    'HeaderSN is text field on parent form  
 strFilter = "[Furnace Run ID] IN(SELECT [Furnace Run ID] " & _  
     "FROM [Header Data] WHERE [Header SN] = """ & ctrl & """)"  
      
 If Nz(ctrl, 0) = 0 Then  
     ' turn off filter  
     Me.FilterOn = False  
     Me.[Header Data Form].Form.FilterOn = False  
 Else  
     If Not IsNull(DLookup("[Header SN]", "[Header Data]", "[Header SN] = '" & ctrl & "'")) Then  
         ' filter form to name selected in text box  
         Me.Filter = strFilter  
         Me.FilterOn = True  
              
         ' filter subform to selected Header SN  
         Me.[Header Data Form].Form.Filter = "[Header SN] = '" & ctrl & "'"  
         Me.[Header Data Form].Form.FilterOn = True  
     Else  
         ' inform user if no matching records found and show all records  
         MsgBox MESSAGETEXT, vbInformation, "Warning"  
         Me.FilterOn = False  
         Me.Requery  
         Me.[Header Data Form].Form.FilterOn = False  
     End If  
 End If

    End Sub

    Was this answer helpful?

    1 person found this answer helpful.
    0 comments