Bookmarks and recordsets - Dazed and confused

P

PAC

I'm just lost on this subject and the brain refuses to absorb the basics here.

I have a listbox on a form. This listbox is driven by a query that shows
Id's, usernames, etc. That is working just fine. On the same form just above
this box are the various fields plus a few that the user can change existing
data, modify data, etc. that is shown in the listbox. The listbox is used as
a basic navigation aid.

What I am trying to do is let the user doubleclick on a record in this
listbox and then the user will be taken to that specific record on the form.
This works fine as the form opens intially. I can click on any record on the
list and it goes to the indicated record. The problem starts when I wish to
ADD a new record to the underlying table. A button to add the record opens a
small form, gives the user a choice of 2 types and upon selecting one of the
two, returns them to the prior form. The ADD form has the following code;

Set rs = Curdb.OpenRecordset("tblTweakDwgs", dbOpenDynaset)

With rs
.AddNew
!GeoRefType = strGeoRefType
.Update
.Bookmark = .LastModified
End With

Upon closing this form and returning to the prior one, the listbox does
reflect the newly added record. However, when I doubleclick on the one just
below this one, it tries to go to the first record in the table. The listbox
is driven as such

Set rs = Me.RecordsetClone
intDocID = lstTweak.Column(0)

rs.FindFirst "[Tweak_ID] = " & intDocID

If rs.NoMatch Then
MsgBox "No doc id", vbInformation
Else
Me.Bookmark = rs.Bookmark
End If

rs.Close


What I want to do is
1. Add the new record,
2. Upon returning to the form, goto that newly added record so the user can
immediately enter the data.

I've looked here and tried to use various examples here.....but I'm just
missing the boat here. Can someone please hold my hand and walk me through
some basics?

Thanks,
PC
 
S

Sergey Poberezovskiy

As far as I understand the sequence of the events should
be something similar to the following:
- create a new record and get the new ID:

With rs
.AddNew
!GeoRefType = strGeoRefType
.Update
IDtoPassBack = .LastModified
End With
- refresh the underlying form's (not Add form) source:
Me.Requery
- locate the new record in the RecordsetClone the same
way you did:

With RecordsetClone
.FindFirst "[Tweak_ID] = " & IDtoPassBack
If Not .NoMatch Then
Me.Bookmark = .Bookmark
End If
End With

HTH
 
J

John Vinson

As far as I understand the sequence of the events should
be something similar to the following:
- create a new record and get the new ID:

With rs
.AddNew
!GeoRefType = strGeoRefType
.Update
IDtoPassBack = .LastModified
End With
- refresh the underlying form's (not Add form) source:
Me.Requery
- locate the new record in the RecordsetClone the same
way you did:

With RecordsetClone
.FindFirst "[Tweak_ID] = " & IDtoPassBack
If Not .NoMatch Then
Me.Bookmark = .Bookmark
End If
End With
Click to expand...

Um? I think the .lastmodified property is a Bookmark, not the value of
any field in the table. I'd try

Dim IDToPassBack As Variant
....
IDToPassBack = .LastModified
....
Me.Bookmark = IDToPassBack

John W. Vinson[MVP]
 
Top