Cascading Combobox problem

[Ken]

I have an Access 2003 database with two tables: tblWriters and tblProjects.

I created a query, qryForComboBox, that lists all of the project names from
tblProjects with the appropriate writer names from tblWriters.

I’m trying to develop a form with cascading combo boxes. The Writers
combobox needs to show all the writers in qryForComboBox, and the Projects
combobox needs to show all the projects from qryForComboBox for the selected
writer.

The problem is that when I select a writer from the Writer combobox, and
then select one of that writer’s projects from the Project combobox, and then
press Enter, the writer name in one of the records in the Writer table gets
overwritten with someone else’s name!

Since the form and the comboboxes look exclusively at qryForComboBox and
none of the tables, I don’t know why tblWriters is getting updated. It
shouldn’t. Can anyone offer any suggestions?

 

[Piet Linden]

Try this:
http://www.mvps.org/access/forms/frm0028.htm

 

[Piet Linden]

I have an Access 2003 database with two tables: tblWriters and tblProjects.

I created a query, qryForComboBox, that lists all of the project names from
tblProjects with the appropriate writer names from tblWriters.

I’m trying to develop a form with cascading combo boxes. The Writers
combobox needs to show all the writers in qryForComboBox, and the Projects
combobox needs to show all the projects from qryForComboBox for the selected
writer.

The problem is that when I select a writer from the Writer combobox, and
then select one of that writer’s projects from the Project combobox, and then
press Enter, the writer name in one of the records in the Writer table gets
overwritten with someone else’s name!

Since the form and the comboboxes look exclusively at qryForComboBox and
none of the tables, I don’t know why tblWriters is getting updated. It
shouldn’t. Can anyone offer any suggestions?

Hang on... answered too soon... How are Writer and Project related?
Can a Project have more than one Writer? Sounds like you might be
missing a table. If the following is true, you're missing a table:

Each Writer can work on zero or more Projects
Each Project can be worked on by zero or more Writers.

Writer---(1,M)---AssignedTo--(M,1)---Project.

Then you won't overwrite anything. You can have multiple writers on
each project and each writer assigned to multiple projects. Then if
you wanted to track hours, you'd add a child to AssignedTo (the
intersection of Writer and Project).

 

[Ken]

There is a one-to-many relationship between writers and projects. Each writer
has many projects, but each project is assigned to only one writer.

 

[Steve Schapel]

Ken,

Since the form and the comboboxes look exclusively at qryForComboBox and
none of the tables, I don’t know why tblWriters is getting updated.

But isn't the qryForComboBox query based on the tables? So therefore,
changing data in the qryForComboBox query *will* directly and
immediately affect the data in the tables that are part of the query.

And the data that is affected will be the field that the combobox is
*bound* to. So it seems to me that the combobox is bound to a field in
the form's underlying Record Source. So editing the data in the
combobox is tantamount to editing the data in that field.

I am not sure of the solution, because it isn't clear what the purpose
and function of these comboboxes might be. Perhaps they should actually
be unbound controls.

 

[Ken]

Steve,

Thank you for your comments. Perhaps I oversimplified my explanation of the
problem, but I think you've pointed me in the right direction. I'll try
unbinding the comboboxes when I get to work Monday.

 

[Ken]

I'll try unbinding the comboboxes when I get to work Monday.

I must be overlooking something very basic. The comboxes must be bound
because they must display data. But they must not allow existing data to be
updated. How do I accomplish that?

 

[Steve Schapel]

Ken,

I am not 100% sure whether I am understanding you fully. If you have a
combobox or other bound control, which is, as you state, displaying
data, and then you edit/change the data displayed in the control, the
existing data must be updated. What do you want to happen to the
existing data after you have replaced it with something else?
Therefore, we have the concept of *records*, so the data displayed in
the control will be the data in the field your control is bound to, for
the form's current record. And then you can navigate from record to
record via the form, and thus the data displayed will change as gthe
current record changes. Or you can go to a new record and use the
controls on the form to enter the data for the new record, which will
not affect the data in existing records.

Does that help in any way?

 

[Ken]

If I select a writer from the Writer combobox and then click the arrow on the
Projects combobox, the Projects combobox correctly displays the projects for
the selected writer. If I then select one of the displayed projects and press
Enter, the writer name in one of the writer records gets changed. That
shouldn't happen.

Once I get this glitch figured out, I'll add a subform that displays (and
allows updating) all of the Task records for the selected writer/project. I
haven't even created the Tasks table yet because it makes no sense to add
another level of complexity until I get the combobox problem resolved.

I have a small sample database that illustrates this problem, but I don't
see a way to upload it with this post.

 

[Ken]

Steve, thanks for your earlier suggestion that the problem was the bound
controls. That turned out to be right on. When I got your suggestion, I
didn't use the proper method to unbind them. I just now removed the Control
Source from the two comboboxes, and it works perfectly.

Thanks again

 

[Steve Schapel]

Very good, Ken. I am pleased to know that it is now sorted. Best
wishes with the rest of the project.