Help with RecordSetClone

  • Thread starter Thread starter Lloyd
  • Start date Start date
L

Lloyd

Hello,

this is my first attempt at using recordsetclone to look at the same field
on a continuous form and update the field. I have looked at other examples
on this forum and put together the below code but I keep getting an "invalid
argument 3001 error" when it reaches the first .FindNext.

With Me!subfrmPersons.Form.RecordsetClone

.Bookmark = Me!subfrmPersons.Form.Bookmark
.FindNext Me!subfrmPersons.Form![DOB]
MsgBox Me!subfrmPersons.Form![DOB] 'for testing only
If Not IsNull(Me!subfrmPersons.Form![DOB]) Then Me!subfrmPersons.Form!
[Age] = DateDiff("yyyy", Me!subfrmPersons.Form![DOB],
[Forms]![frmMainEntry]![DateOccd]) - IIf(Format(Me!subfrmPersons.Form![DOB],
"mmdd") > _
Format([Forms]![frmMainEntry]![DateOccd], "mmdd"), 1, 0)


If .NoMatch Then
.FindFirst Me!subfrmPersons.Form![DOB]
If Not IsNull(Me!subfrmPersons.Form![DOB]) Then Me!subfrmPersons.Form![Age]
= DateDiff("yyyy", Me!subfrmPersons.Form![DOB],
[Forms]![frmMainEntry]![DateOccd]) - IIf(Format(Me!subfrmPersons.Form![DOB],
"mmdd") > _
Format([Forms]![frmMainEntry]![DateOccd], "mmdd"), 1, 0)

End If

If .NoMatch Then
MsgBox "No additional DOB's Found"
Else
Me.Bookmark = .Bookmark
End If

End With
 
Lloyd said:
Hello,

this is my first attempt at using recordsetclone to look at the same field
on a continuous form and update the field. I have looked at other
examples
on this forum and put together the below code but I keep getting an
"invalid
argument 3001 error" when it reaches the first .FindNext.

With Me!subfrmPersons.Form.RecordsetClone

.Bookmark = Me!subfrmPersons.Form.Bookmark
.FindNext Me!subfrmPersons.Form![DOB]
MsgBox Me!subfrmPersons.Form![DOB] 'for testing only
If Not IsNull(Me!subfrmPersons.Form![DOB]) Then Me!subfrmPersons.Form!
[Age] = DateDiff("yyyy", Me!subfrmPersons.Form![DOB],
[Forms]![frmMainEntry]![DateOccd]) -
IIf(Format(Me!subfrmPersons.Form![DOB],
"mmdd") > _
Format([Forms]![frmMainEntry]![DateOccd], "mmdd"), 1, 0)


If .NoMatch Then
.FindFirst Me!subfrmPersons.Form![DOB]
If Not IsNull(Me!subfrmPersons.Form![DOB]) Then
Me!subfrmPersons.Form![Age]
= DateDiff("yyyy", Me!subfrmPersons.Form![DOB],
[Forms]![frmMainEntry]![DateOccd]) -
IIf(Format(Me!subfrmPersons.Form![DOB],
"mmdd") > _
Format([Forms]![frmMainEntry]![DateOccd], "mmdd"), 1, 0)

End If

If .NoMatch Then
MsgBox "No additional DOB's Found"
Else
Me.Bookmark = .Bookmark
End If

End With
Click to expand...


Your argument to .FindNext needs to specify a complete conditional
expression that can be evaluated as True or False. For example,

.FindNext "DOB = " & _
Format(Me!subfrmPersons.Form![DOB], "\#mm\/dd\/yyyy\#")
 
Dirk Goldgar said:
Lloyd said:
Hello,

this is my first attempt at using recordsetclone to look at the same field
on a continuous form and update the field. I have looked at other
examples
on this forum and put together the below code but I keep getting an
"invalid
argument 3001 error" when it reaches the first .FindNext.

With Me!subfrmPersons.Form.RecordsetClone

.Bookmark = Me!subfrmPersons.Form.Bookmark
.FindNext Me!subfrmPersons.Form![DOB]
MsgBox Me!subfrmPersons.Form![DOB] 'for testing only
If Not IsNull(Me!subfrmPersons.Form![DOB]) Then Me!subfrmPersons.Form!
[Age] = DateDiff("yyyy", Me!subfrmPersons.Form![DOB],
[Forms]![frmMainEntry]![DateOccd]) -
IIf(Format(Me!subfrmPersons.Form![DOB],
"mmdd") > _
Format([Forms]![frmMainEntry]![DateOccd], "mmdd"), 1, 0)


If .NoMatch Then
.FindFirst Me!subfrmPersons.Form![DOB]
If Not IsNull(Me!subfrmPersons.Form![DOB]) Then
Me!subfrmPersons.Form![Age]
= DateDiff("yyyy", Me!subfrmPersons.Form![DOB],
[Forms]![frmMainEntry]![DateOccd]) -
IIf(Format(Me!subfrmPersons.Form![DOB],
"mmdd") > _
Format([Forms]![frmMainEntry]![DateOccd], "mmdd"), 1, 0)

End If

If .NoMatch Then
MsgBox "No additional DOB's Found"
Else
Me.Bookmark = .Bookmark
End If

End With
Click to expand...


Your argument to .FindNext needs to specify a complete conditional
expression that can be evaluated as True or False. For example,

.FindNext "DOB = " & _
Format(Me!subfrmPersons.Form![DOB], "\#mm\/dd\/yyyy\#")



--
Dirk Goldgar, MS Access MVP
Access tips: www.datagnostics.com/tips.html

(please reply to the newsgroup)
Click to expand...
Dirk,

thank you, that did it, but I have one more quesiton if you can guide me.
when I run the code now, it finds the first DOB field, but it doesnt find the
next one on the next record on the form. Am I missing something? I have
posted code thats a bit more cleaned up.

With Me!subfrmPersons.Form.RecordsetClone

.Bookmark = Me!subfrmPersons.Form.Bookmark
.FindNext "DOB = " & Format(Me!subfrmPersons.Form![DOB],
"\#mm\/dd\/yyyy\#")
MsgBox Me!subfrmPersons.Form![DOB] 'for testing only

If .NoMatch Then
.FindFirst "DOB = " & Format(Me!subfrmPersons.Form![DOB],
"\#mm\/dd\/yyyy\#")
MsgBox Me!subfrmPersons.Form![DOB] 'for testing only
End If

If .NoMatch Then
MsgBox "No additional DOB's Found"
Else
Me!subfrmPersons.Form.Bookmark = .Bookmark
End If

End With
 
Lloyd said:
thank you, that did it, but I have one more quesiton if you can guide me.
when I run the code now, it finds the first DOB field, but it doesnt find
the
next one on the next record on the form. Am I missing something? I have
posted code thats a bit more cleaned up.

With Me!subfrmPersons.Form.RecordsetClone

.Bookmark = Me!subfrmPersons.Form.Bookmark
.FindNext "DOB = " & Format(Me!subfrmPersons.Form![DOB],
"\#mm\/dd\/yyyy\#")
MsgBox Me!subfrmPersons.Form![DOB] 'for testing only

If .NoMatch Then
.FindFirst "DOB = " & Format(Me!subfrmPersons.Form![DOB],
"\#mm\/dd\/yyyy\#")
MsgBox Me!subfrmPersons.Form![DOB] 'for testing only
End If

If .NoMatch Then
MsgBox "No additional DOB's Found"
Else
Me!subfrmPersons.Form.Bookmark = .Bookmark
End If

End With
Click to expand...


There's no looping in the code you posted, so it's naturally going to stop
after the first iteration. You would have to either (a) start with a
..FindFirst and continue with .FindNext until the .NoMatch condition is True,
or else (b) start at the current record, loop forward with .FindNext until
..NoMatch, then return to the start of the recordset with .FindFirst, then
continue with .FindNext until you come to the current record again. The
former is certainly simpler and easier to code.

But what exactly is it that you are trying to do? While recordsets are very
useful, most operations that loop through recordsets to update a field in
each record can be implemented more efficiently as a single update query.
Furthermore, the code you originally posted seems to be intended to update
an Age field based on DOB, and age is a concept that is usually better
represented as a calculated field, rather than a stored one, so that it is
always correct and needs no special update process.
 
Dirk Goldgar said:
Lloyd said:
thank you, that did it, but I have one more quesiton if you can guide me.
when I run the code now, it finds the first DOB field, but it doesnt find
the
next one on the next record on the form. Am I missing something? I have
posted code thats a bit more cleaned up.

With Me!subfrmPersons.Form.RecordsetClone

.Bookmark = Me!subfrmPersons.Form.Bookmark
.FindNext "DOB = " & Format(Me!subfrmPersons.Form![DOB],
"\#mm\/dd\/yyyy\#")
MsgBox Me!subfrmPersons.Form![DOB] 'for testing only

If .NoMatch Then
.FindFirst "DOB = " & Format(Me!subfrmPersons.Form![DOB],
"\#mm\/dd\/yyyy\#")
MsgBox Me!subfrmPersons.Form![DOB] 'for testing only
End If

If .NoMatch Then
MsgBox "No additional DOB's Found"
Else
Me!subfrmPersons.Form.Bookmark = .Bookmark
End If

End With
Click to expand...


There's no looping in the code you posted, so it's naturally going to stop
after the first iteration. You would have to either (a) start with a
.FindFirst and continue with .FindNext until the .NoMatch condition is True,
or else (b) start at the current record, loop forward with .FindNext until
.NoMatch, then return to the start of the recordset with .FindFirst, then
continue with .FindNext until you come to the current record again. The
former is certainly simpler and easier to code.

But what exactly is it that you are trying to do? While recordsets are very
useful, most operations that loop through recordsets to update a field in
each record can be implemented more efficiently as a single update query.
Furthermore, the code you originally posted seems to be intended to update
an Age field based on DOB, and age is a concept that is usually better
represented as a calculated field, rather than a stored one, so that it is
always correct and needs no special update process.

--
Dirk Goldgar, MS Access MVP
Access tips: www.datagnostics.com/tips.html

(please reply to the newsgroup)
Click to expand...
Dirk,

Thanks agian for your quick response. I would agree that age is better
calculated instead of stored, but in this case we need to store the age field
as we need the age at the time of the incident and not the current age. I
think you are correct and that I need to look at some type of query instead
of looing through the records. I found some other code on how to move to the
next record, but I find its not working, because there are required fields in
the record that have not been completed which is why it wont move to the next
record. So I need to rethink how I'm approaching this and look at an update
query instead of going through the form.

thanks again for your help and suggestions.
 
Lloyd said:
I would agree that age is better
calculated instead of stored, but in this case we need to store the age
field
as we need the age at the time of the incident and not the current age.
Click to expand...

And do you not have the time of the incident stored?
I
think you are correct and that I need to look at some type of query
instead
of looing through the records. I found some other code on how to move to
the
next record, but I find its not working, because there are required fields
in
the record that have not been completed which is why it wont move to the
next
record. So I need to rethink how I'm approaching this and look at an
update
query instead of going through the form.
Click to expand...

That may be best, if you really need to store the age, but be aware that if
you have a dirty record in the form, when that record is saved you will
inevitably overwrite whatever version of that same record was previously
stored in the table. I don't know enough about what you're trying to do to
give more detailed advice.
thanks again for your help and suggestions.
Click to expand...

You're welcome.
 
If you need the age at the time of the incident, it can be calculated from
the DOB and the date of the incident it can still be a calculated field in a
query



Lloyd said:
Dirk Goldgar said:
Lloyd said:
thank you, that did it, but I have one more quesiton if you can guide
me.
when I run the code now, it finds the first DOB field, but it doesnt
find
the
next one on the next record on the form. Am I missing something? I
have
posted code thats a bit more cleaned up.

With Me!subfrmPersons.Form.RecordsetClone

.Bookmark = Me!subfrmPersons.Form.Bookmark
.FindNext "DOB = " & Format(Me!subfrmPersons.Form![DOB],
"\#mm\/dd\/yyyy\#")
MsgBox Me!subfrmPersons.Form![DOB] 'for testing only

If .NoMatch Then
.FindFirst "DOB = " & Format(Me!subfrmPersons.Form![DOB],
"\#mm\/dd\/yyyy\#")
MsgBox Me!subfrmPersons.Form![DOB] 'for testing only
End If

If .NoMatch Then
MsgBox "No additional DOB's Found"
Else
Me!subfrmPersons.Form.Bookmark = .Bookmark
End If

End With
Click to expand...


There's no looping in the code you posted, so it's naturally going to
stop
after the first iteration. You would have to either (a) start with a
.FindFirst and continue with .FindNext until the .NoMatch condition is
True,
or else (b) start at the current record, loop forward with .FindNext
until
.NoMatch, then return to the start of the recordset with .FindFirst, then
continue with .FindNext until you come to the current record again. The
former is certainly simpler and easier to code.

But what exactly is it that you are trying to do? While recordsets are
very
useful, most operations that loop through recordsets to update a field in
each record can be implemented more efficiently as a single update query.
Furthermore, the code you originally posted seems to be intended to
update
an Age field based on DOB, and age is a concept that is usually better
represented as a calculated field, rather than a stored one, so that it
is
always correct and needs no special update process.

--
Dirk Goldgar, MS Access MVP
Access tips: www.datagnostics.com/tips.html

(please reply to the newsgroup)
Click to expand...
Dirk,

Thanks agian for your quick response. I would agree that age is better
calculated instead of stored, but in this case we need to store the age
field
as we need the age at the time of the incident and not the current age. I
think you are correct and that I need to look at some type of query
instead
of looing through the records. I found some other code on how to move to
the
next record, but I find its not working, because there are required fields
in
the record that have not been completed which is why it wont move to the
next
record. So I need to rethink how I'm approaching this and look at an
update
query instead of going through the form.

thanks again for your help and suggestions.
Click to expand...
 
Back
Top