Print Single Report from Form (1 Viewer)

Harry Taylor · Mar 11, 2015

Please Help,

I'm trying to print a Report specific to the record i'm viewing on my Form.

I've created a Report linked to the Table - "SCJobsheet"

I've created a button in my Form;
Name; cmdSCJobsheet
Caption: Print Record
OnClick: [Event Procudure]

Private Sub cmdSCJobsheet_Click()
Dim strReportName As String
Dim strCriteria As String
strReportName = "SCJobsheet"
strCriteria = "[ACCOUNT]='" & Me![ACCOUNT] & "'"
DoCmd.OpenReport strReportName, acViewPreview, strCriteria

The Report is opening but shows Every record (Not just the one I'm viewing in the Form.

Any ideas where I'm going wrong?

Minty · Mar 11, 2015

Is [Account] numeric or text? You are formatting it as text.
And it might be worth changing the form control name to txtAccount or similar as having the same name as the field in the table can cause issues.

Edit - and is ACCOUNT unique? If not you will get a report page for every record with that account number.

Harry Taylor · Mar 11, 2015

Hi Minty, thank for the reply.

Yes it is formatted as Number but I get the same result if I make it ;

strCriteria = "[ACCOUNT]=' & Me![ACCOUNT]

The ACCOUNT is unique

I am very new to VB and took a lot of the code below from Google.
What part of the code below is the form control?

Thanks again for your help.

Minty · Mar 11, 2015

You are nearly there;

strCriteria = "[ACCOUNT]= " & Me.ACCOUNT

Assuming me.ACCOUNT where ACCOUNT is the the name of the control on your form.

Harry Taylor · Mar 12, 2015

Hi Minty,

Unfortunately I'm getting the same result (all records are printing).

I'm stuck

Harry Taylor · Mar 12, 2015

To Clarify;

Origional Table (BE) - Heatmaster100
Form- Heatmaster
Report - SCJobsheet
The primary (number) Key field is - ACCOUNT

I have put a button on the form and want it to print the SCJobsheet Report for the record i'm viewing on the form.

The button code is;
Private Sub cmdSCJobsheet_Click()
Dim strReportName As String
Dim strCriteria As String
strReportName = "SCJobsheet"
strCriteria = "[ACCOUNT]=" & Me.ACCOUNT
DoCmd.OpenReport strReportName, acViewPreview, strCriteria
End Sub

Minty · Mar 12, 2015

Can you add the following into your code;

Code:

strCriteria = "[ACCOUNT]=" & Me.ACCOUNT
[COLOR="Red"]Debug.Print strCriteria [/COLOR]
DoCmd.OpenReport strReportName, acViewPreview, strCriteria

Then save, leave the code window open and open the form and press the button. In the Immediate window for the code you will see what the result of me.ACCOUNT is.

Harry Taylor · Mar 12, 2015

[ACCOUNT]=1

(Same result no matter what record I'm viewing)

Minty · Mar 12, 2015

Can you post the record source SQL for your report and your form?

Harry Taylor · Mar 12, 2015

When I go into the record source SQL it's blank.
(Although I'm not sure im going about it correctly).

Minty · Mar 12, 2015

In design view (of both the Form and report) open the properties and select the data tab, at the top make sure Form is selected and then you should see either a table name or a SELECT ... Statement. That is what we are after

Harry Taylor · Mar 12, 2015

Form SQL -
SELECT
FROM Heatersmaster100;

Report SQL -
SELECT
FROM Heatersmaster100;

Minty · Mar 12, 2015

That doesn't look correct - at the very least it should say
SELECT * FROM HeatersMaster100;

Would it be possible for you to post up a stripped down version of your database?

I'm sure there is something pretty basic going wrong here but can't see it, and could be here all day trying to fathom it via the forum.

Print Single Report from Form (1 Viewer)

Harry Taylor

Registered User.

Minty

AWF VIP

Harry Taylor

Registered User.

Minty

AWF VIP

Harry Taylor

Registered User.

Harry Taylor

Registered User.

Minty

AWF VIP

Harry Taylor

Registered User.

Minty

AWF VIP

Harry Taylor

Registered User.

Minty

AWF VIP

Harry Taylor

Registered User.

Minty

AWF VIP

Similar threads

Users who are viewing this thread