Problem with multiple combo boxes on the same table

[Peter Stone]

Novice/XP/Access 2003

I have a tblMain with multiple forms to enter data. Because there is
considerable variation between the data on each form, there are many tables
joined one-to-one or one-to-many to tblMain on MainID.

I exceeded 32 indexes on tblMain and so decided to throw all my lookup
tables into one table: tblSubcats and select them into a many-to-many table:
tblMainSubcats. I divided the records in tblSubcats into categories
corresponding to my previous lookup tables.

I now have multiple continuous subforms on tblMainSubcats each one with a
bound combo box selecting one from one category in tblSubcats into
tblMainSubcats. It works fine except that when I close and reopen a form;
only the combo box where I made the first selection displays the selections,
even though all the selected SubcatIDs appear in tblMainSubcats. All other
combos are blank.

I tried changing the combo boxes from continuous forms to single forms to
see if that would help, but no go.

Any suggestions please?

Thank you

Peter

 

[Peter Stone]

Post Script
I should have added an example of what I tried to do to avoid the 32 indexes
limit. here's an example that incorporates the 3 lookup tables listed in my
previous post into 1 lookup table. Each combo selected one Category from
tblSubcats into the many-to-many tblMainSubcats. And caused the problem that
I described in my initial post.

tblSubcats with 3 fields:
SubcatID, CategoryID, Subcategory
1 1 Arts Festival
2 1 Ballet Festival
3 1 Classical Music Festival
4 1 Dance Festival
5 1 Film Festival
6 1 Literary Festival
7 1 Opera Festival
8 1 Theater Festival
9 2 Blues Festival
10 2 Country Music Festival
11 2 Jazz Festival
12 2 Latin Music Festival
13 2 Music Festival
14 2 Popular Music Festival
15 2 Reggae Festival
16 2 Rock Festival
17 2 World Music Festival
18 3 Conference
19 3 Trade Fair

 

[Damian S]

Hi Again Peter,

What a confusing structure... Of course, it's always more confusing in text
than in the database.

You could have a "single" lookup table like this:

tlkEvents:
EventID, EventDescription, EventTypeID
1, Arts Festival, 1
2, Ballet Festival, 1
3, Blues Festival, 2
4, Country Music Festival, 2

linked to this table on EventTypeID
tlkEventType
EventTypeID, EventTypeDescription
1, Arts
2, Music

That way you can simply link to items in tlkEvents and know via the link
what type of Event it is.

Does this help, or am I completely on the wrong track for what you are
trying to achieve?

Damian.

 

[Peter Stone]

Thanks again Damian
I'm so deep into this that I forget there are other ways of looking at it.
I'll explain more clearly. It's a db for a website. We will probably use
MySQL when I've got all my ducks in a row.

The forms will be filled in by writers. A festival form will fill create a
record for a particular festival. They will decide what type(s) of festival
by selecting from a lookup table. I want to keep the lookups lists as short
as possible so that the writer makes the correct selection(s).

For festivals and similar events, I have 41 Subcategories in 5 combos (5
logical Categories). Because in real life, things often don't fall into neat
boxes, I want the writer to be able to make selections from any of the 5
combos. I want separate combos so the writers can focus on one Category, make
(or not make) a selection and move onto the next combo (Category). My
categories and subcategories are based on analysis of existing festivals and
similar events.

This isn't just for festivals, there are many other components, but I in
each case, I want to use lookups to define events, places, businesses, etc.

The writers like the idea; it makes sense to them and makes their job much
simpler.

That's why there are so many joins. The users will be using the lookups to
define things rather than find things.

 

[Damian S]

Hi Peter,

If your problem is purely that the combo isn't repopulating (and it's
working other times for you), check that the datasource for each combo is
correct. To force it to repopulate, you could use
forms!FORMNAME.COMBONAME.requery as applicable (or me.COMBONAME if it's on
the current form).

For instance, you might want to requery them in the On Current event, and
again in the after update or on click event for each combo.

Hope this helps.

Damian.

 

[Peter Stone]

Hello Damien
90% there.

The combos are now repopulating thank you. Problem is they are continuous
forms and the second selection repopulates the second box leaving the first
one blank.

I've just discovered an interesting effect. I can't write more than two
records to my many-to-many table. Any subsequent selections in the combo
boxes overwrite the existing ones.

I'm still digesting this.

 

[Peter Stone]

Good Evening
The various refreshes work thankyou. But I can now see the combo boxes are
overwriting each other's selections.

Remember I am trying to select all my lookup lists from one table into
another many-to-many table to get around the 32 index maximum. My combos are
continuous forms. If I make two selections on my first combo and then make
two selections on my second combo, I overwrite the first selections.

Can I force the second combo to write new records to the table or is what's
happening an inherent attribute of a combo?

Thank you