Form to Search and Retrieve Records

[Evanescent]

Hi Guys, I am trying to create a form which allows the users to retrieve records based on the values entered or chosen in the various combo boxes and textboxes, such as the customer's name, invoice number, service number and installation site. The users do not have to enter all the information for the search.

Currently, for the Customer's Name Combo Box, the values are retrieved from the Information Table and when a user selects a name, the Invoice Number Combo Box will automatically generate a list of Invoice Numbers related to the customer.

As for now, I need some guidance regarding the codes for the Search Button to retrieve the records. For example, when a user selects a particular Invoice Number and clicks on the Search Button, that particular Invoice Record will be shown.

Additional Information:
Tables:
1) Information (customer's particulars)
2) Invoice
3) SiteSurvey
4) ServiceRec (customer's service records)\

Queries:
1) infoInv (displays the fields in the Information table and Invoice table)
2) infoSs (displays the fields in the Information table and SiteSurvey table)
3) infoServrec (displays the fields in the Information table and Servicerec table)

Form: Single View, Unbound

Thanks!

[puppydogbuddy]

Currently, for the Customer's Name Combo Box, the values are retrieved from the Information Table and when a user selects a name, the Invoice Number Combo Box will automatically generate a list of Invoice Numbers related to the customer.

As for now, I need some guidance regarding the codes for the Search Button to retrieve the records. For example, when a user selects a particular Invoice Number and clicks on the Search Button, that particular Invoice Record will be shown.

Form: Single View, Unbound

Thanks!

Here is sample code. Replace illustrative object names with their actual names in your application. Eliminate one of the two sql strings depending on the data type of the invoice. Assumes search results returned to main form because you did not mention anything about subforms.

Private Sub btnSearch_Click()
 
Dim strSql As String
 
strSql = "Select * From tblInvoice Where InvNo = " & Me!cboInvoice      'if InvNo is Numeric
 
strSql = "Select * From tblInvoice Where InvNo = '" & Me!cboInvoice & "'"      'if InvNo is Text
 
Me.RecordSource = strSql
 
End Sub

[Evanescent]

Here is sample code. Replace illustrative object names with their actual names in your application. Eliminate one of the two sql strings depending on the data type of the invoice. Assumes search results returned to main form because you did not mention anything about subforms.

Private Sub btnSearch_Click()
 
Dim strSql As String
 
strSql = "Select * From tblInvoice Where InvNo = " & Me!cboInvoice      'if InvNo is Numeric
 
strSql = "Select * From tblInvoice Where InvNo = '" & Me!cboInvoice & "'"      'if InvNo is Text
 
Me.RecordSource = strSql
 
End Sub

Hi. Thanks for replying. Ive added in the codes for the Search Button and there is no error message when I click on the button. If I were to include a subform to display the records, how do I go about writing the codes?

[puppydogbuddy]

Hi. Thanks for replying. Ive added in the codes for the Search Button and there is no error message when I click on the button. If I were to include a subform to display the records, how do I go about writing the codes?

To display the records on the subform instead of the main form, change Line No 9 in the above code as follows:

From this:
Me.RecordSource = strSql

To This:
Me.YourSubformControl.Form.RecordSource = strSql

[Evanescent]

To display the records on the subform instead of the main form, change Line No 9 in the above code as follows:

From this:
Me.RecordSource = strSql

To This:
Me.YourSubformControl.Form.RecordSource = strSql

Thanks a lot for your help!