A2007 Sub-Form: Minor Event/Update Issues...

[marcus101]

Hi there:

Using Microsoft Access 2007, 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)

Me.Requery
Me.Refresh
' 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

ErrExit:

Exit Sub

ErrorTrap:

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.

Or maybe make an adjustment to my code so that the execute command is more efficient.

I tried building a crude QueryDef to track whether or not the query fires and closes in more detail, but ran into some syntax and some object handling issues with that.

Suggestions/thoughts?

Thanks,

m101

marcus101
Access/SQL/XML Developer
Ottawa, Canada

 

[marcus101]

Well, nobody's replied to my message, but I think I may have an answer. I just need to test it out.

The problem lies in the multi-user aspect of the database.

The code works better if I am on a single user PC, but when the database is hosted on a fileshare, it works differently.

I've read that when you are editing a record in this situation, the form needs to be closed, so either:

1. I will need to close the form before running the ADODB code somehow

2. I will need to open a dummy form with the ADODB code in the OnOpen event, but use a timer or delay so that the other form can be closed before it can execute.

3. Assuming I need to open a dummy form, I need to maintain a lock on the ID of the relevant table so I can reopen the original form and then cursor/move to that record, so I can maintain a sense of usability/functionality.

4. I am assuming that an explicit Open statement for the current Code block will not be enough to get around the multi-user method and ways things operate.

If anyone has any thoughts on this, agreements, disagreements, clarifications, etc, they would be most welcome.

Thanks,

m101

marcus101
Access/SQL/XML Developer
Ottawa, Canada