Printing a Report from a Form with a Button

[CBenfer]

OK, this one is bugging the heck out of me. This worked fine until two days ago, and now it just stopped and I can't get it to work.

Basically, I'm trying to do a couple of functions with a button. I want to:
Populate a couple of fields based on data that the user entered
Save the record
Print a form that is based on the record
Close the Form

I have the code listed below. The report has a criteria in the Primary Key field that specifies [Forms]![Register_Form]![Primary_Key] so it should only print the record I want (I tried some other stuff but that's a whole different subject).

When I click on the button, it appears to update the fields fine and saves the record. A quick "printing" dialog appears, but nothing prints and it stops at that point (won't exit the form).

I tried just creating a print button, but that doesn't work right, either. Yet I can print the form directly (if I enter the Primary_Key manually), so it does print.

What am I missing here?

Private Sub RegisterFOButton_Click()
On Error GoTo Err_RegisterFOButton_Click
' Populate fields before saving
Me.Date_Started = Now
If Me.Profile_Number = 99 Then
Me.Status = "Active"
Else
Me.Status = "Pending"
End If
Me.Primary_Key = Me.FONum & "-" & Me.SequenceNum & "-" & Me.Date_Started
' End populate fields
' Auto generated code - saves record
DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70
' End auto generated code
' Print report
DoCmd.OpenReport "Process_Sheet_Report_Generic", acViewNormal
' End Print report
DoCmd.Close
Exit_RegisterFOButton_Click:
Exit Sub
Err_RegisterFOButton_Click:
MsgBox Err.Description
Resume Exit_RegisterFOButton_Click

End Sub

 

[John Big Booty]

A quick way to save a record is to simply set it's Dirty property to False, so you could use ;

Me.Dirty = False

in place of;

' Auto generated code - saves record
DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70

The DoCmd.Close method will simply close the object that currently holds focus, so given that you have opened a report the focus has moved away from your form, so you will need to explicitly name the form you wish to close;

DoCmd.Close acForm, "YourFormName", acSaveYes

I'm not sure how you are populating your report but try removing any criteria in the query and try using;

DoCmd.OpenReport "Process_Sheet_Report_Generic", acViewNormal, , "YourRecordID = " & Me.YourRecordID

 

[CBenfer]

Thanks for the info on the record saving - I haven't used that function before.

I didn't play with either the close or the save function yet - I didn't want to change too many things at once. But I did change the print command to:

DoCmd.OpenReport "Process_Sheet_Report_Generic", acViewNormal, , "Primary_Key = " & Me.Primary_Key

The problem is that it gives me new error on printing -
Syntax error (missing operator) in query expression '(Primary_Key = TEST-5352-9/27/12 7:56:03 AM)'.

Seems like we're making progress, but not quite there yet. I tried a few permutations of the command (different quotes, etc.) but couldn't come up with the correct combo. It looks like I need quotes around the primary key field, but I couldn't get them in there. Any thoughts?

 

[John Big Booty]

Given what you've posted try;

DoCmd.OpenReport "Process_Sheet_Report_Generic", acViewNormal, , "Primary_Key = [B][COLOR="Red"]'[/COLOR][/B]" & Me.Primary_Key [COLOR="Red"][B]& "'"[/B][/COLOR]

 

[CBenfer]

Now it opens the "printing box" but them immediately prompts me to enter a value for Primary_Key - like the field name is really a parameter. I tried referencing it as [Forms]![Register_Form]![Primary_Key] but it didn't like that either...

I think we're getting closer - I appreciate the help so far.

 

[John Big Booty]

Are you able to post a copy of your DB? ('03 version if possible)

 

[CBenfer]

I'm having some trouble posting it - probably has to do with my company firewall or something. I'll upload it tonight or tomorrow from home.

Thanks in advance for all of the help - this has been driving me crazy for the past week.

 

[John Big Booty]

You may need to do a Compact and Repair on the DB prior to zipping and uploading it.

 

[John Big Booty]

...also strip out all a handful of representative records.

 

[CBenfer]

Let's try this again...

The "Register_Form" is the one that's giving me the trouble, although it is basically a copy of "Register_New_Factory_Order" which is the one that I was originally working with. I created "Register_Form" from scratch to try to fix the problem.

It is printing "Process_Sheet_Report_Generic", which is basically a copy of "Process_Sheet_Report_From_Register", created for the same reason.

I should mention that the "Process_Sheet_Form_From_Combo_Box" form does mostly the same thing with the "Process_Sheet_Report_From_Drop_Down", and it seems to work OK, so that might be a good place to look for clues as well.

And last I should note that this is the first time I've tried to create a database like this, so you'll probably see all kinds of things that don't make sense or are horribly inefficient ways of doing things. If you can't figure out what I was trying to do with something, let me know and I'll help out as much as I can.

 

[BecoNorte]

Hello
Try this:
Under Private Sub RegisterFOButton_Click()
' Print report - remove the filter here
DoCmd.OpenReport "Process_Sheet_Report_Generic", acViewNormal
' End Print report

Add filter or criteria back here on your Process_Sheet_Query
under Primary Key field add criteria [Forms]![Register_Form]![Primary Key]

BN

 

[John Big Booty]

A couple of observations;
Rather than using your constructed Primary Key as you are currently doing use the AutoNumber function and let that do the job it's designed for. By all means continue to use your current schema for constructing P/O numbers or whatever. Remember though that the Primary Key is only that a unique identifier for each record, by assigning meaning to it beyond that function you start to make life difficult for your self.

Your constructed Primary Key is what has greatly complicated the current process of printing a report relevant to the current record.

I notice that you are using the Now() function to assign dates. Unless you specifically need a Time component to your date use the Date() function in it's place. The time component (embedded in the now function) greatly complicates your comparison of dates.

Consider implementing a naming convention along the lines of FRM_FormName, TBL_TableName, QRY_QueryName etc. This will make it quiet clear the type of object you are referring to in your code.

 

[John Big Booty]

... also on the subject of calculated/constructed fields. Have a read of this article.

 

[CBenfer]

OK - thanks for the advice. Since it was the formatting of the primary key field that was causing the trouble, I put in a workaround that seems to be working OK. And at least I understand why it isn't working.

I appreciate the help - as I said, I'm very new to this sort of database creation so I'm sure there is a ton that I can improve on.

And the article you linked to makes sense. I'll try to keep that in mind for future work.

Thanks, and take care.