Queries in Forms

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hello again, everyone. This question ties into a prior question I had, but
is in a different area.

I have a table that contains a hierarchy used for products. The table is
laid out as:
CategoryID|LevelOne|LevelTwo|LevelThree|LevelFour

I've created a form with 4 comboboxes in it. The first combobox gets its
values from all DISTINCT LevelOne categories (i.e. Accessories, Books,
Shirts, Pants, CDs). For the other comboboxes, I wanted to only show
appropriate values based on what a user selects in combobox one.

To accomplish this, I set up an "OnUpdate" code so that when a user chooses
a value for the first combobox, a query runs. This query creates a new table
from which combobox two displays all valid values (based on the selection
made for combobox one). I then do the same for combobox 3 and 4 (with
queries running to create tables for each combobox).

This all seems to work fine when a user first selects their choices.
However, if a user makes a choice for the 1st combobox, then the second, and
then decides to *change* their selection in the first combobox, I get an
error telling me that the "OnUpdate" function (query) couldn't complete
because the table that would be created is locked by combobox two. This
makes sense because the user had already made an initial selection and thus
'locked' combobox two onto the table.

My question is this -- if someone uses this form, makes a selection and then
changes their selection, how can I get the other comboboxes to "unlock" their
tables so that the query can run properly and create new values?

Or, if there is a better way for me to accomplish this, I would greatly
appreciate it!

Thanks in advance!
 
Badmojoman,

Indeed there is a better way to accomplish this. To begin with, there is no
need to create tables, since all the data is already there, all you need is
queries to read it. Secondly, you may not realize this while the hierarchy
table is still small, but when it grows it will become apparent that it
should be normalized, i.e. split to 4 tables like:

tblHierarchy1
L1ID
CategoryID
L1Description

tblHierarchy2
L2ID
L1ID
L2Description

tblHierarchy3
L3ID
L2ID
L3Description

tblHierarchy4
L4ID
L3ID
L4Description

so each level is only maintained/stored once, and "knows" which higher level
it belongs to. The tables are joined to the next level on the common LXID
field.
So, the rowsource for cboLevel1 would be table tblHierarchy1. The rowsource
for cboLevel2 would be a query on tblHierarchy2 where L1ID = cboLevel1 and
so on for the other two. This done, you would need a macro (or line of code)
run by the Before Update event of each of the first three to Requery the
next combo in the chain, and that's it, no temporary tables or locking
thereof involved.

HTH,
Nikos
 
Back
Top