Printing a detail of a single record (ie report)

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Fendi Baba - 13 Jan 2006 18:21 GMT

I am new to MS Access so please bear with me. I am trying to create a
report format and attach a button to a form. This would users to
print-out a record that they are looking at. I can report a report
which print out the entire database so how do I limit it do just the
record that the user see on screen?

Thanks for the tip.

Reply to this Message

Jana - 13 Jan 2006 19:28 GMT

Fendi:

In the VB code for your form button, find the line that opens your
report, and change it like this (all one line):

DoCmd.OpenReport stDocName, acPreview,,"Put Your Criteria Here ="
& Me!FieldName

Replace the "Put Your Criteria Here" with the criteria for your report
to limit the records returned, such as "[RecordID] = " Me!RecordID.
The Criteria portion is basically a where clause without the word
WHERE.

HTH,
Jana

Reply to this Message

Fendi Baba - 14 Jan 2006 02:22 GMT

> Fendi:
>
[quoted text clipped - 11 lines]
> HTH,
> Jana

Thank you Jana. I'll give it a go.

Reply to this Message

Fendi Baba - 15 Jan 2006 14:07 GMT

Jana

I tried this and somehow I am not getting the right result.

Dim stDocName As String
stDocName = "dbo_School Query"
DoCmd.OpenReport stDocName, acPreview, , [SchoolID] = Me!SchoolID,
acDialog

I tried diffent combination of schoolID and even hard coding the
Me!SchoolID but the result is still the same, the report generates for
all the schools in my database.

Do I need to do anything to the report format itself?

Regards

Reply to this Message

Bob Alston - 15 Jan 2006 14:27 GMT

> Jana
>
[quoted text clipped - 12 lines]
>
> Regards

Surround this text: [SchoolID] = Me!SchoolID
by quotes like:

DoCmd.OpenReport stDocName, acPreview, , "[SchoolID] = Me!SchoolID",
acDialog

Reply to this Message

Fendi Baba - 15 Jan 2006 14:54 GMT

Bob

Thanks that worked. However, can I asked what does Me!SchoolID do? It
doesn't seem to pick up the schoolid field on the form but instead
requires me to enter a value is a prompt box.

Regards

Reply to this Message

Fendi Baba - 15 Jan 2006 14:58 GMT

Resolved. I found the code. I changed this portion to
"[SchoolID]=forms!frmSchoolData.SchoolID"

Reply to this Message

cjb_kjb - 17 Jan 2006 05:45 GMT

I think it shoud be:
"SchoolID = " & me!SchoolID. (better than
"[SchoolID]=forms!frmSchoolData.SchoolID" - if for instance you change
the name of the form the report may stop working).

so assuming SchoolId is 147 on the form this will be passed as a
WhereCondition to the report as "SchoolId = 147", i.e. the value of
SchoolId is resolved before the WhereCondition is passed - so it gets
passed as a literal.

You are passing to the report "SchoolId = forms!frmSchoolData.SchoolId"
which also works because the report resolves forms!SchoolData.SchoolId
by looking into the named form for field SchoolId.

If you pass "SchoolId = me!schoolID" (all in quotes) then in the report
- 'Me' is no longer the form but the report (i.e. the current object)
and the fact that it gives a popup box means it cant find a field
SchoolId to get the value from.

You also need Filter On set to yes in the report for it to work.

Reply to this Message

Fendi Baba - 17 Jan 2006 06:41 GMT

Thanks, I appreciate the fact you explained how Me!SchoolID worked. So
basically i hv to put it outside the quotes. I'll give it a go.

Regards.

Reply to this Message

Jana - 17 Jan 2006 20:52 GMT

Fendi:

Sorry for the delay in checking this thread...haven't been online in a
while.

Here's the proper way to have your line of code (all one line):
DoCmd.OpenReport stDocName, acPreview, , "[SchoolID] = " & Me!SchoolID

The & causes a concatenation of the [SchoolID] = and your SchoolID on
the form. The , acDialog should not be there, as that particular
parameter only applies to the DoCmd.OpenForm method. Report viewing
options are specified where you have the VB constant acPreview.

HTH,
Jana

Reply to this Message

Fendi Baba - 19 Jan 2006 15:35 GMT

Thanks, everyone. The input really helps.

Reply to this Message