Wrox Programmer Forums
|
Access Discussion of Microsoft Access database design and programming. See also the forums for Access ASP and Access VBA.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Access section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old June 8th, 2007, 11:03 AM
Authorized User
 
Join Date: Jun 2007
Posts: 21
Thanks: 0
Thanked 0 Times in 0 Posts
Default Form Criteria for Report

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

 
Old June 8th, 2007, 11:25 AM
Friend of Wrox
 
Join Date: Jan 2005
Posts: 471
Thanks: 0
Thanked 1 Time in 1 Post
Default

Mark,

Are you closing the form before running the report?

Kevin

dartcoach
 
Old June 8th, 2007, 11:34 AM
Authorized User
 
Join Date: Jun 2007
Posts: 21
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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

 
Old June 8th, 2007, 11:39 AM
Authorized User
 
Join Date: Jun 2007
Posts: 21
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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.

 
Old June 8th, 2007, 11:41 AM
Friend of Wrox
 
Join Date: Jan 2005
Posts: 471
Thanks: 0
Thanked 1 Time in 1 Post
Default

Mark,

Can you post the complete code behind your command button?

Kevin

dartcoach
 
Old June 8th, 2007, 11:50 AM
Authorized User
 
Join Date: Jun 2007
Posts: 21
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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
 
Old June 8th, 2007, 12:03 PM
Friend of Wrox
 
Join Date: Jan 2005
Posts: 471
Thanks: 0
Thanked 1 Time in 1 Post
Default

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
 
Old June 8th, 2007, 12:08 PM
Authorized User
 
Join Date: Jun 2007
Posts: 21
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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


 
Old June 8th, 2007, 12:14 PM
Friend of Wrox
 
Join Date: Jan 2005
Posts: 471
Thanks: 0
Thanked 1 Time in 1 Post
Default

No, create an actual query with the query wizard.

Kevin

dartcoach
 
Old June 8th, 2007, 12:27 PM
Authorized User
 
Join Date: Jun 2007
Posts: 21
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Such a simple solution, Worked great.

Thanks






Similar Threads
Thread Thread Starter Forum Replies Last Post
Access VBA Report Criteria anterior Access VBA 17 October 24th, 2007 07:13 AM
How to pass filter form criteria to a sub report?? Jabba007 Access 0 July 12th, 2007 09:11 PM
Passing Criteria to a Sub Report from Filter Form Jabba007 Access VBA 0 July 11th, 2007 04:56 PM
Multiple criteria for a Report stealthdevil Access VBA 33 June 8th, 2006 10:38 AM
Print selection criteria on report sjperl VB Databases Basics 1 May 3rd, 2004 11:41 PM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.