Can't Acess filter combo box rowsource by field variable?

[Brian Goodheim]

Trying to use a combo box lookup field in a primary table to select contact
personnel in a secondary table. The lookup field is supposed to return only
personnel working for the clientID of the active datasheet record, but
instead the combo box is populated with personnel form every client. When I
enter an ASCII constant or parameter as criterea, the query works fine. But
I can't get just the current client's contacts to display by entering the
field name as criterea. The SQL looks like this:

SELECT Reps.REPID, Reps.[rep-ln], Reps.CLIID
FROM Reps
WHERE (((Reps.CLIID)=[Assignments].[ClientID]))
ORDER BY Reps.[rep-ln];

I've come to the conclusion that Access can't use field names in the primary
table as variable criterea passed to the query. Am I wrong?

Thanks.

-Brian

 

[Michel Walsh]

Hi,

You are right, but we can bypass that restriction. I assume that
Assignments is the table (or a table from the query that is) bound to the
form (form record source). If so, use


WHERE Reps.CLIID=FORMS!yourFormNameHere!ClientID


assuming ClientID a control bound to the field of the same name.


The syntax FORMS!FormName!ControlName is accepted in the RowSource, in
Dxxx functions, with DoCmd, and in the Query Designer in immediate mode, BUT
NOT with CurrentDb.




Hoping it may help,
Vanderghast, Access MVP

 

[John Vinson]

Trying to use a combo box lookup field in a primary table to select contact
personnel in a secondary table. The lookup field is supposed to return only
personnel working for the clientID of the active datasheet record, but
instead the combo box is populated with personnel form every client. When I
enter an ASCII constant or parameter as criterea, the query works fine. But
I can't get just the current client's contacts to display by entering the
field name as criterea. The SQL looks like this:

SELECT Reps.REPID, Reps.[rep-ln], Reps.CLIID
FROM Reps
WHERE (((Reps.CLIID)=[Assignments].[ClientID]))
ORDER BY Reps.[rep-ln];

I've come to the conclusion that Access can't use field names in the primary
table as variable criterea passed to the query. Am I wrong?

You are correct. You cannot use a variable fieldname. But that does
not appear to be what you're doing! If you want to use a *FORM
CONTROL* as a criterion you need to remember that a Field and a
Control are two quite different objects. If you have a Form named
Assignments, with a Combo Box named ClientID, you can use a criterion

=[Forms]![Assignments]![ClientID]

So long as the form Assignments is open, the query will search for the
currently selected ClientID in that combo box.

You will need to Requery the Contact combo box in the afterupdate
event of the Client combo box, however, so the combo box is "aware"
that its rowsource has changed:

Private Sub ClientID_AfterUpdate()
Me!comboboxname.Requery
End Sub

John W. Vinson[MVP]
Join the online Access Chats
Tuesday 11am EDT - Thursday 3:30pm EDT
http://community.compuserve.com/msdevapps

 

[Brian Goodheim]

If only a datasheet representation of the table is open, rather than a form,
can the current field value be retrieved for use (perhaps as a parameter) in
rowsource SQL? In other words, can the current record field values in an
open datasheet be referenced as a control object would be on a form? It
seems silly that only a form control can reference field values for the
current record.

Thanks to John Vinson and Michel Walsh for the replies.

-Brian Goodheim

Hi,

You are right, but we can bypass that restriction. I assume that
Assignments is the table (or a table from the query that is) bound to the
form (form record source). If so, use


WHERE Reps.CLIID=FORMS!yourFormNameHere!ClientID


assuming ClientID a control bound to the field of the same name.


The syntax FORMS!FormName!ControlName is accepted in the RowSource, in
Dxxx functions, with DoCmd, and in the Query Designer in immediate mode, BUT
NOT with CurrentDb.




Hoping it may help,
Vanderghast, Access MVP

Trying to use a combo box lookup field in a primary table to select
contact
personnel in a secondary table. The lookup field is supposed to return
only
personnel working for the clientID of the active datasheet record, but
instead the combo box is populated with personnel form every client. When
I
enter an ASCII constant or parameter as criterea, the query works fine.
But
I can't get just the current client's contacts to display by entering the
field name as criterea. The SQL looks like this:

SELECT Reps.REPID, Reps.[rep-ln], Reps.CLIID
FROM Reps
WHERE (((Reps.CLIID)=[Assignments].[ClientID]))
ORDER BY Reps.[rep-ln];

I've come to the conclusion that Access can't use field names in the
primary
table as variable criterea passed to the query. Am I wrong?

Thanks.

-Brian

 

[John Vinson]

If only a datasheet representation of the table is open, rather than a form,
can the current field value be retrieved for use (perhaps as a parameter) in
rowsource SQL

YOu can use the DLookUp function, with criteria, to find a record in a
table - but AFAIK you cannot reference a table datasheet or use its
values for much of anything.

John W. Vinson[MVP]
Join the online Access Chats
Tuesday 11am EDT - Thursday 3:30pm EDT
http://community.compuserve.com/msdevapps