Cascading Combo Boxes trouble

[Monterey_Manzer]

Hello All,

I am trying to get a series of combo boxes to interact and record the selections made in new records within a destination table. Here is situation with respect to the tables involved:

1 table lists the names/acronyms of various research facilities and the branch to which they belong (acronym is PK)
1 table lists Financial Points of Contact for each facility (acronym is FK to relate to earlier table)


1 table lists Technical Points of Contact for each facility (acronym is FK to relate to Facility table)
1 table lists program participants and the branch they belong to (Branch in FK to relate to Facility table)

I am trying to create a form that allows me to set the participant and, from this selection, restricts the facility choices in the Facility combo box to those that fall within the branch to which the participant is assigned. I think the next step is pretty obvious too. Once the facility is selected I have combo boxes that would only display those Financial and Technical PoCs assigned to that facility.

I have watched the 4 Data Pig tutorials and I can easily make functional cascading combo boxes as long as they only refer to the look up tables. I generated perfectly functioning cascading combo boxes on a test form. What I need help with is how do I generate combo boxes that will then store the info selected in my destination table? Whenever I try to set a Control Source it disrupts the functioning of the queries. Any guidance would be greatly appreciated!

Thanks
Monterey_Manzer

[June7]

What do you mean by 'disrupts the functioning of the queries'?

Are these comboboxes in a Continuous or Datasheet form? Dependent combo/list boxes in those form views do not work well. It is a known issue with combobox using lookup. When the RowSource property is changed it is changed for all instances of the combobox, because there really is only one combobox, as you can see in Design view. So the related values are not available for display in all of the records as they have been filtered out of the RowSource set.

[Monterey_Manzer]

Okay so I think I've refined my problems.

First I need to further explain the set up: I select participants from a combo box. These participants are identified by a unique ID # in Access but in most correspondence we simply use names. To help with this, I set up the Participant combo box to select both the ID and the name ([Last Name] & ", " & [First Name]). The participants I am interested in are a particular subset of the total and there are three "Where" statements in the query as well as the two fields mentioned so that only those participants I want show up.

The query for my Facility combo box includes two "Where" criteria which states that the record in the Participant Information table must match the ID selected in the first column of the selection in the Participant combo box and then only show facilities that fall under the branch that is designated in that Participant record. The point of contact combo box queries are a more straightforward and only include one Where statement.

Okay...so, now I have my set up with my four combo boxes on a form. I can select a specific participant and only the appropriate facilities show up. I can generate new records in my destination table and everything is peachy keen. That is until....I try to add this form to my Navigation form. I loaded the form with the combo boxes into my Navigation form yesterday and everything was great. However, I open up my file this morning and, when accessed through the Navigation form, I cannot generate new records and when I select a participant in the first combo box the query apparently cannot find the ID since it prompts me to enter the ID so the facility query can run. If there is a way to get the navigation form to function properly I would greatly appreciate it.

I am currently the only person who uses this database but I will be asked to share it with others soon. I prefer not to let people monkey around with the structure of my queries and directly manipulate data in tables so I tend to restrict the Navigation pane and simply use a Navigation form instead. Thanks!

[Monterey_Manzer]

Okay....so it seems this has transitioned to a general problem with my navigation form. It no longer allows me to edit any information contained in the forms...[sigh]. Perhaps too many late additions to an existing navigation form have created some issues. I will start from scratch. Any clues on what the particular errors are that create this situation? By the way, I don't get the message I am used to seeing, telling me the record set cannot be edited. It simply will not allow any changes.

[June7]

I would have to analyse the db. If you want to provide, follow instructions at bottom of my post.