Add record race condition

[rafe]

Hi all,

I've got a race(?) condition for recently added records to a table. Basically, when I add or modify a record in a table and then immediately access the updated record the DB doesn't seem send to the form the recent changes. For example, I'm using an auto number field as a primary key in a table and as a reference# in my log book. I...

Particulars:

1) Dims & opens
Dim rstCash As ADODB.Recordset
Private Sub Form_Load()
Set rstCash = New ADODB.Recordset
rstCash.Open "CashBox", CurrentProject.Connection, adOpenKeyset, adLockOptimistic
End Sub

2) Add a record doesn't seem to always update the form in time...
Private Sub cmdOK_Click()
rstCash.AddNew
rstCash!ApprovedBy = Me.ApprovedBy
rstCash!AdvanceDate = date
rstCash!AdvancePayeeID = Me.AdvancePayeeID
rstCash!AdvanceAmount = Me.AdvanceAmount
rstCash!Index = Me.Index
rstCash!Account = Me.Account
rstCash!Description = Me.Description
rstCash.Update
rstCash.Requery
Me.CashID = rstCash!CashID 'the auto# field & key
MsgBox "The 'Reference Number' is: " & Me.CashID
!!!!!!!! Sometimes Reference # is wrong = 1!!!!

3)If I print after the update some times I just get "Error" in all of the fields
rstCash.MoveFirst
rstCash.Find "[CashID] = " & Me.CashID
rstCash!ApprovedBy = Me.ApprovedBy
rstCash!ReceiptDate = date
rstCash!ReceiptPayeeID = Me.ReceiptPayeeID
rstCash!ReceiptAmount = Me.ReceiptAmount
rstCash!AdvanceChange = Me.AdvanceChange
rstCash!MissingAmount = Me.MissingAmount
rstCash!Description = Me.Description
rstCash.Update
rstCash.Requery
Me.cmdPrint.Enabled = True
Me.cmdPrint.SetFocus
Me.transList.Requery

Private Sub cmdPrint_Click()
DoCmd.OpenReport "CashBoxTrans", , , "[CashID] = Forms![CashBoxTransaction]![CashID]"
DoCmd.Close acReport, "CashBoxTrans"

This is abrreviated code... Oh yea...Access 2K using ADOB.RecordSet(s)

Thanks rafe [sig][/sig]

 

[rafe]

Never mind! Brain fade.

*.Requery = *.Close + *.Open... which will, of course, reset my pointer!

The...
DoCmd.Close acReport, "CashBoxTrans"
command is superfulous & is the likely culprit on the other problem.

Sorry to clutter the boards.

rafe