Hi,
I currently have a SOP database which holds all of my customer discount information. The discount information is broken down into three different tables due to the data being incompatible. As a result I have generated three different reports which has a different page for each customer which has that particular discount. Not all customers have a discount or have one or two out of the three.
So far I have written this:
Private Sub Command0_Click()
'Print current record
If IsNull(Me!txtAccountNo) Then
MsgBox "Please select a valid record", _
vbOKOnly, "Error"
Exit Sub
End If
DoCmd.OpenReport "rptInHouseReports_DiscountCodes", , , _
"[CU Search Name] = """ & Forms![frmDiscount].[txtAccountNo] & """"
DoCmd.OpenReport "rptInHouseReports_DiscountProducts", , , _
"[CU Search Name] = """ & Forms![frmDiscount].[txtAccountNo] & """"
DoCmd.OpenReport "rptInHouseReports_DiscountRolls", , , _
"[CU Search Name] = """ & Forms![frmDiscount].[txtAccountNo] & """"
End Sub
This checks to see if an account number has been entered in the text box and then prints the reports. It currently tries to print all three reports even if no data is present within each report.
What I need to end up with is the ability to select a customer as we currently have but only print the reports which are relevant to the customer. We also more importantly at the moment need to be able to print all customers which have data in one or more of the reports.
I then edited the code which should close the report automatically if no data is found (or so I am told!):
'Print current record
If IsNull(Me!txtAccountNo) Then
MsgBox "Please select a valid record", _
vbOKOnly, "Error"
Exit Sub
End If
On Error Resume Next
DoCmd.OpenReport "rptInHouseReports_DiscountCodes", , , _
"[CU Search Name] = """ & Forms![frmDiscount].[txtAccountNo] & """"
If Err = 2501 Then Err.Clear
On Error Resume Next
DoCmd.OpenReport "rptInHouseReports_DiscountProducts", , , _
"[CU Search Name] = """ & Forms![frmDiscount].[txtAccountNo] & """"
If Err = 2501 Then Err.Clear
On Error Resume Next
DoCmd.OpenReport "rptInHouseReports_DiscountRolls", , , _
"[CU Search Name] = """ & Forms![frmDiscount].[txtAccountNo] & """"
If Err = 2501 Then Err.Clear
End Sub
I logically need to insert a loop into the code so that it will cycle through all of the records. The easiest way I can think of doing this is to go through every record in the customer table (tblCustomers) to see if there is any data in each report and if yes print if not move on to the next report or customer. Is there an easy way to do this?
I have looked through the other threads relating to printing multiple reports for a record but I cant get my head around how it relates to what I am trying to achieve!
Any help is much appreciated!
Thanks in advance
I currently have a SOP database which holds all of my customer discount information. The discount information is broken down into three different tables due to the data being incompatible. As a result I have generated three different reports which has a different page for each customer which has that particular discount. Not all customers have a discount or have one or two out of the three.
So far I have written this:
Private Sub Command0_Click()
'Print current record
If IsNull(Me!txtAccountNo) Then
MsgBox "Please select a valid record", _
vbOKOnly, "Error"
Exit Sub
End If
DoCmd.OpenReport "rptInHouseReports_DiscountCodes", , , _
"[CU Search Name] = """ & Forms![frmDiscount].[txtAccountNo] & """"
DoCmd.OpenReport "rptInHouseReports_DiscountProducts", , , _
"[CU Search Name] = """ & Forms![frmDiscount].[txtAccountNo] & """"
DoCmd.OpenReport "rptInHouseReports_DiscountRolls", , , _
"[CU Search Name] = """ & Forms![frmDiscount].[txtAccountNo] & """"
End Sub
This checks to see if an account number has been entered in the text box and then prints the reports. It currently tries to print all three reports even if no data is present within each report.
What I need to end up with is the ability to select a customer as we currently have but only print the reports which are relevant to the customer. We also more importantly at the moment need to be able to print all customers which have data in one or more of the reports.
I then edited the code which should close the report automatically if no data is found (or so I am told!):
'Print current record
If IsNull(Me!txtAccountNo) Then
MsgBox "Please select a valid record", _
vbOKOnly, "Error"
Exit Sub
End If
On Error Resume Next
DoCmd.OpenReport "rptInHouseReports_DiscountCodes", , , _
"[CU Search Name] = """ & Forms![frmDiscount].[txtAccountNo] & """"
If Err = 2501 Then Err.Clear
On Error Resume Next
DoCmd.OpenReport "rptInHouseReports_DiscountProducts", , , _
"[CU Search Name] = """ & Forms![frmDiscount].[txtAccountNo] & """"
If Err = 2501 Then Err.Clear
On Error Resume Next
DoCmd.OpenReport "rptInHouseReports_DiscountRolls", , , _
"[CU Search Name] = """ & Forms![frmDiscount].[txtAccountNo] & """"
If Err = 2501 Then Err.Clear
End Sub
I logically need to insert a loop into the code so that it will cycle through all of the records. The easiest way I can think of doing this is to go through every record in the customer table (tblCustomers) to see if there is any data in each report and if yes print if not move on to the next report or customer. Is there an easy way to do this?
I have looked through the other threads relating to printing multiple reports for a record but I cant get my head around how it relates to what I am trying to achieve!
Any help is much appreciated!
Thanks in advance
Last edited:
