Print Current Record

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Dan - 23 Sep 2007 03:52 GMT

I want to print the current record in the form, I am using the following code:
Private Sub cmdPrint_Click()
Dim strWhere As String

If Me.Dirty Then 'Save any edits.
Me.Dirty = False
End If

If Me.NewRecord Then 'Check there is a record to print
MsgBox "Select a record to print"
Else
strWhere = "[EmployeeID] = " & Me.[EmployeeID]
DoCmd.OpenReport "MyReport", acViewPreview, , strWhere
End If
End Sub

The report opens , but it is showing me all the records not just one. The
form I am using has 3 subforms on seperate Tabs, Is this causing my problem?

thanks

Signature

Dan

Reply to this Message

UpRider - 23 Sep 2007 04:20 GMT

Dan, first, after setting me.dirty to false, it is impossible for
me.newrecord to be true, so you don't need to check newrecord.
Second, the code looks good for the criteria.
However, try putting

debug.print strWhere

just before the docmd.openreport.
That will tell what you are actually passing as the report criteria.
The debug.print will appear in the immediate code window after you click the
cmdPrint.

UpRider

>I want to print the current record in the form, I am using the following
>code:
[quoted text clipped - 18 lines]
>
> thanks

Reply to this Message

Dan - 23 Sep 2007 05:36 GMT

Thanks for the quick reply, but the report still prints all records, not just
the current

Signature

Dan

> Dan, first, after setting me.dirty to false, it is impossible for
> me.newrecord to be true, so you don't need to check newrecord.
[quoted text clipped - 32 lines]
> >
> > thanks

Reply to this Message

Dan - 23 Sep 2007 05:42 GMT

Interestly, if I change the report name to one of my other reports, it prints
just the current record, so could it be a flaw in my report?

Signature

Dan

> Dan, first, after setting me.dirty to false, it is impossible for
> me.newrecord to be true, so you don't need to check newrecord.
[quoted text clipped - 32 lines]
> >
> > thanks

Reply to this Message

Dan - 23 Sep 2007 06:04 GMT

my report has 1 field linked to a different table, the field is called
'budgetCode" in the tbl_department. I removed that field and now it prints
perfect. So I guess, the next question is How do I add that field back in as
it is required for the report. Do I need to create a query and base the
report on that, or can I add something to the cmdprint code?

thanks

Signature

Dan

> Interestly, if I change the report name to one of my other reports, it prints
> just the current record, so could it be a flaw in my report?
[quoted text clipped - 35 lines]
> > >
> > > thanks

Reply to this Message

Rick Brandt - 23 Sep 2007 13:05 GMT

> my report has 1 field linked to a different table, the field is called
> 'budgetCode" in the tbl_department. I removed that field and now it
> prints perfect. So I guess, the next question is How do I add that
> field back in as it is required for the report. Do I need to create
> a query and base the report on that, or can I add something to the
> cmdprint code?

You'll have to explain further. A report based on a table cannot have "1 field
linked to a different table".

If you used the wizard to create the report and selected fields from more than
one table then your report is already based on a query (the wizard built it for
you). That is perfectly okay and I can think of no reason why that would make
your criteria not work.

Signature

Rick Brandt, Microsoft Access MVP
Email (as appropriate) to...
RBrandt at Hunter dot com

Reply to this Message

Dan - 23 Sep 2007 16:22 GMT

The report was built with a wizard with 1 field from a seperate table. After
the wizard was done I re arranges the layout. When I remove the 1 field from
the report everything works fine. I am thinking it has something to do with
the way the tables are joined in the query. The field on the report is not a
PK or a FK. The primary key from tbl_department is joined to tbl_employees.
The report is mainly based on tbl_employee.

Signature

Dan

> > my report has 1 field linked to a different table, the field is called
> > 'budgetCode" in the tbl_department. I removed that field and now it
[quoted text clipped - 10 lines]
> you). That is perfectly okay and I can think of no reason why that would make
> your criteria not work.

Reply to this Message

Rick Brandt - 23 Sep 2007 17:31 GMT

> The report was built with a wizard with 1 field from a seperate
> table. After the wizard was done I re arranges the layout.

> When I remove the 1 field from the report everything works fine. [snip]

Explain what you mean by this. Do you mean you are removing the extra table
from the query? Just removing the use of that field in your actual report
should make no difference at all.

Do the tables have a join between them in the query?

Signature

Rick Brandt, Microsoft Access MVP
Email (as appropriate) to...
RBrandt at Hunter dot com

Reply to this Message

Dan - 23 Sep 2007 18:42 GMT

The query for the report has a join between tbl_department and tbl_employee,
the join is between DepartmentID pk in tbl_department and DepartmentID fk in
tbl_employee. If I remove this join and the field budgetcode from the
report. the print current record works fine. If I have the field budgetcode
in the report, then all records print, not just the current.

Signature

Dan

> > The report was built with a wizard with 1 field from a seperate
> > table. After the wizard was done I re arranges the layout.
[quoted text clipped - 6 lines]
>
> Do the tables have a join between them in the query?

Reply to this Message

Dan - 24 Sep 2007 01:48 GMT

amazingly, I have rebuilt the report with all the required fields and it
works fine. So I have no idea why the old report wouldn't work

Signature

Dan

> I want to print the current record in the form, I am using the following code:
> Private Sub cmdPrint_Click()
[quoted text clipped - 16 lines]
>
> thanks

Reply to this Message