Return to record after requery

[Josh]

I requery a form that has two tables connected with a LEFT
JOIN in order to get access to the fields in both tables
for editing.

The backend is SQL Server 2k and I programmatically
generate a record in the 2nd table when conditions warrant
after inserting a new record into the 1st table.

After requery, I need to return to the record being
edited. The bookmark method does not work after requery.
Another suggestion was to store the recordID in a separate
table prior to requery. That seems awkward. Is that my
best option?

Thanks so much for your help.
Josh

 

[PC Datasheet]

You don't need to store the recordID in a separate table, just store it in a
dimmed variable.

 

[Josh]

I'm running into a problem picking up the recordID. If I
refresh after entering a new record on table1, SQL
apparently doesn't return the recordID back to Access. I
get an error message.

 

[PC Datasheet]

You need something like this:

Dim Rst As DAO.Recordset
Dim CaptureRecordID As Long
Set Rst = Me.RecordsetClone
<<Do Something>>
CaptureRecordID = Me!RecordID
Me.Requery
Rst.FindFirst "[RecordID] = " & CaptureRecordID
Me.BookMark = Rst.BookMark


--
PC Datasheet
Your Resource For Help With Access, Excel And Word Applications
(e-mail address removed)
www.pcdatasheet.com

 

[Josh]

Thanks so much!
I had to put

Me.refresh

before CaptureRecordID=me.RecordID

but that got it!

-----Original Message-----
You need something like this:

Dim Rst As DAO.Recordset
Dim CaptureRecordID As Long
Set Rst = Me.RecordsetClone
<<Do Something>>
CaptureRecordID = Me!RecordID
Me.Requery
Rst.FindFirst "[RecordID] = " & CaptureRecordID
Me.BookMark = Rst.BookMark


--
PC Datasheet
Your Resource For Help With Access, Excel And Word Applications
(e-mail address removed)
www.pcdatasheet.com

I'm running into a problem picking up the recordID. If I
refresh after entering a new record on table1, SQL
apparently doesn't return the recordID back to Access. I
get an error message.

table,
just store it in a

.