Drop-down that's determined by another doesn't work in subform

[redaccess]

I've got two combo boxes on a subform. The second combo's list is determined by what's selected in the first combo by using the following code in its rowsource property.

SELECT DISTINCTROW [tp_id], [Tech_Product] FROM [tbl_Tech_Type] where [tc_id] = [forms]![frm_client_tech]![tc_id];

(I refresh the form after the first combo is updated)

This works great when the form stands alone (ie.. not a subform). But when I add it to another form as a subform, the [forms]![frm_client_tech]![tc_id] isn't returning a value so everytime I open the form, I get asked for its value.

Any suggestions on how to correct this?

 

[redaccess]

Actually I just figured it out. What I did was changed this code:

[forms]![frm_client_tech]![tc_id]

to this:

[forms]![frm_client]![frm_client_tech]![tc_id]

The first line of code was looking at the wrong form. I needed to look at the main form first [frm_Client] THEN look at the subform [frm_client_tech].

 

[JeremyNYC]

First off, the correct way to refer to that control will be:
[forms]![frm_client]![frm_client_tech].[form]![tc_id]

Next, I would suggest not using this in a subform, because you'll change the rowsource for the second combo and then you'll move to the next record and the rowsource will still be tweaked. Lots of people do this and end up running screaming from the process with a migraine.

Jeremy =============
Jeremy Wallace
Designing, Developing, and Deploying Access Databases Since 1995

http://www.AlphaBetCityDataworks.com

Take a look at the Developers' section of the site for some helpful fundamentals.