Updating field based on info in control

[iwasinnihon]

I need to update a field in a table based on information found in a
listbox on a form. This in itself sounds easy. However, it gets a
little complex. Allow me to elaborate.

Table1 has fields ID, title, LicenseCount. ID is the key
Table2 has fields Inv, Type, Location. Inv is the key
Table3 has fields ID, Inv, DateAssigned. ID and Inv are the keys from
Table1 and Table2

If a row from Table2 is deleted the following needs to happen

1) for every row in Table3 that has the Inv from the row that will be
deleted, the LicenseCount from Table1 for the corresponding ID from
Table3 needs to incremented by 1.

2) every row in Table3 that has the Inv from the row that will be
deleted needs to be deleted

3) The row in Table2 needs to be deleted.

I am fine with 2 and 3. It is number 1 that is causing me trouble.

Any ideas on how to accomplish number 1?

 

[kingston via AccessMonster.com]

What is the logic connecting LicenseCount and Table2? I mean without knowing
that a record had been deleted, is there a way to calculate the proper
LicenseCount? I don't think you want to rely on an event in a form to do
maintain this data since records can be deleted directly from Table2 (or a
query on Table2) and you'd never know about it. However, if you want to do
it as you describe, use a form event to do something like the following prior
to the actual deletion:

Make a temporary table that sums the records that will be deleted from Table3
-
"SELECT [ID], Count([ID]) AS NumID INTO TmpTable3 FROM Table3 WHERE ([Inv]="
& Me.Inv & ") GROUP BY [ID];"
Run an update query on Table1 for those IDs that match TmpTable3 -
"UPDATE Table1 INNER JOIN TmpTable3 ON Table1.ID=TmpTable3.ID SET
LicenseCount=LicenseCount+NumID;"
Then proceed with the deletion of the record in Table2 (and subsequent
records in Table3) and remove TmpTable3 if desired.