REPOST - How to Lookup Current Record Field Value (that is not displayed)

  • Thread starter Thread starter John C.
  • Start date Start date
J

John C.

I have a form which allows the user to add new records (by
use of the navigation button).

The form record source is a query which sorts the data by
last name, ascending.

When adding a new record, I want the form to retain proper
sort order. I have noticed that the added record remains
as the last record until the form is closed and re-opened.

I have tried .refresh, but that does not effect the sort
order.

I am trying to use .requery, which does place everything
in proper sort order, but will return to the first record
unless I 'trap' the current records [autonumm] value.
Then use this as criteria to return to.

Here's what I have, I need help determining the current
records [AutoNum] field value that is not displayed on the
form.

Private Sub Form_BeforeUpdate(Cancel As Integer)
'Test to see if record being updated is a new record
varNewRecord = Me.NewRecord
If varNewRecord = True Then
varAutoNum = ***not sure how to lookup***
End If
End Sub

Private Sub Form_AfterUpdate()
'If record being updated was a new record
If varNewRecord = True Then
Me.Requery
With Me.RecordsetClone
.MoveFirst
.FindFirst "[AutoNum] = '" & varAutoNum & "'"
Me.Recordset.Bookmark = .Bookmark
End With
End If
End Sub
 
I created a 'save' button on my form and added some code
to the after_update event...something like this:

Dim intID As Long
Dim rs As Object

Me.Refresh

intID = Me.AutoNum_ID

Set rs = Me.RecordsetClone

Me.Requery

rs.FindFirst "[AutoNum_ID]=" & intID


Me.Bookmark = rs.Bookmark

Also add the Autonumber field to your form and change
the 'Visible' property to 'No'.
 
Thankyou...you put me on track again...here's what I ended
up using.

I placed a hidden text box on the form, linked to my
AutoNumber field (txtAutoNum).

Private Sub Form_BeforeUpdate(Cancel As Integer)
'Test to see if record being updated is a new record
If Me.NewRecord = True Then
varBookmark = Me.txtAutoNum
varNewRecord = True
End If
End Sub

Private Sub Form_AfterUpdate()
'If record being updated was a new record
If varNewRecord = True Then
Me.Requery
With Me.RecordsetClone
.MoveFirst
.FindFirst "[AutoNum] = " & varBookmark
Me.Recordset.Bookmark = .Bookmark
End With
End If
End Sub
 
Back
Top