Where Condition Query

[lostdawg]

Hi,

I have a question regarding placing the Where condition in this section of code

 Private Sub Command219_Click()
 
DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70
If IsNull(Me!ContactID) Then
MsgBox "Please select a valid record", _
vbOKOnly, "Error"
Exit Sub
End If
DoCmd.OpenReport "mailmerge", acViewPreview, , _
"ContactID = " & Me!ContactID 
end sub 

I need to insert 2 additional where conditions for the report, one being a checkbox and another testing for a value of 1.

The SQL would be

 WHERE (((Letters.PrintF)=-1) AND ((Letters.RecpID)=1)); 

Can anyone help me convert this into the VB?

Thanks

[jaxjagfan]

Hi,

I have a question regarding placing the Where condition in this section of code

 Private Sub Command219_Click()
 
DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70
If IsNull(Me!ContactID) Then
MsgBox "Please select a valid record", _
vbOKOnly, "Error"
Exit Sub
End If
DoCmd.OpenReport "mailmerge", acViewPreview, , _
"ContactID = " & Me!ContactID 
end sub 

I need to insert 2 additional where conditions for the report, one being a checkbox and another testing for a value of 1.

The SQL would be

 WHERE (((Letters.PrintF)=-1) AND ((Letters.RecpID)=1)); 

Can anyone help me convert this into the VB?

Thanks

I put the ContactID last since it seems to be a value coming from the form and the other values are hard coded.

"PrintF=-1 AND RecpID=1 AND ContactID = " & Me!ContactID

As long as it is a viable WHERE clause you don't need all of the brackets the QBE uses.

[lostdawg]

Hi Jaxjagfan

Thanks for the quick reply!

I gave it a go and it produced an error. I should have explained that ContactID is the main form and the PrintF and RecpID values are on a linked form.

The original code selects the current record displayed but I get an error when I try to further filter these results by using values form the letters form.

Apologies for not being clearer!

[jaxjagfan]

Hi Jaxjagfan

Thanks for the quick reply!

I gave it a go and it produced an error. I should have explained that ContactID is the main form and the PrintF and RecpID values are on a linked form.

The original code selects the current record displayed but I get an error when I try to further filter these results by using values form the letters form.

Apologies for not being clearer!

Try this (assuming the name of the subform control is "Letters"):

"Me!Letters.PrintF=-1 AND Me!Letters.RecpID=1 AND ContactID = " & Me!ContactID

or you may have to use this format to reference the subform values:

Forms.YourMainFormName.SubformContainerName.Form.C ontrolName

What is the name of the main form?

[lostdawg]

Try this (assuming the name of the subform control is "Letters"):

"Me!Letters.PrintF=-1 AND Me!Letters.RecpID=1 AND ContactID = " & Me!ContactID

or you may have to use this format to reference the subform values:

Forms.YourMainFormName.SubformContainerName.Form.C ontrolName

What is the name of the main form?

My mainform is Contacts and the subform is Letters

I gave the above example and

"Forms.Contacts.Letters.Form.PrintF=-1 AND Forms.Contacts.Letters.Form.RecpID=1 AND ContactID = " & Me!ContactID 

a go but on running it i was asked to enter the parameter value. I can't think of what else I am missing.

[jaxjagfan]

My mainform is Contacts and the subform is Letters

I gave the above example and

"Forms.Contacts.Letters.Form.PrintF=-1 AND Forms.Contacts.Letters.Form.RecpID=1 AND ContactID = " & Me!ContactID 

a go but on running it i was asked to enter the parameter value. I can't think of what else I am missing.

Look at the main form in design view. Select the control that contains the subform not the subform itself. Look at the properties of this control and see what its name is. Is it "Letters"?

FYI - You should get into the habit of satndardizing your naming. I'm not always consistent myself (but should be). It would be frmContacts, tblContacts, qryContacts, etc.

[lostdawg]

Look at the main form in design view. Select the control that contains the subform not the subform itself. Look at the properties of this control and see what its name is. Is it "Letters"?

FYI - You should get into the habit of satndardizing your naming. I'm not always consistent myself (but should be). It would be frmContacts, tblContacts, qryContacts, etc.

Actually now you mention, the mainform is called Contacts but the subform is called Letter Detail Form that is again placed in another form Letters which is then placed on the main form.

So it goes Contacts > Letters > Letter Detail Form.

Letter Detail Form being the one with the information needed.

[lostdawg]

Got sorted thanks, took me a while but finally got there.

I am going to have to tidy up the names as you said, would be alot easier to follow for me and others.

Thanks for the help!