updating from multiple listboxes

[Bal]

Hi everyone,

I'm brand new to the site, and I'm really impressed with the community so far.


Anyway, I have a form in Access 2007 that is a series of unbound columns. One set of columns is used to navigate through a series of category tiers and is populated with an OnClick event setting lst_tierX.RowSource. Each value in the listbox is a description column from an underlying table. Another listbox displays a 'to be assigned' value, also determined by a RowSource from a separate table.

Currently to assign the value from the first box to the second I am using a query to populate a third listbox with the key of the first, and an OnClick event there runs an UPDATE query (referring to this listbox in the SET clause). I did this as a workaround because I couldn't figure a better way to get it done. Here's the rub, I need to assign three values now.

Using my current method the only way I know how to get the information to update is by actually clicking on the listbox (so that forms!sexton!lst_tier4 is assigned a value).

Is there a better way to do this? I'm sure there must be, but my knowledge of Access and VBA is... limited. Code included below as it may explain better than I can..

Private Sub lst_tier4_AfterUpdate ()
lst_CatID.RowSource = "SELECT [qry_tier4].[Brick Code] FROM qry_tier4 WHERE [brick description] = lst_tier4;"
END SUB

lst_CatID has an OnClick event that runs a macro including this UPDATE:

UPDATE tbl_mappee 
SET catassign = forms!sexton!lst_CatID
WHERE category = (
SELECT DISTINCT category
FROM tbl_mappee
WHERE catassign IS NULL
and updateid = 1
);

and to preempt any questions like, "Why didn't you just do this simple-but-somewhat-technical thing?"
The answer is, "Because I don't know what that even means."

Thanks for any help