Dirk,

You've been a great deal of help so far. Thanks for taking the time to help
me through this.

After your last post, I went back and made some changes based on your
recommendations. I deleted the tblAuditBoxOffice since it served no real
purpose. I did keep the table that stores information on the audit files
(tblAuditRecordsOffice) because the security requirements for it are
different than for the warehouse table (tblAuditRecords).

I was able to get my coding to work in a test environment, but when I moved
it into production I encountered some errors.

My test environment has the same references, but the forms and tables were
in the same .mdb file. Also, the production environment is secured using user
level security.

Initially I received an error that indicated that my provider did not
support using an index (run-time error '3251'). I researched that error and
then tried establishing my ADO connection directly to the back-end.

Now I'm encountering a different run-time error: '-2147217843 (80040e4d)'.
Cannot start your application. The workgroup information file is missing or
opened exclusively by another user.

Here is my code as it stands now:

Private Sub btnMove_Click()
Dim rstAuditOffice As New ADODB.Recordset
Dim rstNewBox As New ADODB.Recordset
Dim intNumSelected As Integer
Dim conn As New ADODB.Connection
Dim strUsr As String
Dim strPwd As String
Dim strDBName As String
Dim varItm As Variant
Dim strSQLInsertBox As String
Dim strSQLInsertRecords As String
Dim strSQLDeleteRecords As String
Dim intRecordType As Integer
Dim intBoxID As Integer
Dim intBoxAutoID As Integer
Dim strRecordFilter As String

intBoxID = CInt(Me!txtNewBoxNum.Value)
intRecordType = Me!intRecordType.Value

'Define connection
strUsr = "rbain"
strPwd = "fire&ice4u"
strDBName =
"\\Blue7\SYS1\SHARE\MEDICARE\Medshare\TRAINING\RCRDRET\recret_be.mdb"
With conn
   .Provider = "Microsoft.Jet.OLEDB.4.0"
   .ConnectionString = "Data Source=strDBName; User ID=strUsr;
Password=strPwd"
   .Open
End With

'Set conn = CurrentProject.Connection

If ValidateBoxNum(txtNewBoxNum.Value) Then
   'Build SQL statement to add a new box
   strSQLInsertBox = "INSERT INTO tblAuditBox (BoxID,DepartmentID,TypeID)"
   strSQLInsertBox = strSQLInsertBox & " VALUES (" & intBoxID & ",6," &
intRecordType & ");"

   'Add new box
   conn.Execute strSQLInsertBox

   rstNewBox.Open "Select Max(BoxAuto) as MaxAuto from tblAuditBox", conn
   intBoxAutoID = rstNewBox("MaxAuto")
   Set rstNewBox = Nothing
   
   'Verify that items are selected
   intNumSelected = Me.list_office_records.ItemsSelected.Count
       If Nz(intNumSelected) = 0 Then
       MsgBox ("You must select at least one record.")
       Exit Sub
   End If
   
   'Open tblAuditRecordsOffice
   rstAuditOffice.Open "tblAuditRecordsOffice", conn, adOpenKeyset,
adLockOptimistic, _
   adCmdTableDirect
   
   
   'Begin transaction
   conn.BeginTrans
   '******
   '
   rstAuditOffice.Index = "RecordID"
   '
   '******
       
   For Each varItm In Me.list_office_records.ItemsSelected
   'Find record in tblAuditRecordsOffice
   rstAuditOffice.Seek list_office_records.ItemData(varItm)
   
   'Update it with new box number and auto box number
   rstAuditOffice!BoxID = intBoxID
   rstAuditOffice!BoxAuto = intBoxAutoID
'    rstAuditOffice!Row = ""
'    rstAuditOffice!rack = ""
'    rstAuditOffice!Shelf = ""
   rstAuditOffice.Update
   
   Next varItm
   
   conn.CommitTrans
   rstAuditOffice.Close
   

'Insert new records into the tblAuditRecords
strSQLInsertRecords = "Insert into tblAuditRecords Select "
strSQLInsertRecords = strSQLInsertRecords & "tblAuditRecordsOffice.BoxAuto,"
strSQLInsertRecords = strSQLInsertRecords & " tblAuditRecordsOffice.BoxID, "
strSQLInsertRecords = strSQLInsertRecords & "
tblAuditRecordsOffice.ProviderName,"
strSQLInsertRecords = strSQLInsertRecords & " tblAuditRecordsOffice.FYE, "
strSQLInsertRecords = strSQLInsertRecords & "
tblAuditRecordsOffice.DestroyDate, "
strSQLInsertRecords = strSQLInsertRecords & "
tblAuditRecordsOffice.AuditSubTypeNumber"
strSQLInsertRecords = strSQLInsertRecords & " FROM tblAuditRecordsOffice"
strSQLInsertRecords = strSQLInsertRecords & " WHERE
tblAuditRecordsOffice.BoxID= "
strSQLInsertRecords = strSQLInsertRecords & intBoxID
conn.Execute strSQLInsertRecords

'Delete records from tblAuditRecords
strSQLDeleteRecords = "DELETE * FROM tblAuditRecordsOffice WHERE BoxID = "
strSQLDeleteRecords = strSQLDeleteRecords & intBoxID
conn.Execute strSQLDeleteRecords

'Clear list box and other controls
For Each varItm In Me.list_office_records.ItemsSelected
list_office_records.Selected(varItm) = False
Next varItm
Me!intRecordType.Value = Null
Me!txtNewBoxNum.Value = Null

'Requery combo box
Me!list_office_records.Requery
Me.list_office_records.SetFocus

'Open report
strRecordFilter = "tblAuditBox!BoxID = " & intBoxID
DoCmd.OpenReport "rptAuditSingleWarehouse-Move", acViewPreview, ,
strRecordFilter

Else
   txtNewBoxNum.SetFocus
   btnMove.Enabled = False
End If
End Sub