Looking up within a subform

[canfish]

Hey everyone,



I can't figure out how to do this:

I am making a subform that will fill in information about Radio Stations with which the Office being edited in the main form will be associated. I have a junction table between the tblStations and tblOffices, and I have the subform working so that I can enter in the station name, type, and description and associate this information with the office. It works well so far.

What I want to be able to do, though, is within the subform to let the user search up previously added radio stations (which may be associated with a different office), and have the subform automatically fill in the information about that radio station while at the same time associating that information with a new office and a new junction table primary ID (PKOfficeStationID). I have toyed around with using combo boxes, instead of the usual text box I would use to enter in a new radio station for the office, but I can't figure out how to get it to fill in the rest of the information (description, etc.) about the radio station automatically. Also, I want to be able to add in a new radio station for the office if I want to instead of just selecting an old one, and when I use a combo box I can't seem to find an option that will let me type in a new value as well as select other ones. Is there any way to do these things?

If you need more detail in order to help, please ask. And thank you for your help!!

canfish

[pbaldy]

The combo is probably the most common. The way to let the user add new values is to use the Not in List event. Searching here for "notinlist" should turn up code examples. You typically wouldn't save related info about the station in your junction table, just the ID. To display the other info when you make a selection, you make sure the other info is in the combo box row source (it can be hidden), and have textboxes with control sources of:

=ComboBoxName.Column(x)

where x is the column containing the desired info.