Where Condition Query

lostdawg

Hi,

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

Expand|Select|Wrap|Line Numbers

  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

Expand|Select|Wrap|Line Numbers

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

Can anyone help me convert this into the VB?

Thanks

jaxjagfan

Quote:

Originally Posted by lostdawg

Hi,

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

Expand|Select|Wrap|Line Numbers

  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

Expand|Select|Wrap|Line Numbers

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

Quote:

Originally Posted by 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!

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

Quote:

Originally Posted by jaxjagfan

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

Expand|Select|Wrap|Line Numbers

 
"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

Quote:

Originally Posted by lostdawg

My mainform is Contacts and the subform is Letters

I gave the above example and

Expand|Select|Wrap|Line Numbers

 
"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

Quote:

Originally Posted by jaxjagfan

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!

Where Condition Query

re: Where Condition Query

re: Where Condition Query

re: Where Condition Query

re: Where Condition Query

re: Where Condition Query

re: Where Condition Query

re: Where Condition Query

Similar Microsoft Access / VBA bytes

/bytes/development

/bytes/it

/bytes/about