rowsource of a lookup field dependent on fieldvalue in same record in a subform

[oharmsen]

I want to create a different rowsource-query for a lookup field (field1) in each record in a subform.


The rowsource changes dependent on the value in another field (field2) in the same record.
How can this be done?
- I tried to change the rowsource-query in an eventmacro when the focus is set to field1, but this ofcourse changes the rowsource for all field1's and makes the allready selected values unvisible.
- i think i have to include the value of record 'field2' in the rowsource query, but i cannot find a way to include that value in the query
something like:
lookup field1 in the subform contains this rowsource
- SELECT CUSTOMER.Id, CUSTOMER.AGE, CUSTOMER.NAME
FROM CUSTOMERS
WHERE (CUSTOMER.AGE= me![field2]);

me![field2] however does not function

please HELP !!

[orange]

You are referring to table CUSTOMER and CUSTOMERS -- can't be both. Which is the correct name of the table?

You also have a syntax error

...WHERE (CUSTOMER.AGE= me![field2]);

try
.....WHERE (CUSTOMER.AGE=" & me![field2] &");"

[June7]

If form is in datasheet or continuous view and combobox has conditional RowSource with lookup alias, then the situation of values not showing will persist no matter how you set the RowSource.

[oharmsen]

With other words.... you say there is no solution for this problem?

[June7]

Sort of.

Include the lookup table in the form RecordSource (join type "Include all records from {data table} and only those from {lookup table} that match"). Bind textbox to the alias field from lookup table. Set properties Locked Yes and TabStop No.

[oharmsen]

Thanks for your help I understand that you understand the situation. However until now i still didn't manage to make it work with your suggestions, the lookup field now sowhow doesn't accept the chosen input, it only accepts when i use the real record-id, not with the alias. I thought maybe i create 2 field, one that shows the list items (from the record-conditional query ( just a narrow dropdown list) and then the chossen value is displayed in another recordfield...... i let you know if it works.