Call report form a Form (1 Viewer)

P

ppierce

Registered User.

Local time

Today, 02:03

Joined

Nov 18, 2009

Messages

21

• Apr 9, 2019

• #1

I have not done any work in Access for about 8 years and have been tasked with creating a simple form with a button to print the data on the form to a report. I have a print button on my form with this code behind.

Code:Copy to clipboard

Public Sub PrintReport()
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 = "[ID] = " & Me.[ID]
        DoCmd.OpenReport "rptDispatchCard", , , strWhere
    End If
End Sub

I want to report to print 1 record but for some reason it is printing all of the records in the database. When I put a break point on the code the strWhere parameter is showing the correct ID number for the report.

Can anyone see what I am not getting here? How can I print the 1 record?

 

Ranman256

Well-known member

Local time

Today, 02:03

Joined

Apr 9, 2015

Messages

4,339

• Apr 9, 2019

• #2

the code is correct for the where clause, if
ID is the name of the field AND the name of the text box
and
ID has a value.

Try print preview before you send to printer:

preview string field:
docmd.OpenReport "rpt",acViewPreview ,,"[field]='" & me.textbox & "'"

preview numeric field:
docmd.OpenReport "rpt",acViewPreview ,,"[ID]=" & me.textbox

 

P

ppierce

Registered User.

Local time

Today, 02:03

Joined

Nov 18, 2009

Messages

21

• Apr 9, 2019

• #3

Okay when I use the acViewPreview it opens the report in preview mode with 1 record. So why is that and how can I print the one record?

 

P

ppierce

Registered User.

Local time

Today, 02:03

Joined

Nov 18, 2009

Messages

21

• Apr 9, 2019

• #4

I was wrong when I run in the preview mode I am still getting all of the records in the database. I did not see the record navigation at the bottom, so I am back to the original problem.

 

Minty

AWF VIP

Local time

Today, 07:03

Joined

Jul 26, 2013

Messages

10,366

• Apr 9, 2019

• #5

Try this as an experiment , change the name of the textbox holding your ID to txtID

Code:Copy to clipboard

   strWhere = "[ID] = " & Me.txtID
   Debug.Print strWhere
   DoCmd.OpenReport "rptDispatchCard", acViewPreview , , strWhere

Now check in the immediate window of the VB editor what your where string really looks like.

Moving forwards, ID is a terrible name for a PK field. ID of what?
DispatchID , DogBoneID, BoatyMcBoatFaceID would all be more descriptive and let you know which ID you where referencing if you had some joined tables in a query or for.

 

P

ppierce

Registered User.

Local time

Today, 02:03

Joined

Nov 18, 2009

Messages

21

• Apr 9, 2019

• #6

Okay made those changes and my ID is the correct primary key value. I don't know why it works as expected in preview mode but when I change to send to the printer it prints all of the records?

 

Gasman

Enthusiastic Amateur

Local time

Today, 07:03

Joined

Sep 21, 2011

Messages

14,221

• Apr 9, 2019

• #7

ppierce said:

Okay made those changes and my ID is the correct primary key value. I don't know why it works as expected in preview mode but when I change to send to the printer it prints all of the records?

Click to expand...

You stated that it did not work in preview either?

 

P

ppierce

Registered User.

Local time

Today, 02:03

Joined

Nov 18, 2009

Messages

21

• Apr 9, 2019

• #8

No it does work in preview mode

 

M

Mark_

Longboard on the internet

Local time

Yesterday, 23:03

Joined

Sep 12, 2017

Messages

2,111

• Apr 9, 2019

• #9

Does your report do anything that changes either your record source or apply any filter?

Also make sure that [ID] is the correct field and that it is included in your report's record source.

 

P

ppierce

Registered User.

Local time

Today, 02:03

Joined

Nov 18, 2009

Messages

21

• Apr 9, 2019

• #10

There is no code behind on the report. And ID is the correct primary key and I have it included on my record source. As I said it has been awhile since I worked with Access. I pulled my last Access project out and looked at that code and it uses the same code except it calls acNormal instead of acPreview and that app runs just fine. This really has me stumped.

 

Gasman

Enthusiastic Amateur

Local time

Today, 07:03

Joined

Sep 21, 2011

Messages

14,221

• Apr 9, 2019

• #11

ppierce said:

I was wrong when I run in the preview mode I am still getting all of the records in the database. I did not see the record navigation at the bottom, so I am back to the original problem.

Click to expand...

I think it might be time to upload your db in zip form.?
However I only have 2007, so I might not be able to open it.?

 

P

ppierce

Registered User.

Local time

Today, 02:03

Joined

Nov 18, 2009

Messages

21

• Apr 9, 2019

• #12

I figured out. I changed the reports record source from the table to a query in the query I set the criteria to this [Forms]![CallForm2]![ID] that only returns the one record and the report only shows 1 record.

Thanks for time.

 

M

Mark_

Longboard on the internet

Local time

Yesterday, 23:03

Joined

Sep 12, 2017

Messages

2,111

• Apr 9, 2019

• #13

Glad you got it sorted out!

 

Gasman

Enthusiastic Amateur

Local time

Today, 07:03

Joined

Sep 21, 2011

Messages

14,221

• Apr 9, 2019

• #14

Me being me, I'd still want to get to the bottom of it.

From what you posted, it should have worked, so something silly going on somewhere.

What if you wanted two or three separate IDs for the report?

ppierce said:

I figured out. I changed the reports record source from the table to a query in the query I set the criteria to this [Forms]![CallForm2]![ID] that only returns the one record and the report only shows 1 record.

Thanks for time.

Click to expand...