subform requery's after SQL statement runs

[Guest]

I have table A -->> table B (1:M) with form A, form B, subform B.
Usually, when I add a record in subform B this new record stays
put and another new record can be added. If I have a SQL update
of table A in subform B event form_beforeupdate (as below) the
subform is refreshed, records are sorted, the cursor is placed at
record 1 top left, and the user is upset.

What's happening and can I prevent this?

Offender: CurrentDb().Execute strSQL, dbFailOnError is updating
an audit trail in table B.

Thanks,
Doug

 

[Guest]

Hi Doug,
It's not clear if you are using two sub forms, one for each table, or main
form for TableA and sub form for TableB.

What is the point of running insert query?
Can you post the full code?

At any case you can check help on BookMark, how you can move back to the
record that had the focs in the form

 

[Guest]

Hi Ofer,
I'm using the later, main form for TableA and TableB and a subform for
TableB on form TableB. Form TableA has a button to open form TableB.

The insert SQL updates an audit trail in TableA.
TableB also has an audit trail.

Code from subform TableB: with some added COMMENTs
-----------------------------------------
Private Sub Form_BeforeUpdate(Cancel As Integer)
Dim intnewrec As Integer
'
'update audit trail
intnewrec = Me.NewRecord
If IsNull(GlobalUserID) Then
GlobalUserID = "not available"
End If
If GlobalUserID = "" Then
GlobalUserID = "not available"
End If

'BUT FIRST update Contact table audit trail THIS IS TABLE A
Dim sqlContactNo As Integer
Dim strSQL As String
sqlContactNo = [ContactNo]
strSQL = "UPDATE Contacts SET Contacts.DateModified = Now(),
Contacts.UserModified = '" & GlobalUserID & "' " & _
"Where Contacts.ContactNo=" & sqlContactNo & ";"
CurrentDb().Execute strSQL, dbFailOnError

'THEN update table audit trail THIS IS TABLE B
If intnewrec = True Then
[RecordCreated] = Now
[UserCreated] = GlobalUserID
Else: [RecordModified] = Now
[UserModified] = GlobalUserID
End If

End Sub

Thanks
Doug