Go through a form's recordset without affecting the form

[bentheimmigrant]

I've got a continuous form, on which I need to use an OnCurrent event to check that the first record has a certain value.
So I have an OnCurrent sub like so:

Private Sub Form_Current()
Dim FormRst As Recordset
FormRst.MoveFirst
' Code to check that the first item is as expected

However, the MoveFirst of course means that every time I click on a subsequent record, I'm immediately returned to the first record.

If I remove the OnCurrent call, the queries I need to run to keep the form up to date won't run.

Is there some way to copy a recordset as a readonly object, so I can't affect the form?

 

[Minty]

Google recordset clone

 

[The_Doc_Man]

Set NewRecSet = Me.Recordset.Clone

Then you can step through the cloned recordset without affecting the bound recordset of the form. However, if the form was open with the ability to update, the new recordset is a clone of that bound recordset including its mode of opening. Therefore, be careful with it. As long as you don't do an action other than "Movexxx" or "Findxxx" then you won't have a problem.

NOTE: You can skin this cat another way if and only if the form's .RecordSource is a named query and not just an arbitrary SQL string, because then you could also do

Set NewRecSet = CurrentDB.OpenRecordset( Me.Recordsource, dbOpenTable, dbReadOnly, dbOptimistic)

I'm shooting from the hip so double-check the arguments if you want to do the OpenRecordset method in order to assure that the recordset is read-only. Personally, I would just use the clone and remember not to do .AddNew, .Edit, .Delete, etc.

 

[CJ_London]

sure

dim rst as dao.recordset
set rst=me.recordset


there is also the recordsetclone, though not sure if this is suitable for your needs

but seems a strange requirement - if you go to the first record and change it will be updated when you move to another record.

so why not just use dlookup instead?

 

[bentheimmigrant]

RecordsetClone seems to have done the job perfectly.

Thanks all.