Query Designer with Field Value

[dniezby]

Is it possible to create a query in the designer that looks for a value from a Form control?



For example, I have two tables:

tblContacts , tblCampaigns

I have a form frmCampaigns . This will be tracking my marketing campaigns. On the form is a is a field name that I will be turning into a button. When someone clicks that button I want it to open a query result.

It should list all of the contacts from my contact table that have the same campaign ID as the campaign form I'm on.

I hope I didn't just confuse everyone.

I did make this query in the query designer and it works but I already have the WHERE set to 1...When it's on the form it needs the WHERE set to Me!ID of the current record.

Can I do this from the designer or do I have to create it in VBA code.

Here is the sqlview of the query from the designer.

SELECT tblContacts.FirstName, tblContacts.LastName, tblCampaigns.CampaignCode
FROM tblCampaigns INNER JOIN tblContacts ON tblCampaigns.ID = tblContacts.Campaign_ID
WHERE (((tblCampaigns.ID)= 1));

[RuralGuy]

Try:

SELECT tblContacts.FirstName, tblContacts.LastName, tblCampaigns.CampaignCode
FROM tblCampaigns INNER JOIN tblContacts ON tblCampaigns.ID = tblContacts.Campaign_ID
WHERE (((tblCampaigns.ID) = Forms.frmCampaigns.ID));

[dniezby]

Try:

SELECT tblContacts.FirstName, tblContacts.LastName, tblCampaigns.CampaignCode
FROM tblCampaigns INNER JOIN tblContacts ON tblCampaigns.ID = tblContacts.Campaign_ID
WHERE (((tblCampaigns.ID) = Forms.frmCampaigns.ID));

Cool, I'll give this a shot. Thanks. I didn't know I could put that criteria there. Thank you very much.

[dniezby]

That didn't work. It just keeps asking me for the campaign ID. It's not taking it from the form.

[Micron]

I would have thought this syntax was required
Forms!frmCampaigns.ID));
Plus, ID is the name of the form control and not the name of the field the control is bound to? Or have you let it be named Text11 or some such default?

[Bob Fitz]

That didn't work. It just keeps asking me for the campaign ID. It's not taking it from the form.

What is the name of the control on the form which shows the ID.
If it is CampaignsID then the where clause would need to be:
WHERE (((tblCampaigns.ID) = Forms.frmCampaigns.CampaignsID));

[dniezby]

What is the name of the control on the form which shows the ID.
If it is CampaignsID then the where clause would need to be:
WHERE (((tblCampaigns.ID) = Forms.frmCampaigns.CampaignsID));

OMG - Found the problem. I was using frmCampaigns and it's actually singular for the form name. frmCampaign also I changed the CampaignsID to just = ID

Now it shows me the correct contact information for everyone that falls under that campaign data.

Thanks.

[dniezby]

Now that it works....Is there a way for me to present the information in a better looking manner? I know I can create a form which will look ideal and pleasant to my user but, will it keep the information it needs to present in a form.

For example, right now it looks at the Control "ID" on my form and says to present all the contacts where Contact_ID = ID... Will that follow over to a form or is it now time to move to the Programming forum?

[RuralGuy]

In order for that query to work as you want, the frmCampaign needs to be open. No need to switch forums. Right here will be just fine.

[dniezby]

Got it working using the information you provided. Thank you.

[RuralGuy]

Thanks for posting back with your success and solution.