Print Report from Form based on multiple criteria?

[Alan R]

Hi,

I have the following code which i found on another thread on this forum (thanks to original author) which is attached to the On Click of a button which prints the report corresponding to the details displayed in the form.

Dim strCriterion As String
Dim strMsg As String, strTitle As String
Dim intStyle As Integer

If IsNull(Me![ReferenceNumber]) Then

strMsg = "You cannot print a Blank Form!!."
strTitle = "Print Error"
intStyle = vbOKOnly
MsgBox strMsg, intStyle, strTitle

Exit Sub
End If

If Me.Dirty Then
Me.Dirty = False
End If

strCriterion = "[ReferenceNumber]=" & Me![ReferenceNumber]
DoCmd.OpenReport "DoC Certificate", acViewNormal, , strCriterion

This works fine, however, i need to be able to select the report based on more than 1 criteria. For example, the Reference Number can be repeated but is distinguishable from each other by an Issue Number i.e. ReferenceNumber = 93, Issue 1 or 2 etc. At present when i run the above it prints all versions of, in this case, reference number 93, which given that each report is only a single page isn't a show-stopper but it would be nice to have it working as i would like.

I have tried adding to the strCriterion line such as strCriterion = "[ReferenceNumber]=" & Me![ReferenceNumber] and "[IssueNumber]=" & Me![IssueNumber] but no joy. I have tried bracketing the whole line and variations thereof, again no joy.

Can anybody tell if what i am attempting to do is possible and if so how do i go about it?

I have tried the above coding using MasterID which is the Autonumber PK but it produces an "Enter Parameter Value" box for MasterID. Obvioulsy if i can get it to work for the Autonumber then my problem goes away but i can't seem to figure out why it works for Reference Number (Number) and not MasterId (Autonumber)?

Regards

Alan

 

[edtab]

try:

"[ReferenceNumber]=" & Me![ReferenceNumber] & " and " & "[IssueNumber]=" & Me![IssueNumber]

 

[Alan R]

What a star

Edtab,

That works - what a star

Many Thanks

Alan

 

[gemma-the-husky]

just out of interest you have a bit of code saying

If Me.Dirty Then
Me.Dirty = False
End If


me.dirty is set by Access to indicate that a record as been edited, and is therefore dirty. I didn't think you could actually write to this property, and I'm not sure what it would do with a dirty record.

If you want to save the changes you should do
application.runcommand acCmdSaveRecord

if you want to scrap any changes i think its
application.runcommand acCmdUndo

i think there are docmd objects to do these also

obviously the idea is that you handle the changes before printing the information.

 

[Alan R]

Gemma,

I will have to bow to your greater knowledge of VB and Access on this one. What i can add is that there were comments included with the code i found on this forum which were

'If the record is not blank then it saves it, also when you have just created a new record and try and print it, it will not have been saved so this will do it'.

Whether that is correct or not i can't say, all i know is that it does what i want it to, having said that i have copied your notes into my 'notebook' for future reference - many thanks.

The following has been copied from the Visual Basic Help File

"You can use the Dirty property to determine whether the current record has been modified since it was last saved. For example, you may want to ask the user whether changes to a record were intended and, if not, allow the user to move to the next record without saving the changes. "

Regards

Alan

 

[gemma-the-husky]

Alan -

the original note about the code is obviously correct.

I've just tried it, and setting dirty to false does indeed appear to save the changes directly. I thought it was a read only property, but it clearly does something else, as your orginal code suggested.


Further thoughts - i've had another play with it now. It does try to write the record, because if the data is invalid ie required field not complete, or key violation it throws an error, and won't save the change.