Hi there:

I have a form that has both a personal data (child) table/form component and a sub-form component that tracks a personal device (PD) that is being loaned to the child.

I have 4 tables:

1. Family for the Child
2. Children (linked to Family using Family ID)
3. PD_Inventory (list of personal devices and their details
4. PD_Loan (list of loan details - linked by PD_Inventory_ID to the Inventory table and also by Child_ID to the Children table)

Here is what I want to do:

When the user updates the loan data in the sub-form, I want to trigger the AfterUpdate (or OnChange event, if the field has a drop-down menu component) on certain fields so that it does one, possibly two, things:

1. Updates the PD_Loan_Last_Update_Date to today's date.

NOTE: This I want done for many fields (about 3-4 of them total on the subform. I just use the same code for each)

2. Updates the Availability in the PD_Inventory table for this PD so that it is set from "Yes" (default value when creating new PDs in that table) to "No").

NOTE: I only enable this functionality on the PD_Loan_Date_In field.

Here is a complete list of the fields and events I am using:

PD_Status - textbox - AfterUpdate
PD_Type - textbox - AfterUpdate
PD_Available - combobox - OnChange
Condition - combobox - OnChange
Notes - textbox/memo field - AfterUpdate

The basic functionality works - sort of. The code I am using seems be confusing Access 2007 a little, despite the fact that the update SQL is correct and *does* fire. When I go back to the source tables the correct updates have been applied, so that part is OK.

However, the code typically does not fire properly until the second time I force an update of the relevant field, and sometimes I also get error 3197, or when it does fire on the second time, Access complains that I am overwriting values from another user.

I suspect that the issue is because Access thinks I am two users when I am in fact the SAME user.

The file IS located on a shared network drive, but I am the only person connecting to the database when I am testing the code.

Here is the code sample for PD_Loan_Date_In.

The code is the same for the relevant fields and events used all across the sub-form, with the exception of me simply updating the date last modified on other fields:

Private Sub PD_Loan_Date_In_AfterUpdate()

On Error GoTo ErrorTrap:

Dim conn As ADODB.Connection
Set conn = CurrentProject.Connection

Dim PDLoanVal As Variant
PDLoanVal = Me.PD_Loan_ID.Value

Dim PDIDVal As Variant
PDIDVal = Me.PD_ID.Value

Dim CurDate As Variant
'CurDate = Now() ' used in place of Now(), which stores date and time..
CurDate = Format(Now(), "mm/dd/yyyy")

Dim T1 As String
T1 = "PD_Loan"

Dim F1 As String
F1 = T1 & ".PD_Loan_Date_Modified"

Dim F2 As String
F2 = T1 & ".PD_Loan_ID"

Dim T2 As String
T2 = "PD_Inventory"

Dim F3 As String
F3 = T2 & ".PD_Available"

Dim F4 As String
F4 = T2 & ".PD_Date_Modified"

Dim F5 As String
F5 = T2 & ".PD_ID"

Dim strSQL As String
strSQL = "UPDATE " & T1 & " SET PD_Loan.PD_Loan_Date_Modified=#" & CurDate & "# WHERE (((" & F2 & ")=" & PDLoanVal & "));"

' MsgBox strSQL

'update values as needed
conn.Execute (strSQL)

Dim strSQL2 As String
strSQL2 = "UPDATE " & T2 & " SET " & F3 & " = 'Yes', " & F4 & "=#" & CurDate & "# WHERE (((" & F3 & ")=" & "'No'" & _
") AND ((" & F5 & "=" & PDIDVal & "));"

' MsgBox strSQL2

'UPDATE PD_Inventory SET PD_Inventory.PD_Available = "No", PD_Inventory.PD_Date_Modified = Now()
'WHERE (((PD_Inventory.PD_Available)="Yes") AND ((PD_Inventory.PD_ID)=36));

conn.Execute (strSQL2)

' repaint and requery after connection is closed

MsgBox "PD Inventory Availability and Loan Date Modified Values Updated.", vbInformation, "PD Inventory Availability and Loan Date Modified Values Updated"

Set conn = Nothing


Exit Sub


Select Case Err.Number
    Case 3021 ' no current record
        ' MsgBox 3021
        GoTo ErrExit:

    Case 3197 ' double access issue
        MsgBox "PD Inventory Availability and Loan Date Modified Values Updated.", vbInformation, "PD Inventory Availability and Loan Date Modified Values Updated"
        GoTo ErrExit:
    Case 3270 ' property unhandled issue?
        ' MsgBox 3270
        GoTo ErrExit:
    Case 94
        GoTo ErrExit:
    Case Else
        MsgBox Err.Number & ": " & Err.Description, vbCritical, "Database Error: " & Err.Number
End Select
    Resume ErrExit:

End Sub
Right now, my handling of this is kind of a hack.

The events still only fire on the second time, not the first, and even on the second time I see the dialog box prompting the user to overwrite the changes from the "previous user", and I'd like to avoid that if I could.

So I'd much prefer to avoid triggering the three 3000-level errors I am trapping at the moment and perhaps open/close the ADODB connection better, just to tighten it up.

I'm wondering if my not explicitly opening/closing this connection is what is causing the multi-user problem?

Many examples I have seen of this sort of update code do not seem to require it, but that might just be for stand-alone database files.

Any suggestions, ideas, greatly appreciated.