How do you print a single record?

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I would like the users of my dbase to print the record they are viewing on a
report.
Is there a way to do this either through macro or code?
 
Tim,

Yes, you can do this, both in a macro or in a VBA procedure. In both
cases, the approach is similar, and you will need to refer to the value
of the Primary Key field in your current record. In a macro, use the
OpenReport action, and then in the Where Condition argument of the macro
design, put the equivalent of this...
[YourID]=[Forms]![NameOfYourForm]![YourID]
In code, you would do like this...
DoCmd.OpenReport "ReportName", , , "[ID]=" & Me.ID
Obviously, substitute the actual names of your report, form, and id
field. I imagine you would put this macro or code on the Click event of
a command button on your form?
 
I'm pretty sure I have the code right but it still is printing all the records.
I am printing from a query and there is now primary field set.
Is that the problem? Because I don't know how to assign the primary key to a
query.

Steve Schapel said:
Tim,

Yes, you can do this, both in a macro or in a VBA procedure. In both
cases, the approach is similar, and you will need to refer to the value
of the Primary Key field in your current record. In a macro, use the
OpenReport action, and then in the Where Condition argument of the macro
design, put the equivalent of this...
[YourID]=[Forms]![NameOfYourForm]![YourID]
In code, you would do like this...
DoCmd.OpenReport "ReportName", , , "[ID]=" & Me.ID
Obviously, substitute the actual names of your report, form, and id
field. I imagine you would put this macro or code on the Click event of
a command button on your form?

--
Steve Schapel, Microsoft Access MVP

I would like the users of my dbase to print the record they are viewing on a
report.
Is there a way to do this either through macro or code?
Click to expand...
Click to expand...
 
Nice web site Jeff.
I copied your code from the site and made the changes to it as I thought
correct but it's not working. Like I mentioned to Steve Shepard, I don't have
a primary key and the common key that is used in the qry driving the report
is a text field - does this have anything to do with my problem?
 
Tim,

Bottom line here is: if you want the report to only print for a certain
record, or certain records, then the report's record source obviously
needs to contain the data which will be used as the basis of selecting
which record(s) will be printed. Your original question referred to
"the record they are viewing". We have to presume that "the record they
are viewing " contains data which can identify that record, and that
this data is also in the report, so that the seected record can be
identified and printed. So, probably you can only get a general answer
on the basis of general information, so if you need more specific help
with this, you willneed to provide more details, with examples, code,
and the SQL view of your query.
 
Steve,
Sorry about being so vague.
I have a pretty complex database and sometimes I don't know where to start
with my question.
A little history: This datbase is comprised of about 7 tables with SEVERAL
queries layered upon each other in order to do complex tax calculations. The
"unique" key or field that identifies each record is created from the queries
by concatenating two fields and so on..
At the end of the day now when the user is done entering in all the data
through the queries which eventually populate the underlying tables..

The report would be driven off of a final query which uses tmp_tables that
are created by a macro of make table queries using the select queries (this
was the only way to compile all the information from the queries or else
Access couldn't handle the complexity)
However I can get them to choose the record via dialog box/form, or click a
button to print and then be prompted with a dropdown box of some sort to
choose the record (aside from just leaving the criteria with a [Enter ID]) is
my goal.
 
Tim,

Sure, that sounds good. Basically all you need to do is work out the
basis on which the report record for printing will be selected. If it's
on the basis of the value in an ID field, then you will need to identify
that ID value on the form. This can be either on the basis of the value
of a field in the current record on the form, or, as you suggest, the
value of a combobox where the user makes a selection. Once you have
that value nobbled on the form, then you can do it 2 ways. One is to
use this value in the Where Condition argument of the OpenReport
macro/procedure, as I mentioned earlier. The other is to refer to this
value, using syntax such as [Forms]![NameOfForm]![NameOfCombobox/Field],
in the criteria of the query that the report is based on.
 
in message:
Nice web site Jeff.
I copied your code from the site and made the changes to it as I thought
correct but it's not working. Like I mentioned to Steve Shepard, I don't have
a primary key and the common key that is used in the qry driving the report
is a text field - does this have anything to do with my problem?
Click to expand...

Looks like Steve has you covered pretty well Tim.
The link I provided does assume a numeric ID field for the table.
For text you would use something like so as the Where clause:

DoCmd.OpenReport strReport, , , "[IDField]= """ & Me.IDTextBox & """"

If your ID text field is on the form, the report will only display the one
record that matches the text box ID value.
 
Back
Top