Query for combo box

H

HeatherD25

I can't figure out what I'm missing in this query... I think it should be
pretty straight forward. Here's the SQL code:

SELECT DISTINCT tbl_CONTACTS.CONTACT_ID, tbl_CONTACT_TYPES.CONTACT_TYPE AS
[Contact Type], tbl_CONTACT_TYPES.CONTACT_TYPE_ID,
tbl_CONTACT_FACILITIES.FACILITY_ID
FROM (tbl_CONTACTS INNER JOIN tbl_CONTACT_TYPES ON tbl_CONTACTS.CONTACT_TYPE
= tbl_CONTACT_TYPES.CONTACT_TYPE_ID) INNER JOIN tbl_CONTACT_FACILITIES ON
tbl_CONTACTS.CONTACT_ID = tbl_CONTACT_FACILITIES.CONTACT_ID
WHERE (((tbl_CONTACT_TYPES.CONTACT_TYPE_ID)=2) AND
((tbl_CONTACT_FACILITIES.FACILITY_ID)=[FORMS].[frm_RAC_MAIN].[FACILITY_ID]));

I'm trying to narrow the combo box list down by having it only return
contacts assigned to a facility that selected on the form
([FORMS].[frm_RAC_MAIN].[FACILITY_ID]). The query above returns nothing.
When I do a message box for the value in the vb code it returns 1. When I
put just the number 1 in the criteria it works, but it's not working with the
reference. Any ideas what's going on? What am I missing?

Thanks!
Heather
 
K

Ken Sheridan

Heather:

I don't know if its still the case but in earlier versions of Access it was
necessary to use the exclamation mark, not the dot operator when referencing
controls on forms as parameters in a query:

[FORMS]![frm_RAC_MAIN]![FACILITY_ID]

Ken Sheridan
Stafford, England
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top