Microsoft Access Database - Search textbox form

Question

I am trying to create a form which contains a textbox and a button to click on to search for a record in a specific table and show all pertaining information for the searched text. With this form I will be using it as a sub-form so I think VBA is probably the best way.

Here is my Table:

Here is an example of what I would like to see happen:

I would like to enter a partno and click on the button and the fields will populate.

Please advise how to approach this.

Answer 1

On the click of the button you can set the SubForm's Recordsource to be based on the Query. It should be something like.

Private Sub Command0_Click()
    Me!subFrm_searchResult.Form.RecordSource = "SELECT theFields " & _
                                                FROM theTable " & _
                                                WHERE partNoFieldName = '" & 
                                                Me.partNumberTextBoxName & "'"
End Sub

This should be a start for you.