Simultaeously Updating Two Tables

[Wayne]

Greetings. I have a table (tblLastNum) containing the last report
numbers for several sites, such as:

AL 001
MS 009
IL 020

A second table (tblReports), contains data a user enters on a form,
including a site selected in a combo box (Row Source is the table
above). I don't want the last number at this point because the user
may decide to cancel the record, or another user may submit their
record while this one is still being edited.

When the user clicks on a submit button, I want to:
1) Increment the last report number for the applicable site in the
tblLastNum table;
2) Populate a ReportNum field on the form with the new number (thereby
adding it to the user's record in tblReports).

I got the first step working by running the following update query:
UPDATE tblSites SET tblSites.LastNumber = [LastNumber]+1 WHERE
(((tblSites.Site)=[Forms]![frmReport].[Site]));

How can I do both updates in one step so I don't have a conflict with
multiple users?

Thanks,
Wayne

[salad]

Wayne wrote:

Greetings. I have a table (tblLastNum) containing the last report
numbers for several sites, such as:

AL 001
MS 009
IL 020

A second table (tblReports), contains data a user enters on a form,
including a site selected in a combo box (Row Source is the table
above). I don't want the last number at this point because the user
may decide to cancel the record, or another user may submit their
record while this one is still being edited.

When the user clicks on a submit button, I want to:
1) Increment the last report number for the applicable site in the
tblLastNum table;
2) Populate a ReportNum field on the form with the new number (thereby
adding it to the user's record in tblReports).

I got the first step working by running the following update query:
UPDATE tblSites SET tblSites.LastNumber = [LastNumber]+1 WHERE
(((tblSites.Site)=[Forms]![frmReport].[Site]));

How can I do both updates in one step so I don't have a conflict with
multiple users?

Thanks,
Wayne

Instead of using a query to update, perhaps use a recordset. Ex:
Dim blnExclusive As Boolean
Dim rst As Recordset
On Error GoTo 0
On Error Resume Next
Set rst = CurrentDb.OpenRecordset(strFile, dbOpenDynaset,
dbDenyWrite + dbDenyRead)
blnExclusive = (Err.Number = 0)

You could put something like this in a dowhile. If you know you'll open
table1 first, then table2 then get table1 exclusive, open table2
exclusive, then update then close. Put in something that can pause the
dowhile if blnExclusive is false.