View Report of the Search Result on Search Form: MS Access
The customer data shown on the Search Form is come from the search result of Search or Show All button which assigns the result from the SQL SELECT statement to the Record Source of a current form.
If we have a customer report with a Record Source of tbl_Customer, the original Record Source on report and the Record Source on the search form are different. If we want to open a report showing only the customer data that shows on this search form then we need to create temp record source from the search result data and pass it to a report. In this How To, I will create a temp table of Customer table and pass the result data from the search keyword to this temp table. Every time before proceeding the search, we need to delete all data from the temp table and assign a new data from the search result.
1. Copy table tbl_Customer and rename it as tbl_Customer_Temp. The tbl_Customer_Temp table will have the same fields and same customer information.
2. Create a report name it as CustomerList with a Record Source = tbl_Customer_Temp. The completed report will look like the picture below:
Other statin side effects have been seen, however, which, although rare, are still of some concern. purchase cialis other levitra 20 mg Ideal dose of Penegra is one tablet 45 to 60 minutes before an act of intercourse but it shall not be taken regularly. So if you’re one of those who suffer from find to find out more now online cialis soft erectile dysfunction frequently, no sex is possible and relation crisis is inevitable between them. This is most benefited in the solution generic cialis canadian for white discharge.
3. Under the Search Form, update the VBA code that search for a customer name under the Search button as shown below:
Private Sub Command163_Click() Dim strsearch As String Dim Task As String Dim strDelete As String Dim strAppend As String If IsNull(Me.txtSearch) Or Me.txtSearch = "" Then MsgBox "Please type in your search keyword.", vbOKOnly, "Keyword Needed" Me.txtSearch.BackColor = vbYellow Me.txtSearch.SetFocus Else 'disable warning alert 'Delete all records from the tbl_Customer_Temp before search DoCmd.SetWarnings False strDelete = "Delete * from tbl_customer_Temp" DoCmd.RunSQL strDelete 'Use the SELECT Statement to assign data to the record source to Task strsearch = Trim(Me.txtSearch) Task = "Select * FROM tbl_customer WHERE ((CustomerName Like ""*" & strsearch & "*""))" Me.RecordSource = Task Me.txtSearch.BackColor = vbWhite 'Append data matching keyword from the search result into the tbl_Customer_Temp strAppend = "INSERT INTO tbl_Customer_Temp SELECT * FROM tbl_customer WHERE ((CustomerName Like ""*" & strsearch & "*""))" DoCmd.RunSQL strAppend DoCmd.SetWarnings True End If End Sub
On the example, there are two customers found from the result of searching for a keyword “john”
4. Under the Preview Report button, put a command to open a report “CustomerList” as shown below with the result showing on the report preview.
Private Sub Command220_Click() DoCmd.OpenReport "CustomerList", acViewPreview End Sub
5. Under the Show All button, update the VBA code as shown below and the result will be showing on the report preview.
Private Sub Command94_Click() Dim Task As String Dim strDelete As String Dim strAppend As String 'Disable warning alert 'Delete all records from the tbl_Customer_Temp before search DoCmd.SetWarnings False strDelete = "Delete * from tbl_customer_Temp" DoCmd.RunSQL strDelete strsearch = Trim(Me.txtSearch) Task = "Select * FROM tbl_customer" Me.RecordSource = Task Me.txtSearch.BackColor = vbWhite 'Append all data from the search result to the tbl_Customer_Temp strAppend = "INSERT INTO tbl_Customer_Temp SELECT * FROM tbl_customer" DoCmd.RunSQL strAppend DoCmd.SetWarnings True End Sub
