Print current record

F

Fred Worthington

Greetings:

I have a Report that is linked to a Query. The Report (for example, a
packing slip or form letter) will contain a range of information relevant to
a single account. I want to place a command button in a Form that will open
the Report and Print only the current (open) record. How can I do this?

Thanks . . . Fred
 
C

Chuck

Greetings:

I have a Report that is linked to a Query. The Report (for example, a
packing slip or form letter) will contain a range of information relevant to
a single account. I want to place a command button in a Form that will open
the Report and Print only the current (open) record. How can I do this?

Thanks . . . Fred
Click to expand...

The report can not print anything that is not present in the record source, in
your case the query. Open the query with the form still open and make sure the
only record showing is the one from the form.

If no, query design is flawed.

If yes, open report in design mode and make sure the record source for the
report is the open query.

If yes, I'm stumped.

Chuck
--
 
F

Fred Worthington

Chuck,

When I open the query with the form open I am still seeing all the records.
As such, you suggest my query design is flawed. The query is relatively
simple containing name and address fields as well as the primary key ID.
Can you tell me what parameter is missing from my query that will make it
return just the record whose form is open?

Thanks . . . Fred
 
F

fredg

Greetings:

I have a Report that is linked to a Query. The Report (for example, a
packing slip or form letter) will contain a range of information relevant to
a single account. I want to place a command button in a Form that will open
the Report and Print only the current (open) record. How can I do this?

Thanks . . . Fred
Click to expand...

Your table should have a unique prime key field.
In my example it is named [RecordID].

On the command button's property sheet write
[Event Procedure]
on the Click event line.
Then click on the little button with 3 dots that will appear on that
line.
When the code window opens, the cursor will be flashing between 2
already existing lines of code.
Between those 2 lines write:

DoCmd.RunCommand acCmdSaveRecord
DoCmd.OpenReport "ReportName", acViewPreview, , "[RecordID] = " &
[RecordID]

The above assumes a [RecordID] field that is a Number Datatype.

If, however, [RecordID] is Text Datatype, then use:

DoCmd.OpenReport "ReportName", acViewPreview, ,"[RecordID] = '" &
[RecordID] & "'"

as the Where clause.

For clarity, the single and double quotes are..
"[RecordID] = ' " & [RecordID] & " ' "
Change [RecordID] to whatever the actual field name is that you are
using.

See VBA Help files for:
Where Clause + Restrict data to a subset of records'
 
F

Fred Worthington

I have attempted to follow your instructions, but Access (2003) tends to
prevent me from entering the text precisely as you have suggested. This is
what I have:
Private Sub PreviewPackingSlipButton_Click()

On Error GoTo Err_PreviewPackingSlipButton_Click

DoCmd.RunCommand acCmdSaveRecord

DoCmd.OpenReport "Packing Slip", acViewPreview, ,
"DonatedStoreItemID"


Exit_PreviewPackingSlipButton_Click:

Exit Sub

Err_PreviewPackingSlipButton_Click:

MsgBox Err.Description

Resume Exit_PreviewPackingSlipButton_Click


End Sub


Though my command button asks for the record ID, it still opens all Reports
instead of the one associated with the open Record. Perhaps you can spot my
error and recommend a correction.

Thanks . . . Fred


fredg said:
Greetings:

I have a Report that is linked to a Query. The Report (for example, a
packing slip or form letter) will contain a range of information relevant to
a single account. I want to place a command button in a Form that will open
the Report and Print only the current (open) record. How can I do this?

Thanks . . . Fred
Click to expand...

Your table should have a unique prime key field.
In my example it is named [RecordID].

On the command button's property sheet write
[Event Procedure]
on the Click event line.
Then click on the little button with 3 dots that will appear on that
line.
When the code window opens, the cursor will be flashing between 2
already existing lines of code.
Between those 2 lines write:

DoCmd.RunCommand acCmdSaveRecord
DoCmd.OpenReport "ReportName", acViewPreview, , "[RecordID] = " &
[RecordID]

The above assumes a [RecordID] field that is a Number Datatype.

If, however, [RecordID] is Text Datatype, then use:

DoCmd.OpenReport "ReportName", acViewPreview, ,"[RecordID] = '" &
[RecordID] & "'"

as the Where clause.

For clarity, the single and double quotes are..
"[RecordID] = ' " & [RecordID] & " ' "
Change [RecordID] to whatever the actual field name is that you are
using.

See VBA Help files for:
Where Clause + Restrict data to a subset of records'
Click to expand...
 
F

fredg

I have attempted to follow your instructions, but Access (2003) tends to
prevent me from entering the text precisely as you have suggested. This is
what I have:
Private Sub PreviewPackingSlipButton_Click()

On Error GoTo Err_PreviewPackingSlipButton_Click

DoCmd.RunCommand acCmdSaveRecord

DoCmd.OpenReport "Packing Slip", acViewPreview, ,
"DonatedStoreItemID"

Exit_PreviewPackingSlipButton_Click:

Exit Sub

Err_PreviewPackingSlipButton_Click:

MsgBox Err.Description

Resume Exit_PreviewPackingSlipButton_Click

End Sub

Though my command button asks for the record ID, it still opens all Reports
instead of the one associated with the open Record. Perhaps you can spot my
error and recommend a correction.

Thanks . . . Fred

fredg said:
Greetings:

I have a Report that is linked to a Query. The Report (for example, a
packing slip or form letter) will contain a range of information relevant to
a single account. I want to place a command button in a Form that will open
the Report and Print only the current (open) record. How can I do this?

Thanks . . . Fred
Click to expand...

Your table should have a unique prime key field.
In my example it is named [RecordID].

On the command button's property sheet write
[Event Procedure]
on the Click event line.
Then click on the little button with 3 dots that will appear on that
line.
When the code window opens, the cursor will be flashing between 2
already existing lines of code.
Between those 2 lines write:

DoCmd.RunCommand acCmdSaveRecord
DoCmd.OpenReport "ReportName", acViewPreview, , "[RecordID] = " &
[RecordID]

The above assumes a [RecordID] field that is a Number Datatype.

If, however, [RecordID] is Text Datatype, then use:

DoCmd.OpenReport "ReportName", acViewPreview, ,"[RecordID] = '" &
[RecordID] & "'"

as the Where clause.

For clarity, the single and double quotes are..
"[RecordID] = ' " & [RecordID] & " ' "
Change [RecordID] to whatever the actual field name is that you are
using.

See VBA Help files for:
Where Clause + Restrict data to a subset of records'
Click to expand...
Click to expand...


This is what I suggested:
DoCmd.OpenReport "ReportName", acViewPreview, , "[RecordID] = " &
[RecordID]
This is what you wrote:
DoCmd.OpenReport "Packing Slip", acViewPreview, ,
"DonatedStoreItemID"

What does "DonatedStoreItemID" (all by itself) mean?

You need to tell Access that the value of a certain field in the table
equals the value of certain control on the form. Take a look at my
suggested Where argument again.

"[RecordID = " & [RecordID]

I have no idea what your actual field names are, nor what the field
datatype is, nor what the actual name of the unique control on the
form is. So here is a guess:
The name of the unique field in your table is [DonatedStoreItemID] and
it is a Number datatype; also you have a control on your form named
[DonatedStoreItemID]

Therefore....

DoCmd.OpenReport "Packing Slip", acViewPreview, ,
"[DonatedStoreItemID] = " & [DonatedStoreItemID]

Should open a report showing whatever record is displayed on your
form.
 
F

Fred Worthington

Thanks. That did the trick . . . Fred


fredg said:
I have attempted to follow your instructions, but Access (2003) tends to
prevent me from entering the text precisely as you have suggested. This is
what I have:
Private Sub PreviewPackingSlipButton_Click()

On Error GoTo Err_PreviewPackingSlipButton_Click

DoCmd.RunCommand acCmdSaveRecord

DoCmd.OpenReport "Packing Slip", acViewPreview, ,
"DonatedStoreItemID"

Exit_PreviewPackingSlipButton_Click:

Exit Sub

Err_PreviewPackingSlipButton_Click:

MsgBox Err.Description

Resume Exit_PreviewPackingSlipButton_Click

End Sub

Though my command button asks for the record ID, it still opens all Reports
instead of the one associated with the open Record. Perhaps you can spot my
error and recommend a correction.

Thanks . . . Fred

fredg said:
On Sat, 23 Jun 2007 08:09:49 -0500, Fred Worthington wrote:

Greetings:

I have a Report that is linked to a Query. The Report (for example, a
packing slip or form letter) will contain a range of information relevant to
a single account. I want to place a command button in a Form that
Click to expand...
will
open
the Report and Print only the current (open) record. How can I do this?

Thanks . . . Fred

Your table should have a unique prime key field.
In my example it is named [RecordID].

On the command button's property sheet write
[Event Procedure]
on the Click event line.
Then click on the little button with 3 dots that will appear on that
line.
When the code window opens, the cursor will be flashing between 2
already existing lines of code.
Between those 2 lines write:

DoCmd.RunCommand acCmdSaveRecord
DoCmd.OpenReport "ReportName", acViewPreview, , "[RecordID] = " &
[RecordID]

The above assumes a [RecordID] field that is a Number Datatype.

If, however, [RecordID] is Text Datatype, then use:

DoCmd.OpenReport "ReportName", acViewPreview, ,"[RecordID] = '" &
[RecordID] & "'"

as the Where clause.

For clarity, the single and double quotes are..
"[RecordID] = ' " & [RecordID] & " ' "
Change [RecordID] to whatever the actual field name is that you are
using.

See VBA Help files for:
Where Clause + Restrict data to a subset of records'
Click to expand...
Click to expand...


This is what I suggested:
DoCmd.OpenReport "ReportName", acViewPreview, , "[RecordID] = " &
[RecordID]
This is what you wrote:
DoCmd.OpenReport "Packing Slip", acViewPreview, ,
"DonatedStoreItemID"

What does "DonatedStoreItemID" (all by itself) mean?

You need to tell Access that the value of a certain field in the table
equals the value of certain control on the form. Take a look at my
suggested Where argument again.

"[RecordID = " & [RecordID]

I have no idea what your actual field names are, nor what the field
datatype is, nor what the actual name of the unique control on the
form is. So here is a guess:
The name of the unique field in your table is [DonatedStoreItemID] and
it is a Number datatype; also you have a control on your form named
[DonatedStoreItemID]

Therefore....

DoCmd.OpenReport "Packing Slip", acViewPreview, ,
"[DonatedStoreItemID] = " & [DonatedStoreItemID]

Should open a report showing whatever record is displayed on your
form.
Click to expand...
 
B

Bipbop

This is what I suggested:
DoCmd.OpenReport "ReportName", acViewPreview, , "[RecordID] = " &
[RecordID]
This is what you wrote:
DoCmd.OpenReport "Packing Slip", acViewPreview, ,
"DonatedStoreItemID"

What does "DonatedStoreItemID" (all by itself) mean?

You need to tell Access that the value of a certain field in the table
equals the value of certain control on the form. Take a look at my
suggested Where argument again.

"[RecordID = " & [RecordID]

I have no idea what your actual field names are, nor what the field
datatype is, nor what the actual name of the unique control on the
form is. So here is a guess:
The name of the unique field in your table is [DonatedStoreItemID] and
it is a Number datatype; also you have a control on your form named
[DonatedStoreItemID]

Therefore....

DoCmd.OpenReport "Packing Slip", acViewPreview, ,
"[DonatedStoreItemID] = " & [DonatedStoreItemID]

Should open a report showing whatever record is displayed on your
form.
Click to expand...

This did not work for me. I still get all of the records printing. Could
you please have a quick look at my code and tell me what I am doing wrong?

Thanks

Private Sub Print_Trainee_Certificate_Click()
On Error GoTo Err_Print_Trainee_Certificate_Click

Dim stDocName As String

stDocName = "Cert Fronts Trial"
DoCmd.OpenReport stDocName, acViewPreview, , [Trainee ID] = "& [Trainee
ID]"

Exit_Print_Trainee_Certificate_Click:
Exit Sub

Err_Print_Trainee_Certificate_Click:
MsgBox Err.Description
Resume Exit_Print_Trainee_Certificate_Click

End Sub
 
F

fredg

This is what I suggested:
DoCmd.OpenReport "ReportName", acViewPreview, , "[RecordID] = " &
[RecordID]
This is what you wrote:
DoCmd.OpenReport "Packing Slip", acViewPreview, ,
"DonatedStoreItemID"

What does "DonatedStoreItemID" (all by itself) mean?

You need to tell Access that the value of a certain field in the table
equals the value of certain control on the form. Take a look at my
suggested Where argument again.

"[RecordID = " & [RecordID]

I have no idea what your actual field names are, nor what the field
datatype is, nor what the actual name of the unique control on the
form is. So here is a guess:
The name of the unique field in your table is [DonatedStoreItemID] and
it is a Number datatype; also you have a control on your form named
[DonatedStoreItemID]

Therefore....

DoCmd.OpenReport "Packing Slip", acViewPreview, ,
"[DonatedStoreItemID] = " & [DonatedStoreItemID]

Should open a report showing whatever record is displayed on your
form.
Click to expand...

This did not work for me. I still get all of the records printing. Could
you please have a quick look at my code and tell me what I am doing wrong?

Thanks

Private Sub Print_Trainee_Certificate_Click()
On Error GoTo Err_Print_Trainee_Certificate_Click

Dim stDocName As String

stDocName = "Cert Fronts Trial"
DoCmd.OpenReport stDocName, acViewPreview, , [Trainee ID] = "& [Trainee
ID]"

Exit_Print_Trainee_Certificate_Click:
Exit Sub

Err_Print_Trainee_Certificate_Click:
MsgBox Err.Description
Resume Exit_Print_Trainee_Certificate_Click

End Sub
Click to expand...

You placed the quotes in the where clause in the wrong position.
It should be:

"[Trainee ID] = "& [Trainee ID]

The above assumes [Trainer ID] is a Number datatype.
However, if [Number ID] is Text datatype, then use:

"[Trainee ID] = '"& [Trainee ID] & "'"

Just for clarity, the quotes above are
"[Trainee ID] = ' "& [Trainee ID] & " ' "
 
B

Bipbop

fredg said:
Greetings:

I have a Report that is linked to a Query. The Report (for example, a
packing slip or form letter) will contain a range of information relevant to
a single account. I want to place a command button in a Form that will open
the Report and Print only the current (open) record. How can I do this?

Thanks . . . Fred
Click to expand...

Your table should have a unique prime key field.
In my example it is named [RecordID].

On the command button's property sheet write
[Event Procedure]
on the Click event line.
Then click on the little button with 3 dots that will appear on that
line.
When the code window opens, the cursor will be flashing between 2
already existing lines of code.
Between those 2 lines write:

DoCmd.RunCommand acCmdSaveRecord
DoCmd.OpenReport "ReportName", acViewPreview, , "[RecordID] = " &
[RecordID]

The above assumes a [RecordID] field that is a Number Datatype.

If, however, [RecordID] is Text Datatype, then use:

DoCmd.OpenReport "ReportName", acViewPreview, ,"[RecordID] = '" &
[RecordID] & "'"

as the Where clause.

For clarity, the single and double quotes are..
"[RecordID] = ' " & [RecordID] & " ' "
Change [RecordID] to whatever the actual field name is that you are
using.

See VBA Help files for:
Where Clause + Restrict data to a subset of records'
Click to expand...

Thank for this. It does look better now (don't know much about VBA).
My ID is a number datatype and I have used

DoCmd.RunCommand acCmdSaveRecord
DoCmd.OpenReport "Cert Fronts Trial", acViewPreview, , "[[Trainee ID]] = " &
[[Trainee ID]]

When I run it now, I get a runtime error 3075
"Syntax error in query expression '([[Trainee ID]] = 1234)'.

Any ideas what's wrong now? 1234 is the number I entered in the form so I
would expect it to continue to print preview.

Thanks
 
F

fredg

fredg said:
Greetings:

I have a Report that is linked to a Query. The Report (for example, a
packing slip or form letter) will contain a range of information relevant to
a single account. I want to place a command button in a Form that will open
the Report and Print only the current (open) record. How can I do this?

Thanks . . . Fred
Click to expand...

Your table should have a unique prime key field.
In my example it is named [RecordID].

On the command button's property sheet write
[Event Procedure]
on the Click event line.
Then click on the little button with 3 dots that will appear on that
line.
When the code window opens, the cursor will be flashing between 2
already existing lines of code.
Between those 2 lines write:

DoCmd.RunCommand acCmdSaveRecord
DoCmd.OpenReport "ReportName", acViewPreview, , "[RecordID] = " &
[RecordID]

The above assumes a [RecordID] field that is a Number Datatype.

If, however, [RecordID] is Text Datatype, then use:

DoCmd.OpenReport "ReportName", acViewPreview, ,"[RecordID] = '" &
[RecordID] & "'"

as the Where clause.

For clarity, the single and double quotes are..
"[RecordID] = ' " & [RecordID] & " ' "
Change [RecordID] to whatever the actual field name is that you are
using.

See VBA Help files for:
Where Clause + Restrict data to a subset of records'
Click to expand...

Thank for this. It does look better now (don't know much about VBA).
My ID is a number datatype and I have used

DoCmd.RunCommand acCmdSaveRecord
DoCmd.OpenReport "Cert Fronts Trial", acViewPreview, , "[[Trainee ID]] = " &
[[Trainee ID]]

When I run it now, I get a runtime error 3075
"Syntax error in query expression '([[Trainee ID]] = 1234)'.

Any ideas what's wrong now? 1234 is the number I entered in the form so I
would expect it to continue to print preview.

Thanks
Click to expand...

Why the double set of brackets around the field name?
"[[Trainee ID]] = " & [[Trainee ID]]
should be:
"[Trainee ID] = " & [Trainee ID]
 
B

Bipbop

Why the double set of brackets around the field name?
"[[Trainee ID]] = " & [[Trainee ID]]
should be:
"[Trainee ID] = " & [Trainee ID]
Click to expand...
Removing them still doesn't make it work. I now get #Error in all of the
fields on the report.

I think I shall just give up. This has been going on for too long now. It
seems nobody can help.

Thanks anyway
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Adding data to MS Access from csv file 0
Page numbering with Merged Document and IF statements 1
Improving Macro That Keeps First Letter 0
Trying to print current record 3
Copy previous record into form field 0
Print a report without displaying print preview 1
Printing a report for a single ID 1
Access 2010 DB not fuctional with multiple users 2

Top