Include form recordset's field in SQL for combobox on the form?

[Max Moor]

Hi All,
I have a form with a combobox on it. The form displays info on workers
at different companies. There is a combobox on the form that displays the
name of the person's supervisor.

The form's record includes the person's contactID, their groupID (or
company, which is really just another record's contactID), and a supervisorID
(which is yet another contact ID).

The combobox's control source is the supervisorID. I want the
rowsource to be an SQL that will grab all records with the same groupID as
the person being displayed on the form. That way, the list will show only
other people working with the same company (groupID).

The SQL is the following...

SELECT tblContacts.ContactID, tblContacts.GroupID, [tblContacts].
[FirstName] & " " & [tblContacts].[lastGroupName] AS SupervisorName
FROM tblContacts
WHERE (((tblContacts.GroupID)= [GroupID] ));


The problem is the WHERE. I need '[GroupID]' to be the value of the
GroupID field in the form's current record. How do I tell Access to grab
that value for the SQL?

Oddly enough, this SQL, as written, doesn't fail. It shows all contact
records, though, rather than the subset I want.

Can I be helped?

Thanks, Max

 

[storrboy]

Oddly enough, this SQL, as written, doesn't fail. It shows all contact

records, though, rather than the subset I want.

Because you're telling to return records where GroupID = GroupID. Of
course each field equals itself.
You need to properly reference the form control, not the field. The
syntax will depend on where you are trying to do this and the datatype
of GroupID, but it is usualy WHERE (((tblContacts.GroupID)= [Forms]!
[FormName]![ControlName] ));

 

[Max Moor]

Oddly enough, this SQL, as written, doesn't fail. It shows all
contact
records, though, rather than the subset I want.

Because you're telling to return records where GroupID = GroupID. Of
course each field equals itself.
You need to properly reference the form control, not the field. The
syntax will depend on where you are trying to do this and the datatype
of GroupID, but it is usualy WHERE (((tblContacts.GroupID)= [Forms]!
[FormName]![ControlName] ));

Actually, I thought of that, but I don't have a control on the form with the
GroupID. I could create a textbox bound to it and hide it, but I wondered if
there was a way to do it otherwise.

 

[storrboy]

... I could create a textbox bound to it and hide it, but I wondered if

there was a way to do it otherwise.

That would be the simplest. I think you would otherwise have to make
the sql string in code by looking at the form's recordset. Referencing
a form control would be almost automatic. You might only need to
requery the combo on occasion.