Populate unbound combo box when reviewing data

[hilian]

In my db, there are agencies, which can have one or several programs, programs that can have one or several cases and cases that can have one or several episodes. Agencies, Programs, Cases and Episodes are tables linked on one-to-many relationships.

I am trying to set up a form that displays all of the information for a case: episodes, program and agency. I have a form for cases with a sub-form for episodes. That part works; when I scroll through the cases, the combo box for Programs populates, but I don’t know how to get the combo box for Agencies to populate. Agencies is linked to Programs by primary key/foreign key, and Programs is linked to cases the same way, as are cases and episodes. The Agencies combo box is unbound because there is no need to store Agency information in the cases table.

I can live with this for date entry. The user selects an agency, and a dependent combo box limits the selection to its programs. The problem occurs when the user needs to review or correct case information. Then the Agencies combo box doesn’t populate, leaving the information incomplete.

I know that there’s code that will populate the Agencies combo box, but I can’t figure out what it is. I’ve tried the DLookup function, but I can’t get the syntax correct. Is this the best way to do it, or if not, what is?

Thanks,



Henry

[Missinglinq]

If I understand your scenario correctly, it sounds as if using DLookup would be valid; what code syntax are you using that isn't working?

Linq ;0)>

[hilian]

Thanks for responding so quickly.

In the after update event I entered:

Me.tblAgency = DLookup([tblProgram]![AgencyFK])

I also tried it in the On Change event, which I think is where it actually belongs, since I want the agency to change when the user scrolls through cases. It didn't work there either.