Print report based on value of 2 combo boxes

[Guest]

I have a table called Computers. I have a report called AllComputers. I have
a form called MakeModel. The Table has 2 fields called Make and Model. I have
2 unbound combo boxes on my table and a print report cmd button. I want to
select a make and model of computer in the 2 combo boxes and get the report
called AllComputers to print only those records that match the values in the
combo boxes. I also want it to sort the info based on the model.

Any help would be greatly appreciated.

Thanks

Ralph Malph

 

[Al Camp]

Ralph,
An oft asked question...
Keep the form open as yourun the report. Use the value from your combo
boxes on the form to filter the query behind your report.
In the query Make field, use a criteria of (use your own names)
= Forms!frmMakeModel!cboMake
In the query Model field...
= Forms!frmMakeModel!cboModel

 

[Guest]

Al,

Thanks for the help. However, the formula (Syntax) that you gave me seems to
be for a query. What I have is the following code:

stDocName = "AllComputers"
DoCmd.OpenReport stDocName, acPreview, ,

What I need is the syntax for the rest of the DoCmd.Openreport..... code
where I tell the report to only show me the records in table "Computers"
whose fields "Make" and "Model" match the values selected in my combo boxes.

The report "AllComputers" will be used for many different queries, from "all
the computers in the table" (It already does that) to a "particular computer"
(The one currently showing in my data input/viewing form which is different
from the form with my unbound combo boxes wherr I am issuing the OpenReport
command), I have the syntax for that. To any combination of criteria such as
the make and model, or the OS, or the CPU type and speed, etc. This is why I
need the DOCmd.OpenReport..... syntax. I am using Access 2000.

I would also like to sort the results based on the Model.

Thanks again for the help.

Ralph Malph

 

[Al Camp]

Try this Ralph,
(use your own names)
Dim Arg1 As String, Arg2 As String
Arg1 = "Make = Forms!frmYourForm!Make"
Arg2 = "Model = Forms!frmYourForm!Model
DoCmd.OpenReport "rptYourReport", acViewPreview, , Arg1 & " And " & Arg2

 

[Guest]

Al,

Thanks for all your help. I tried to use your syntax but it gave me a prompt
for more input. After much experementing, between your methods and a few
other examples I have been able to find I came up with the following code
which seems to do most of what I need in terms of getting the report.

DoCmd.OpenReport stDocName, acViewPreview, , "(make= '" & Me.CmbMake & "'" &
" And model= '" & Me.CmbModel & "')"

What it does not do is close the input form after the report appears. I have
tried to use the "DoCmd.Close" command right after the Do.Cmd.OpenReport...
command, but all I get is a flash on the screen and no report and the input
form still open. What I am hoping to have happen is the following. In my main
data input/view form I have a section for reports. One of the options is for
a report based on a couple of user selected variables (Make and Model). When
this button is clicked it opens a new form which will let the user select the
make and model they want the report based on via a couple of unbound combo
boxes that gets its options from a special table I use to keep track of all
the make and models that have been input in to the system. After the
selection has been made the user will click "OK" and the report should appear
and the input form should close. I more or less have the report showing, but
the form does not close once the report shows up etc. Any ideas on what I am
doing wrong?

Thanks again for all your help.

Ralph Malph

 

[Al Camp]

Ralph,
You should have included the code, based on mine, that you used.
Since I can't see your interpretation of my code, I can't diagnose what
the problem might have been.

I just tested this code to open a report with 2 conditions from an Open
form, and it worked fine.

Private Sub cmdTest_Click()
Dim Arg1 As String, Arg2 As String
Arg1 = "LastName = Forms!frmAddresses!LastName"
Arg2 = "State = Forms!frmAddresses!State"
DoCmd.OpenReport "rptChristmasList", acViewPreview, , Arg1 & " And " &
Arg2
End Sub

When the report runs, the form with Make and Model must remain open. If
you want to close that one, do so after the report has closed. Try this
using the same example...

Private Sub cmdTest_Click()
Dim Arg1 As String, Arg2 As String
Arg1 = "LastName = Forms!frmAddresses!LastName"
Arg2 = "State = Forms!frmAddresses!State"
DoCmd.OpenReport "rptChristmasList", acViewPreview, , Arg1 & " And " &
Arg2, , "frmAddresses"
End Sub

I've used the OpenArgs argument to indicate to the report, the form that
asked for the report.
On the report, place this code in the OnClose event...

If OpenArgs = "frmAddresses" then
Close Openargs
End If

 

[Al Camp]

Ralph,
That Close code should be...
DoCmd.Close acForm, OpenArgs

 

[Guest]

Thanks Al,

That helps.

I have goten a long way since my last post on this subject. I will post more
info and let you know how I with your help, have solved my problems. It will
be a few days at least as I have a bunch of day to day work piling up
delaying my programing work.

Thanks again,

Bob