Recordset clones without a form?

[Guest]

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.

 

[Sandra Daigle]

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 -

 

[Guest]

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
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 -

--
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 . . .

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.

 

[Sandra Daigle]

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.



--
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).

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 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 -

--
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 . . .

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.