Form Criteria for Report

MArgente · June 8th, 2007, 11:03 AM

Hello all,

I'm relatively new to Access/VBA, but I've been reading up a lot but I'm having some issues.

I have a form called Report To Issuer Input Screen. And the form has 2 input fields (1 is a combo box (cboCompanyID), and 1 is a text box (txtDate)). The button that is supposed to trigger the report is:

Code:

Dim strReportName As String
strReportName = "Report To Issuer"
DoCmd.OpenReport strReportName, acViewPreview

On the report, I need to take information from two tables based on cboCompanyID and txtDate. This was the code to generate the recordsource:

Code:

Me.RecordSource = "SELECT Company.[Company Short Name], Company.[1st Contact Name], " & _
" Company.[1st Contact Address], Company.[1st Contact Address2], Company.[1st Contact Address3], " & _
" Company.[1st Contact Telephone], Company.[1st Contact Fax], Company.[1st Contact E-mail Address], " & _
" deceasedHolder.[Next Put Date], deceasedHolder.[CUSIP Number] " & _
" FROM deceasedHolder INNER JOIN Company ON deceasedHolder.[Company ID] = Company.[Company ID]" & _
" WHERE Company.[Company ID] = '" & Forms![Report To Issuer Input Screen]![cboCompanyID] & "'" & _
" AND deceasedHolder.[Next Put Date] = #" & Forms![Report To Issuer Input Screen]![txtDate] & "#;"

However, when I run this code, I get prompted for parameter inputs. I believe (from reading around) that it is based on the items I want that isn't supplied in the where clause. How can I generate my recordsource without asking the user for parameters (since that's what the form is supposed to be used for.)

Thanks all for your help
-Mark

dartcoach · June 8th, 2007, 11:25 AM

Mark,

Are you closing the form before running the report?

Kevin

dartcoach

MArgente · June 8th, 2007, 11:34 AM

dartcoach,

No, I am not closing the form before running the report.
The last line right before the report is executed is the DoCmd.OpenReport

MArgente · June 8th, 2007, 11:39 AM

I also had a different method where I had

Code:

Dim strCriteria
strCriteria ="[Company ID] = '" & Me![CompanyIDDisplay] & "'" & _
   " AND [Next Put Date] = #" & Me![Put date] & "#"
DoCmd.OpenReport strReportName, acViewPreview, , strCriteria

And the code on the report had everything minus the where clause. I also got the same problem.
If I did it this way, passing strCriteria, would I have to change the parameters of the subroutine for
Private Sub Report_Open(Cancel As Integer)?

Thanks again.

dartcoach · June 8th, 2007, 11:41 AM

Mark,

Can you post the complete code behind your command button?

Kevin

dartcoach

MArgente · June 8th, 2007, 11:50 AM

Command Button

Code:

Private Sub GenerateReport_Click()

Dim strReportName As String
Dim strCriteria As String
strReportName = "Report To Issuer"
'strCriteria ="[Company ID] = '" & Me![cboCompanyID] & "'" & _
'   " AND [Next Put Date] = #" & Me![txtDate] & "#"

DoCmd.OpenReport strReportName, acViewPreview
End Sub

Report

Code:

Private Sub Report_Open(Cancel As Integer)


Me.RecordSource = "SELECT Company.[Company Short Name], Company.[1st Contact Name], " & _
" Company.[1st Contact Address], Company.[1st Contact Address2], Company.[1st Contact Address3], " & _
" Company.[1st Contact Telephone], Company.[1st Contact Fax], Company.[1st Contact E-mail Address], " & _
" deceasedHolder.[Next Put Date], deceasedHolder.[CUSIP Number] " & _
" FROM deceasedHolder INNER JOIN Company ON deceasedHolder.[Company ID] = Company.[Company ID]" & _
" WHERE Company.[Company ID] = '" & Forms![Report To Issuer Input Screen]![cboCompanyID] & "'" & _
" AND deceasedHolder.[Next Put Date] = #" & Forms![Report To Issuer Input Screen]![txtDate] & "#;"

End Sub

dartcoach · June 8th, 2007, 12:03 PM

Mark,
Instead of creating the recordsource on the open, create a query and use it as the record source. In the query, use your form fields as criteria.

HTH

Kevin

dartcoach

MArgente · June 8th, 2007, 12:08 PM

Sorry, As I said, I'm quite new at using Access 2003/VBA

Would I be creating the query in the vba code, like..

Code:

Dim strQuery As String
strQuery = (whatever)
DoCmd.OpenQuery strQuery

dartcoach · June 8th, 2007, 12:14 PM

No, create an actual query with the query wizard.

Kevin

dartcoach

MArgente · June 8th, 2007, 12:27 PM

Such a simple solution, Worked great.

Thanks