Printing a detail of a single record (ie report)

[Fendi Baba]

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.

[Jana]

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

[Fendi Baba]

Jana wrote:

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

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

[Fendi Baba]

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

[Bob Alston]

Fendi Baba wrote:

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

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

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

[Fendi Baba]

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

[Fendi Baba]

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

[cjb_kjb]

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.

[Fendi Baba]

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.

[Jana]

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

[Fendi Baba]

Thanks, everyone. The input really helps.