Report based on Combobox selection

[MentalDrow]

Hi all,

I'm sorry if my question is a repeat but I've tried using the various
different approaches listed in this group and just don't seem to be getting
it.

I have a report that has data pulled via query from two different tables. I
have thrown sample records into it using the forms I created for data entry.
If I run the report it asks for the Primary Key number; this is coded into
the query. If I enter the number manually, I get a report that shows all the
info I want that is associated with that particular Key number.
I have created an unbound form with an unbound combobox. The primary key
column width is 0 with DriverLast and DriverFirst set at 1" each. The
primary key is not listed in the report and is an AutoNumber generated when
the driver info is input. I have a command button as all questions and
comments in this group seem to point out to needing that.
How do I set this up so that when someone runs the report the combobox form
opens, the user selects the info based on DriverLast and DriverFirst, and the
report opens with the info associated with DriverLast and DriverFirst
selected from the combobox.

Can anyone help me with coding the Report, Query, Command Button and
possibly the Combobox. Thanks.

Chaz

 

[Larry Linson]

Hi all,

I'm sorry if my question is a repeat but I've tried using the various
different approaches listed in this group and just don't seem to be
getting
it.

I have a report that has data pulled via query from two different tables.
I
have thrown sample records into it using the forms I created for data
entry.
If I run the report it asks for the Primary Key number; this is coded into
the query. If I enter the number manually, I get a report that shows all
the
info I want that is associated with that particular Key number.
I have created an unbound form with an unbound combobox. The primary key
column width is 0 with DriverLast and DriverFirst set at 1" each. The
primary key is not listed in the report and is an AutoNumber generated
when
the driver info is input. I have a command button as all questions and
comments in this group seem to point out to needing that.
How do I set this up so that when someone runs the report the combobox
form
opens, the user selects the info based on DriverLast and DriverFirst, and
the
report opens with the info associated with DriverLast and DriverFirst
selected from the combobox.

Can anyone help me with coding the Report, Query, Command Button and
possibly the Combobox. Thanks.

It appears you have properly set up the Combo Box, so all you should need is
code in the AfterUpdate event and to take the Criteria out of the Query...
you are going to create a "WhereCondition" argument for your
DoCmd.OpenReport to take care of that. And, remember that coding is precise,
so you will have to be sure to replace the names I assume for your ComboBox,
cboSel, and Report, rptDrv, and the Table's Primary Key, lngKey, with the
actual names you use. I am also assuming that the order in which the Fields
appear in the RowSource of the Combo is the order you've given: primary key,
DriverLast, and DriverFirst... because the DriverLast and DriverFirst are
only used to let the user select the proper primary key, they won't have to
be used in the code.

In the after update event of the cboSel, put the following

Dim strWhere As String
strWhere = "[lngKey] = " & Me.cboSel
DoCmd.OpenReport "rptDrv", acViewPreviw, , strWhere

You may want to add some error handling code, too. If I don't have specific
errors I want to field and do something about, I just issue a MsgBox with
the Err.Number and Err.Description for the user's information.

Larry Linson
Microsoft Access MVP

 

[MentalDrow]

Hi all,

I'm sorry if my question is a repeat but I've tried using the various
different approaches listed in this group and just don't seem to be getting
it.

I have a report that has data pulled via query from two different tables. I
have thrown sample records into it using the forms I created for data entry.
If I run the report it asks for the Primary Key number; this is coded into
the query. If I enter the number manually, I get a report that shows all the
info I want that is associated with that particular Key number.
I have created an unbound form with an unbound combobox. The primary key
column width is 0 with DriverLast and DriverFirst set at 1" each. The
primary key is not listed in the report and is an AutoNumber generated when
the driver info is input. I have a command button as all questions and
comments in this group seem to point out to needing that.
How do I set this up so that when someone runs the report the combobox form
opens, the user selects the info based on DriverLast and DriverFirst, and the
report opens with the info associated with DriverLast and DriverFirst
selected from the combobox.

Can anyone help me with coding the Report, Query, Command Button and
possibly the Combobox. Thanks.

Chaz

Well, as it turns out, all the information was right there in front of me.
Thanks anyway Larry. I thought I was coding it wrong as far as the Click,
Open, Close etc. I could get all the things to pop up as expected but the
reports were opening blank except for the headers and such. I had the wrong
bound column on my combo box Can we say DOH!? Yes we can. Thanks again
Larry, you and the rest of the problem solvers really make Access (and the
other Office Products) worth learning to use. I'm hoping you guys/gals will
end up teaching me enough to turn around and start helping other folks.

Chaz