Thank you so much Ken. You have been a great help. If I could trouble you
with one more thing, if you don't mind of course. I have an issue I can't
understand. I created 2 forms, one is called "Invoice Tracker Reports" and
the other is called "Report Page Setup".
On "Invoice Tracker Reports": I have 2 buttons. All Vendors Report and All
Reviewers Report.
On "Report Page Setup": I have an Option group with 2 option buttons.
Portrait and Lanscape.
My intent is to have someone select a button on the Invoice Tracker Reports
form and that will open the Report Page Setup form. If you then select an
option button in the Report Page Setup form, the report will open based on
the orientation you choosed.
Everything works fine up to the point wher you select the option button on
the Report Page Setup form. When I select an option nothing happens. It just
doesn't do anything. I have enclosed the codes for both forms, see if you can
find out what I am doing wrong.
Thanks again for all your help.
Ayo
Invoice Tracker Reports Form Code:
Private Sub AllReveiwerReport_Click()
Dim allReviewerReport As String
DoCmd.OpenForm "Report Page Setup", acNormal, , , , acDialog,
OpenArgs:="allReviewerReport"
DoCmd.Close acForm, "Invoice Tracker Reports", acSaveYes
'DoCmd.OpenReport "Reviewer Report", acViewPreview
End Sub
Private Sub AllVendorReport_Click()
Dim allVendorerReport As String
DoCmd.OpenForm "Report Page Setup", acNormal, , , , acDialog,
OpenArgs:=AllVendorReport
'DoCmd.OpenReport "Vendor Report", acViewPreview
End Sub
Report Page Setup Form Code:
Private Sub Form_Load()
Dim option_AllReviewerReport As String
Dim option_AllVendorReport As String
option_AllReviewerReport = Me.OpenArgs
End Sub
Private Sub optLandscape_GotFocus()
If option_AllReviewerReport = Me.OpenArgs Then
DoCmd.Close acForm, "Report Page Setup", acSaveYes
DoCmd.OpenReport "Reviewer Report Landscape", acViewPreview
End If
End Sub
Private Sub optPortrait_GotFocus()
If option_AllReviewerReport = Me.OpenArgs Then
DoCmd.Close acForm, "Report Page Setup", acSaveYes
DoCmd.OpenReport "Reviewer Report", acViewPreview
End If
End Sub
Ken Sheridan said:
Ayo:
It sounds like you might have unchecked the 'show' box for all the columns
in design view. What you should have in query design view is one column with
the asterisk in its 'field' row and its 'show' box CHECKED, and [Vendor] and
[Market Reviewer] in the 'field' rows of two other columns each with their
'show' box UNCHECKED and the references to the combo boxes in their
'criteria' rows.
Also, as the report is based on the query you don't need the 'filter'
argument when opening it; the references to the combo boxes in the query
provide the filtering. The following will do:
DoCmd.OpenReport "Reviewer by Vendor", acViewPreview
Ken Sheridan
Stafford, England
:
Thanks a lot Ken. I believe I am getting close but not quite there yet. I did
as you instructed and then I placed this code in the button click event:
Private Sub cmdVendor_Reviewer_Report_Click()
DoCmd.OpenReport "Reviewer by Vendor", acViewPreview, "Reviewer by
Vendor"
End Sub
Now I am getting this error massage: Run-time error '3066', "Query must
have at least one destination field." I don't know what that means.
Ayo
:
In query design view add the [Invoice Tracker] table to the query, and then
add the asterisk to the first column of the design grid so it returns all
fields. Then add the [vendor] and [market reviewer] fields to the second and
third columns in the design grid and uncheck their 'show' checkboxes. In the
'criteria' row of the [vendor] column put Forms![Select Vendor and
Reviewer]![cmbVendorName] and in the 'criteria' row of the [market reviewer]
column put Forms![Select Vendor and Reviewer]![cmbMarketReviewer]. Save the
query under a suitable name.
Design the report (you can use the report wizard for this if you wish) and
use the name of query you created as its RecordSource property. If you use
the wizard you'll simply select the query from a list.
To open the report you could put a button on the [Select Vendor and
Reviewer] form. You can use the button wizard to generate the code in its
Click event if you wish, or you can write it yourself. When the button is
clicked the report will open filtered to the vendor and market reviewer
selected in the combo boxes. You might want two buttons in fact, one to
preview the report and one to print it.
BTW I notice that in my last reply I didn't enclose the name of the Market
Reviewer column in brackets in the SQL statement. As it contains a space it
needs these, so should have been [Market Reviewer]. When you design a query
in design view, however, Access will automatically put brackets around all
object names, so you don't have to remember to do this as you would if
writing the query in SQL view.
Ken Sheridan
Stafford, England
:
How do I go about "saving the query with the reference to the controls on the
form as parameters"
:
As the object of the exercise is to produce a report why do you need any
code? Why not just design and save the query with the reference to the
controls on the form as parameters, and design and save a report with the
query as its RecordSource?
If you really want to do it in code, you don't need to create a querydef
object, just design and save the report, called rptInvoice below, first with
the table as its RecordSource, and assign the SQL string to its RecordSource
property transparently, and then open the report, e.g.
On Error GoTo Err_Handler
Const conSQL = SELECT * FROM [Invoice Tracker] " & _
"WHERE Vendor = " & _
"Forms![Select Vendor and Reviewer]![cmbVendorName] " & _
"AND Market Reviewer = " & _
"Forms![Select Vendor and Reviewer]![cmbMarketReviewer]"
Application.Echo False
DoCmd.OpenReport "rptInvoice", acViewDesign
Reports("rptInvoice").RecordSource = conSQL
DoCmd.Close acReport, "rptInvoice", acSaveYes
Application.Echo True
DoCmd.OpenReport "rptInvoice", acViewPreview
Exit_Here:
Exit Sub
Err_Handler:
Application.Echo True
MsgBox Err.Description
Resume Exit_Here
The above includes the references to the controls on the form as part of the
constant literal string. You could, however, alternatively use a variable
rather than a constant and concatenate the values of the controls into the
string expression, making sure you wrap them in quotes characters if they are
text.
Ken Sheridan
Stafford, England
:
I am looking to create a query in VBA code using values from ComboBoxes on a
form and use the resulting query to generate a report. So far I have the
following snippets of code but there is an error and I can't figure out what
I am doing wrong. Can any one help?
Private Sub cmdVendor_Reviewer_Report_Click()
Dim dbs As Database: Dim qdf As QueryDef: Dim strSQL As String
Set dbs = CurrentDb
strSQL = "SELECT * FROM [Invoice Tracker] WHERE ((([Invoice
Tracker].Vendor)=" & _
"Forms![Select Vendor and Reviewer]![cmbVendorName]" & ")AND ((" & _
"([Invoice Tracker].Market Reviewer)=" & "Forms![Select Vendor and
Reviewer]![cmbMarketReviewer]" & "));"
Set qdf = dbs.CreateQueryDef("SecondQuarter", strSQL)
'DoCmd.OpenQuery "SecondQuarter", , acReadOnly
DoCmd.OpenQuery qdf, , acReadOnly
End Sub
Thanks
