Generic Lookup Query for COMBO BOX- supplying specific whereclause

[Guest]

Hey, I'm looking for the proper syntax. (Access 97) I have a code table,
which contains 3 fields, code, description and type. Sample entries would
be
High, High Risk, RISKTYPE
Medium, Medium Risk, RISKTYPE
Daily, Daily Transaction, FREQUENCY
Monthly, Monthly Transaction, FREQUENCY

I have created a generic query named CODE LOOKUP,
SELECT Code.code, Code.description, Code.type FROM Code;
which I have assigned to the ROWSOURCE property of the combo box.

and then depending on which combo box has the focus supply the where clause.
IE if I am focused on the Frequency ComboBox the where clause would be
WHERE (((Code.type)="FREQUENCY"));

if the focus is the RISKTYPE combo box then the where clause would be WHERE
(((Code.type)="RiskRate"));

IS there a way to concatenate the specific whereclause at the rowsource
property or using an event procedure?

Thanks

 

[Michel Walsh]

Hi,



If you really mean the control having the focus, you may try the following:

in the GotFocus event of the appropriate combo box, add a line of code like:


Me.ComboBoxName.RowSource = "SELECT ... FROM ... WHERE code.type=
'FREQUENCY' "


for the control linked to frequency. Do the same for the other controls.


Hoping it may help,
Vanderghast, Access MVP

 

[Guest]

Thanks Michel, I considered just writing the sql statement at the Rowsource
control, but I was really hoping to be able to reuse the generic query and
supply the whereclause. Is it possible??? Thanks

 

[Michel Walsh]

Hi,



It is possible if the control that holds the information is always the same.
Use the syntax FORMS!formName!ControlName. That control can be hidden, on
your form, and you are careful to push the right value (RISKTYPE, FREQUENCY,
.... ) in it when the relevant visible control get the focus.

You have to requery the list when the combo box itself got the focus (in its
GotFocus event):

Me.ComboBoxName.RowSource=Me.ComboBoxName.RowSource


It is generally useless to requery it otherwise, since that list is not
visible anyhow, under other circumstances.


Hoping it may help,
Vanderghast, Access MVP