Screen Print instead of Report

[DanGriffin]

I have an application that collects employee application info. When a new record is saved, it prints an envelope with the applicant's name and address and a code based on certain criterion.

The problem is, when the report preview (of the envelope) comes up and the user presses PRINT icon, a SCREEN PRINT of the underlying form is printed instead of the envelope (report) that is currently displayed on the screen!?!?!

What's going on?

This is the code that calls the report:

If MsgBox("Do you wish to print an envelope for this person?", vbQuestion + vbYesNo, "Notification Envelope&quot = vbYes Then
Me!TestLetter = Date
Me.Refresh
DoCmd.OpenReport "Envelope", acPreview, , , , sArgs
End If

...and this is the code behind the reports:

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
Dim dbs As Database, rst As Recordset

Set dbs = CurrentDb()
Set rst = dbs.OpenRecordset("SELECT * FROM [" & sYearFile & "] WHERE [" & sYearFile & "].SSnum = '" & Forms![Applicant Form]!SSnum & "';&quot
Me!Name = rst!Name
Me!Address = rst!Address
Me!City = rst!City & ", " & rst!State & " " & rst!Zip
Me!PosCode = Me.OpenArgs
rst.Close
Set dbs = Nothing

End Sub

...one interesting note: If I remove the reference to the underlying form from the end of the SELECT query, the envelope prints fine(!!) [For every record of course]

Most strange. Anyone have any hint as to why? And/or what to do about it?

Thanks in advance for your time and trouble.

Dan

 

[vbajock]

That's an interesting bug. I think I would create a variable,

Dim strSSNUM as string

strSSNUM=Forms![Applicant Form]!SSnum

then change the sql to:

("SELECT * FROM " & sYearFile & " WHERE " & sYearFile & ".SSnum = '" & strSSNUM& "';&quot

 

[DanGriffin]

I tried that. As long as the reference to the underlying form in present, it prints!!! Crazy.

strTemp = "SELECT * FROM [" & sYearFile & "] WHERE [" &
sYearFile & "].SSnum = '" & Forms![Applicant Form]!
SSnum & "';"

Set rst = dbs.OpenRecordset(strTemp)

Only if I remove the reference ENTIRELY, will it print properly. Such as:

Set rst = dbs.OpenRecordset("SELECT * FROM [" &
sYearFile & "];&quot

But then of course it prints an envelope for every record in the table.

 

[DanGriffin]

Another interesting note! If, while the envelope form is displayed on the screen, one pulls down the FILE menu from the tool bar and selectes PRINT PREVIEW. The report window is closed and the underlying form is displayed! (the one that prints)

Any help would be greatly appriciated. The hiring season started this Monday (today).

Thanks,
Dan

 

[vbajock]

Here's another possible work around:

In a code module:

Public strSSNO as string

Public Function SetSSNo(byval str as string)
strSSNo=str
End Function

Public Function GetSSno() as string
GetSSno=strSSNo
End Function

In the form that collects the ssno, use:
Call SetSSNo(me!ssno) to pass it to the public variable
then:

Set rst = dbs.OpenRecordset("SELECT * FROM " & sYearFile & " WHERE " & sYearFile & ".SSnum = '" &GetSSno&+ "';&quot

 

[RoyVidar]

Hi!

Yes I've also sometimes seen a report lose focus when performing different tasks. Perhaps you can use the selectobject method of the docmd object? I've at least used that from a form, with some degree of success.

Else, perhaps you might consider using the where condition when opening the report, or set the filters in the query?

HTH Roy-Vidar

 

[dgriffin]

I thought for sure one of the two above approaches would work, but alass, not to be.

I changed the report call from the form to:

DoCmd.OpenReport "Envelope", acPreview, ,
"SSnum = '" & Me!SSnum & "'",
, sArgs

along with eliminating the Private Sub Detail_Format event altogether. Still prints the form screen instead of the report.

I also tried using DOCMD.SELECTOBJECT acReport, "Envelope" only to recieve the error message THE SELECTOBJECT METHOD CANNOT BE USED ON A REPORT THAT IS CURRENTLY PRINTING. Except, it isn't the report that is printing!?!?!?

GODDDDDDD HOW I LOOOOOOVE Microsoft!!!!!!!!

Makes me want to go back to Clipper!

 

[RoyVidar]

Hm - version thingie? Just put the selectobject thingie in the detail sections on format event of the report, and it worked (xp).

Think I saw somewhere that it might be some challenges when using popup or modal properties = yes of the form - could be interesting to either toggle those, if their'e in use, or try changing the forms visible property while running the report?

Or if appliccable, run it with acNormal, to print without preview?

- as stumped as you, I'm afraid, Roy-Vidar;-)

 

[dgriffin]

Well, I found a workaround for the time being. The Envelope report prints with each applicant entry upon being saved. So, at least, data entry can resume.

If I eliminate the PREVIEW the report prints properly. But this isn't the end of it. The user needs to SEE the codes for the envelope and make a decision as to whether print it at this time or not. This is a customer design issue. It's a matter of their business procedures and is not negotiable.

I still need to find the reason for this anomaly and hence the solution.

Any more ideas?

Thanks,
Dan