Recordset clones without a form?

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

KarenH - 08 Mar 2006 17:11 GMT

I am totally new to using a recordset clone, and perhaps this is not the
right application of it . . .

I have an application whose only form will contain a command button to start
the action. What it needs to do is to take a table and record-by-record,
check the rest of the records to see if two of the fields (AssetCode and
LocCode) are the same as the current record. If it finds such a record, it
needs to add the value of another field in the record, the Amount.

The only way I could think to do this was with a clone of the recordset,
because I somehow need to set a bookmark to the current record while it is
comparing the rest of the records. However, that isn't working at all, and
I'm guessing it is because I do not have a form containing the recordset, all
this is in a module. Specifically when I try and set the recordset clone,
the help file shows the syntax as "Set rstClone = me.recordsetclone" -- and
without a form, it doesn't know what "Me" is and doesn't like recordsetclone.

The app needs no form, it is not returning any records to be displayed --
instead it is saving them to tables which are then exported out to Excel
spreadsheets.

Will a recordsetclone not work without a form containing the recordset? Is
there a way of doing this that I'm not seeing, a way of setting a bookmark to
the current record while comparing the other records?

TIA for the help. I really don't know what specific group to put this in,
none of them seem to fit, but it looks like most of the "recordsetclone"
postings have been in this one. So if this seems to be the wrong group, I
apologize and would appreciate direction as to which one would be more
appropriate.

Reply to this Message

Sandra Daigle - 08 Mar 2006 17:36 GMT

You need the Clone of a recordset. You can open a recordset based on a
table or query - the recordset does not have to be bound to a form. Then you
can get a clone of the recordset which is what you want. Here is some basic
code for opening and looping through a recordset -

Public Sub WalkRecordset()
Dim rst As DAO.Recordset
Dim db As DAO.Database
Set db = CurrentDb()
'Open the recordset (once)
'the name of a saved query could be used in place
'of the SQL Select statement
Set rst = db.OpenRecordset("Select * from Customers")
With rst
' Test for records (EOF and BOF are both true when empty),
If Not (.EOF And .BOF) Then
' Loop through stopping when the EOF condition is reached.
Do Until .EOF
' Inside the loop we do something with each record,
Debug.Print "Customerid:" & .Fields("Customerid")
' Move to the next record
.MoveNext
Loop
End If
' Close the recordset
.Close
End With
'Destroy the object variables.
Set rst = Nothing
Set db = Nothing
End Sub

To get the clone of a recordset you would do the following:

dim rstNew as dao.recordset
Set rstNew = rst.Clone

Now using rstNew you can search for the matching conditions. I hope this
helps you get started -

Signature

Sandra Daigle [Microsoft Access MVP]
Please post all replies to the newsgroup.

> I am totally new to using a recordset clone, and perhaps this is not
> the right application of it . . .
[quoted text clipped - 29 lines]
> be the wrong group, I apologize and would appreciate direction as to
> which one would be more appropriate.

Reply to this Message

KarenH - 08 Mar 2006 18:09 GMT

I'm actually doing this in ADO (some previous coding in there is in ADO).

Since posting this, I moved the code from the module to behind the form, and
it now accepts me.recordsetclone. Everything is fine until it gets to the
search. The help file said use rstClone.FindFirst -- but that errors, comes
back with "Method or Data Member Not found." So I put rst.Find and that, it
will accept. But then when it hits "NoMatch", I again get "Method or Data
Member Not Found". The code I'm doing is below (the values for strAssetID
and strLocationID have been previously set)

Set rstClone = Me.RecordsetClone
strCriteria = "[Asset ID] like '" & strAssetID & "' AND [LocID] like '" &
strLocationID & "''"

Do while not rstClone.eof
rstClone.Find strCriteria, , adSearchForward
If rstClone.nomatch Then
Exit Do
Else
Me.Bookmark = rstClone.Bookmark
End If
rstClone.MoveNext
Loop

> You need the Clone of a recordset. You can open a recordset based on a
> table or query - the recordset does not have to be bound to a form. Then you
[quoted text clipped - 69 lines]
> > be the wrong group, I apologize and would appreciate direction as to
> > which one would be more appropriate.

Reply to this Message

Sandra Daigle - 08 Mar 2006 22:24 GMT

Unfortunately, I'm not that well versed in ADO - I've always stuck with DAO.
Having said that - I'm pretty sure that using ADO, if the record is not
found, EOF will be reached. There is no NoMatch property.

Signature

Sandra Daigle [Microsoft Access MVP]
Please post all replies to the newsgroup.

> I'm actually doing this in ADO (some previous coding in there is in
> ADO).
[quoted text clipped - 101 lines]
>>> to be the wrong group, I apologize and would appreciate direction
>>> as to which one would be more appropriate.

Reply to this Message