By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,493 Members | 1,179 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,493 IT Pros & Developers. It's quick & easy.

Where Condition Query

P: 10
Hi,

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

Expand|Select|Wrap|Line Numbers
  1.  Private Sub Command219_Click()
  2.  
  3. DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70
  4. If IsNull(Me!ContactID) Then
  5. MsgBox "Please select a valid record", _
  6. vbOKOnly, "Error"
  7. Exit Sub
  8. End If
  9. DoCmd.OpenReport "mailmerge", acViewPreview, , _
  10. "ContactID = " & Me!ContactID 
  11. 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
  1.  WHERE (((Letters.PrintF)=-1) AND ((Letters.RecpID)=1)); 
Can anyone help me convert this into the VB?

Thanks
Dec 31 '07 #1
Share this Question
Share on Google+
7 Replies


jaxjagfan
Expert 100+
P: 254
Hi,

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

Expand|Select|Wrap|Line Numbers
  1.  Private Sub Command219_Click()
  2.  
  3. DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70
  4. If IsNull(Me!ContactID) Then
  5. MsgBox "Please select a valid record", _
  6. vbOKOnly, "Error"
  7. Exit Sub
  8. End If
  9. DoCmd.OpenReport "mailmerge", acViewPreview, , _
  10. "ContactID = " & Me!ContactID 
  11. 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
  1.  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.
Dec 31 '07 #2

P: 10
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!
Dec 31 '07 #3

jaxjagfan
Expert 100+
P: 254
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?
Dec 31 '07 #4

P: 10
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
  1. "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.
Dec 31 '07 #5

jaxjagfan
Expert 100+
P: 254
My mainform is Contacts and the subform is Letters

I gave the above example and
Expand|Select|Wrap|Line Numbers
  1. "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.
Dec 31 '07 #6

P: 10
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.
Dec 31 '07 #7

P: 10
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!
Jan 2 '08 #8

Post your reply

Sign in to post your reply or Sign up for a free account.