DAO.Recordset (instead of "Me.Requery)

  • Thread starter Thread starter Tom
  • Start date Start date
T

Tom

Instead of using the "Me.Requery" on the form, I use the
function below (****) for requerying the form.

This will allow me to "stay" on the current record (after
requerying) instead of "jumping" back to the first record.

I now realize, however, that the function throws an error
if the table is completely empty and I requery the first
record.

The error is: "Update or CancelUpdate without AddNew or
Edit" (error 3020).

Now, in the form's OnError event, I have put the
following:

If DataErr = 3020 Then
Response = acDataErrContinue
End If

.... however, I now cannot change any other field values
in the form anymore.

Does anyone know how to either modify the function below
or simply ignore the error message and allowing me to
continue to make changes to the 1st record?

Thanks in advance,
Tom


&&&&&&&&&&&&&&&&&&&&&&&&&

Object (DAO) 3.6 Library in the References Collection:

****
Dim rs As DAO.Recordset
Dim lngID As Long

With Me
lngID = .AutoNumberFieldName
.Requery
DoEvents
Set rs = .RecordsetClone
rs.FindFirst "[AutoNumberFieldName] = " & lngID
If rs.NoMatch = False Then
.Bookmark = rs.Bookmark
End If
End With
****
 
Hi Tom

I'm not sure how you're getting error 3020. I would have expected a
"Invalid use of Null" error on the line:
lngID = .AutoNumberFieldName

However, the simplest solution would be not to requery if there are no
records:
If Me.RecordsetClone.RecordCount = 0 then Exit Sub

--
Good Luck!
Graham Mandeno [Access MVP]
Auckland, New Zealand

Return mail address is invalid in a vain attempt to reduce spam.
Feedback is welcome at: (e-mail address removed)
Please post new questions or followups to newsgroup.
 
Graham:

I like this approach... simple and straight-forward.

I now bypass the initial 3020 error; however, once the
first record is created, the screen/record freezes when I
run another function that allows me to
update/insert "current values" into a "history table".

This is how I modified the DAO function as you suggested
(I did not use the Exit sub though since other steps have
to be performed before I should exit out).

With the information given, do you have any idea why the
record still freezes at times. Or is the "Do Nothing"
approach not the best one to use?

Thanks in advance,
Tom



&&&&&&&&&&&&&&


If Me.RecordsetClone.RecordCount = 0 Then
'Do Nothing

Else

Dim rs As DAO.Recordset
Dim lngID As Long

With Me
lngID = .CorrespondenceID 'ensure to
synchronize naming convention of PK below []
.Requery
DoEvents

Set rs = .RecordsetClone
rs.FindFirst "[CorrespondenceID] = " &
lngID

If rs.NoMatch = False Then
.Bookmark = rs.Bookmark

End If
End With

End If

'****
-----Original Message-----
Hi Tom

I'm not sure how you're getting error 3020. I would have expected a
"Invalid use of Null" error on the line:
lngID = .AutoNumberFieldName

However, the simplest solution would be not to requery if there are no
records:
If Me.RecordsetClone.RecordCount = 0 then Exit Sub

--
Good Luck!
Graham Mandeno [Access MVP]
Auckland, New Zealand

Return mail address is invalid in a vain attempt to reduce spam.
Feedback is welcome at: (e-mail address removed)
Please post new questions or followups to newsgroup.

Tom said:
Instead of using the "Me.Requery" on the form, I use the
function below (****) for requerying the form.

This will allow me to "stay" on the current record (after
requerying) instead of "jumping" back to the first record.

I now realize, however, that the function throws an error
if the table is completely empty and I requery the first
record.

The error is: "Update or CancelUpdate without AddNew or
Edit" (error 3020).

Now, in the form's OnError event, I have put the
following:

If DataErr = 3020 Then
Response = acDataErrContinue
End If

... however, I now cannot change any other field values
in the form anymore.

Does anyone know how to either modify the function below
or simply ignore the error message and allowing me to
continue to make changes to the 1st record?

Thanks in advance,
Tom


&&&&&&&&&&&&&&&&&&&&&&&&&

Object (DAO) 3.6 Library in the References Collection:

****
Dim rs As DAO.Recordset
Dim lngID As Long

With Me
lngID = .AutoNumberFieldName
.Requery
DoEvents
Set rs = .RecordsetClone
rs.FindFirst "[AutoNumberFieldName] = " & lngID
If rs.NoMatch = False Then
.Bookmark = rs.Bookmark
End If
End With
****
Click to expand...


.
Click to expand...
 
Thanks for your help on this, however, I still have some
remaining problems with the function. I truly appreciate
it
if you could provide me some additional pointers. Here's
what happens...

The function below (between the ******s) throws the
following error:
"Object variable or With block variable not set (91)".

Here's what I trying to achieve:
- Until I added the function DAO.Recordset, the INSERT was
working just fine.
- In order to view the changes immediately (after the
INSERT was executed) in the
tblLocationHistory_subform, I used the "Me.Requery".
- The Me.Requery, however, forced me to "jump" back to the
1st record (instead of remaining on the current record)
- I then included the "RecordClone" function (between the
*****s). This initially seemed to work fine, but I not
realized that the "record" freezes" - particularly when
making on the 1st record.

Again, when I now change the "AsOfDate" and then exit the
field, the error listed above "Object variable..." is
thrown.

How do I need to modify the DAO.Recordset query, so that I
can do the following:
1. Use the Me.Requery when on the 1st record
2. Use the DAO.Record function when on any other record
but the 1st record


Thanks in advance,
Tom




&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&

Private Sub AsOfDate_Exit(Cancel As Integer)
On Error GoTo Err_Location_Exit

strSQL = "Insert into tblLocationHistory
(CorrespondenceID, Disposition, Location, AsOfDate) " &
vbCrLf & _
"VALUES ('" & Format(Me!
CorrespondenceID.Value, "00000") & "', '" & Me!
Disposition.Value & "', '" & Me!Location.Value & "', #" &
Format(AsOfDate) & "#)"

MsgBox strSQL, , "Location history table will be
updated!"

CurrentDb().Execute strSQL, dbFailOnError



'******
Dim rs As DAO.Recordset

If rs.RecordCount = 1 Then

Me.Requery

Else

Dim lngID As Long

With Me
lngID = .CorrespondenceID
.Requery
DoEvents

Set rs = .RecordsetClone
rs.FindFirst "[CorrespondenceID] = " &
lngID

If rs.NoMatch = False Then
.Bookmark = rs.Bookmark

End If
End With

End If
'******



End_Location_Exit:
Exit Sub

Err_Location_Exit:
MsgBox Err.Description & " (" & Err.Number & ")", _
vbOKOnly + vbCritical
Resume End_Location_Exit

End Sub



&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&



-----Original Message-----
Graham:

I like this approach... simple and straight-forward.

I now bypass the initial 3020 error; however, once the
first record is created, the screen/record freezes when I
run another function that allows me to
update/insert "current values" into a "history table".

This is how I modified the DAO function as you suggested
(I did not use the Exit sub though since other steps have
to be performed before I should exit out).

With the information given, do you have any idea why the
record still freezes at times. Or is the "Do Nothing"
approach not the best one to use?

Thanks in advance,
Tom



&&&&&&&&&&&&&&


If Me.RecordsetClone.RecordCount = 0 Then
'Do Nothing

Else

Dim rs As DAO.Recordset
Dim lngID As Long

With Me
lngID = .CorrespondenceID 'ensure to
synchronize naming convention of PK below []
.Requery
DoEvents

Set rs = .RecordsetClone
rs.FindFirst "[CorrespondenceID] = " &
lngID

If rs.NoMatch = False Then
.Bookmark = rs.Bookmark

End If
End With

End If

'****
-----Original Message-----
Hi Tom

I'm not sure how you're getting error 3020. I would have expected a
"Invalid use of Null" error on the line:
lngID = .AutoNumberFieldName

However, the simplest solution would be not to requery if there are no
records:
If Me.RecordsetClone.RecordCount = 0 then Exit Sub

--
Good Luck!
Graham Mandeno [Access MVP]
Auckland, New Zealand

Return mail address is invalid in a vain attempt to reduce spam.
Feedback is welcome at: (e-mail address removed)
Please post new questions or followups to newsgroup.

Tom said:
Instead of using the "Me.Requery" on the form, I use the
function below (****) for requerying the form.

This will allow me to "stay" on the current record (after
requerying) instead of "jumping" back to the first record.

I now realize, however, that the function throws an error
if the table is completely empty and I requery the first
record.

The error is: "Update or CancelUpdate without AddNew or
Edit" (error 3020).

Now, in the form's OnError event, I have put the
following:

If DataErr = 3020 Then
Response = acDataErrContinue
End If

... however, I now cannot change any other field values
in the form anymore.

Does anyone know how to either modify the function below
or simply ignore the error message and allowing me to
continue to make changes to the 1st record?

Thanks in advance,
Tom


&&&&&&&&&&&&&&&&&&&&&&&&&

Object (DAO) 3.6 Library in the References Collection:

****
Dim rs As DAO.Recordset
Dim lngID As Long

With Me
lngID = .AutoNumberFieldName
.Requery
DoEvents
Set rs = .RecordsetClone
rs.FindFirst "[AutoNumberFieldName] = " & lngID
If rs.NoMatch = False Then
.Bookmark = rs.Bookmark
End If
End With
****
Click to expand...


.
Click to expand...
.
Click to expand...
 
You haven't created the recordset that you're trying to use before you have
the statement

If rs.RecordCount = 1 Then

What recordset's count are you trying to check?

--
Doug Steele, Microsoft Access MVP



Tom said:
Thanks for your help on this, however, I still have some
remaining problems with the function. I truly appreciate
it
if you could provide me some additional pointers. Here's
what happens...

The function below (between the ******s) throws the
following error:
"Object variable or With block variable not set (91)".

Here's what I trying to achieve:
- Until I added the function DAO.Recordset, the INSERT was
working just fine.
- In order to view the changes immediately (after the
INSERT was executed) in the
tblLocationHistory_subform, I used the "Me.Requery".
- The Me.Requery, however, forced me to "jump" back to the
1st record (instead of remaining on the current record)
- I then included the "RecordClone" function (between the
*****s). This initially seemed to work fine, but I not
realized that the "record" freezes" - particularly when
making on the 1st record.

Again, when I now change the "AsOfDate" and then exit the
field, the error listed above "Object variable..." is
thrown.

How do I need to modify the DAO.Recordset query, so that I
can do the following:
1. Use the Me.Requery when on the 1st record
2. Use the DAO.Record function when on any other record
but the 1st record


Thanks in advance,
Tom




&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&

Private Sub AsOfDate_Exit(Cancel As Integer)
On Error GoTo Err_Location_Exit

strSQL = "Insert into tblLocationHistory
(CorrespondenceID, Disposition, Location, AsOfDate) " &
vbCrLf & _
"VALUES ('" & Format(Me!
CorrespondenceID.Value, "00000") & "', '" & Me!
Disposition.Value & "', '" & Me!Location.Value & "', #" &
Format(AsOfDate) & "#)"

MsgBox strSQL, , "Location history table will be
updated!"

CurrentDb().Execute strSQL, dbFailOnError



'******
Dim rs As DAO.Recordset

If rs.RecordCount = 1 Then

Me.Requery

Else

Dim lngID As Long

With Me
lngID = .CorrespondenceID
.Requery
DoEvents

Set rs = .RecordsetClone
rs.FindFirst "[CorrespondenceID] = " &
lngID

If rs.NoMatch = False Then
.Bookmark = rs.Bookmark

End If
End With

End If
'******



End_Location_Exit:
Exit Sub

Err_Location_Exit:
MsgBox Err.Description & " (" & Err.Number & ")", _
vbOKOnly + vbCritical
Resume End_Location_Exit

End Sub



&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&



-----Original Message-----
Graham:

I like this approach... simple and straight-forward.

I now bypass the initial 3020 error; however, once the
first record is created, the screen/record freezes when I
run another function that allows me to
update/insert "current values" into a "history table".

This is how I modified the DAO function as you suggested
(I did not use the Exit sub though since other steps have
to be performed before I should exit out).

With the information given, do you have any idea why the
record still freezes at times. Or is the "Do Nothing"
approach not the best one to use?

Thanks in advance,
Tom



&&&&&&&&&&&&&&


If Me.RecordsetClone.RecordCount = 0 Then
'Do Nothing

Else

Dim rs As DAO.Recordset
Dim lngID As Long

With Me
lngID = .CorrespondenceID 'ensure to
synchronize naming convention of PK below []
.Requery
DoEvents

Set rs = .RecordsetClone
rs.FindFirst "[CorrespondenceID] = " &
lngID

If rs.NoMatch = False Then
.Bookmark = rs.Bookmark

End If
End With

End If

'****
-----Original Message-----
Hi Tom

I'm not sure how you're getting error 3020. I would have expected a
"Invalid use of Null" error on the line:
lngID = .AutoNumberFieldName

However, the simplest solution would be not to requery if there are no
records:
If Me.RecordsetClone.RecordCount = 0 then Exit Sub

--
Good Luck!
Graham Mandeno [Access MVP]
Auckland, New Zealand

Return mail address is invalid in a vain attempt to reduce spam.
Feedback is welcome at: (e-mail address removed)
Please post new questions or followups to newsgroup.

Instead of using the "Me.Requery" on the form, I use the
function below (****) for requerying the form.

This will allow me to "stay" on the current record (after
requerying) instead of "jumping" back to the first record.

I now realize, however, that the function throws an error
if the table is completely empty and I requery the first
record.

The error is: "Update or CancelUpdate without AddNew or
Edit" (error 3020).

Now, in the form's OnError event, I have put the
following:

If DataErr = 3020 Then
Response = acDataErrContinue
End If

... however, I now cannot change any other field values
in the form anymore.

Does anyone know how to either modify the function below
or simply ignore the error message and allowing me to
continue to make changes to the 1st record?

Thanks in advance,
Tom


&&&&&&&&&&&&&&&&&&&&&&&&&

Object (DAO) 3.6 Library in the References Collection:

****
Dim rs As DAO.Recordset
Dim lngID As Long

With Me
lngID = .AutoNumberFieldName
.Requery
DoEvents
Set rs = .RecordsetClone
rs.FindFirst "[AutoNumberFieldName] = " & lngID
If rs.NoMatch = False Then
.Bookmark = rs.Bookmark
End If
End With
****







.
Click to expand...
.
Click to expand...
Click to expand...
 
Douglas:

I reckon the "If rs.RecordCount = 1 Then" may not be
needed.

Here's what I'm trying to achieve...

1. After I execute an INSERT statement (which puts the
current values for "Location" & "Date" into a
tblLocationHistory, I'd like to see the newly inserted
values (Location & History) in the underlying subform.

Using the function beginning with (between ***s) works
fine as long as I'm NOT working off the 1st record.

On any records where record number > 1, I can insert
values into the tblLocationHistory without problems... it
automatically updates the subform, etc...

However, again, on the 1st record, after I executed an
INSERT and then made another change to any textbox value,
the textbox that was changed after the INSERT is now
frozen.

So, all I'm trying to achieve is this...
- Do a simple Me.Requery of the form when on the 1st
record. In this case, I do not worry about "jumping"
from e.g. record # 26 to record # 1 (since I'm already on
record # 1).
- If I'm on any other record but record # 1, execute the
DAO.recordset so that I will NOT "jump" from the current
record to record # 1.

I hope that I was able to explain properly what I'm
trying to achieve. I'm sure the answer is easy... I
just can't come up with it though.

Thanks,
Tom


****
Dim rs As DAO.Recordset
Dim lngID As Long
With Me
.....
.....
End With
End If
****


-----Original Message-----
You haven't created the recordset that you're trying to use before you have
the statement

If rs.RecordCount = 1 Then

What recordset's count are you trying to check?

--
Doug Steele, Microsoft Access MVP



Tom said:
Thanks for your help on this, however, I still have some
remaining problems with the function. I truly appreciate
it
if you could provide me some additional pointers. Here's
what happens...

The function below (between the ******s) throws the
following error:
"Object variable or With block variable not set (91)".

Here's what I trying to achieve:
- Until I added the function DAO.Recordset, the INSERT was
working just fine.
- In order to view the changes immediately (after the
INSERT was executed) in the
tblLocationHistory_subform, I used the "Me.Requery".
- The Me.Requery, however, forced me to "jump" back to the
1st record (instead of remaining on the current record)
- I then included the "RecordClone" function (between the
*****s). This initially seemed to work fine, but I not
realized that the "record" freezes" - particularly when
making on the 1st record.

Again, when I now change the "AsOfDate" and then exit the
field, the error listed above "Object variable..." is
thrown.

How do I need to modify the DAO.Recordset query, so that I
can do the following:
1. Use the Me.Requery when on the 1st record
2. Use the DAO.Record function when on any other record
but the 1st record


Thanks in advance,
Tom




&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&

Private Sub AsOfDate_Exit(Cancel As Integer)
On Error GoTo Err_Location_Exit

strSQL = "Insert into tblLocationHistory
(CorrespondenceID, Disposition, Location, AsOfDate) " &
vbCrLf & _
"VALUES ('" & Format(Me!
CorrespondenceID.Value, "00000") & "', '" & Me!
Disposition.Value & "', '" & Me!Location.Value & "', #" &
Format(AsOfDate) & "#)"

MsgBox strSQL, , "Location history table will be
updated!"

CurrentDb().Execute strSQL, dbFailOnError



'******
Dim rs As DAO.Recordset

If rs.RecordCount = 1 Then

Me.Requery

Else

Dim lngID As Long

With Me
lngID = .CorrespondenceID
.Requery
DoEvents

Set rs = .RecordsetClone
rs.FindFirst "[CorrespondenceID] = " &
lngID

If rs.NoMatch = False Then
.Bookmark = rs.Bookmark

End If
End With

End If
'******



End_Location_Exit:
Exit Sub

Err_Location_Exit:
MsgBox Err.Description & " (" & Err.Number & ")", _
vbOKOnly + vbCritical
Resume End_Location_Exit

End Sub



&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&



-----Original Message-----
Graham:

I like this approach... simple and straight-forward.

I now bypass the initial 3020 error; however, once the
first record is created, the screen/record freezes when I
run another function that allows me to
update/insert "current values" into a "history table".

This is how I modified the DAO function as you suggested
(I did not use the Exit sub though since other steps have
to be performed before I should exit out).

With the information given, do you have any idea why the
record still freezes at times. Or is the "Do Nothing"
approach not the best one to use?

Thanks in advance,
Tom



&&&&&&&&&&&&&&


If Me.RecordsetClone.RecordCount = 0 Then
'Do Nothing

Else

Dim rs As DAO.Recordset
Dim lngID As Long

With Me
lngID = .CorrespondenceID 'ensure to
synchronize naming convention of PK below []
.Requery
DoEvents

Set rs = .RecordsetClone
rs.FindFirst "[CorrespondenceID] = " &
lngID

If rs.NoMatch = False Then
.Bookmark = rs.Bookmark

End If
End With

End If

'****
-----Original Message-----
Hi Tom

I'm not sure how you're getting error 3020. I would
have expected a
"Invalid use of Null" error on the line:
lngID = .AutoNumberFieldName

However, the simplest solution would be not to requery
if there are no
records:
If Me.RecordsetClone.RecordCount = 0 then Exit Sub

--
Good Luck!
Graham Mandeno [Access MVP]
Auckland, New Zealand

Return mail address is invalid in a vain attempt to
reduce spam.
Feedback is welcome at: (e-mail address removed)
Please post new questions or followups to newsgroup.

Instead of using the "Me.Requery" on the form, I use
the
function below (****) for requerying the form.

This will allow me to "stay" on the current record
(after
requerying) instead of "jumping" back to the first
record.

I now realize, however, that the function throws an
error
if the table is completely empty and I requery the
first
record.

The error is: "Update or CancelUpdate without AddNew or
Edit" (error 3020).

Now, in the form's OnError event, I have put the
following:

If DataErr = 3020 Then
Response = acDataErrContinue
End If

... however, I now cannot change any other field values
in the form anymore.

Does anyone know how to either modify the function
below
or simply ignore the error message and allowing me to
continue to make changes to the 1st record?

Thanks in advance,
Tom


&&&&&&&&&&&&&&&&&&&&&&&&&

Object (DAO) 3.6 Library in the References Collection:

****
Dim rs As DAO.Recordset
Dim lngID As Long

With Me
lngID = .AutoNumberFieldName
.Requery
DoEvents
Set rs = .RecordsetClone
rs.FindFirst "[AutoNumberFieldName] = " & lngID
If rs.NoMatch = False Then
.Bookmark = rs.Bookmark
End If
End With
****







.

.
Click to expand...
Click to expand...


.
Click to expand...
 
Hi Tom

The trouble is, as Doug says, that you are referring to the variable "rs"
before you have assigned anything to it. An "object variable" (for example,
one Dim-ed as a Recordset) starts off with a value of "Nothing" (just as an
integer variable starts with a value of zero). You can literally do nothing
with "Nothing", hence the error "Object variable not set".

Your "rs" variable remains "Nothing" until you get to the line:
Set rs = .RecordsetClone
Because this line is inside a "With Me" block, it is interpreted as:
Set rs = Me.RecordsetClone

What you need to do is either (a) avoid referring to rs until after that
line, or (b) move that line further up your code.

I suggest (b), and also, extend your With block to include the first part as
well:

============ start code ============
Dim rs As DAO.Recordset
Dim lngID As Long
With Me
lngID = .CorrespondenceID
.Requery
Set rs = .RecordsetClone
If rs.RecordCount > 1 Then
DoEvents ' <<<<<< not sure if this would be necessary
rs.FindFirst "CorrespondenceID = " & lngID
If rs.NoMatch = False Then .Bookmark = rs.Bookmark
End If
End With
========== end code ==============

--
Good Luck!
Graham Mandeno [Access MVP]
Auckland, New Zealand

Return mail address is invalid in a vain attempt to reduce spam.
Feedback is welcome at: (e-mail address removed)
Please post new questions or followups to newsgroup.

Tom said:
Douglas:

I reckon the "If rs.RecordCount = 1 Then" may not be
needed.
Click to expand...
.... [snip]
 
Graham:

Thanks again for your feedback. I did put the code into
the function as you suggested.

Unfortunately, the 1st record (and only the 1st record)
freezes after I execute the INSERT into tblLocationHistory
table.

Is there a way to treat the 1st record differently than
all other records in the record set?

Pseudo:
If Record # 1 then Me.Requery
If Record # 2 to e.g. record # 1000, then DAO.recordset

I truly would appreciate any additional feedback you might
have.

Thanks,
Tom

-----Original Message-----
Hi Tom

The trouble is, as Doug says, that you are referring to the variable "rs"
before you have assigned anything to it. An "object variable" (for example,
one Dim-ed as a Recordset) starts off with a value of "Nothing" (just as an
integer variable starts with a value of zero). You can literally do nothing
with "Nothing", hence the error "Object variable not set".

Your "rs" variable remains "Nothing" until you get to the line:
Set rs = .RecordsetClone
Because this line is inside a "With Me" block, it is interpreted as:
Set rs = Me.RecordsetClone

What you need to do is either (a) avoid referring to rs until after that
line, or (b) move that line further up your code.

I suggest (b), and also, extend your With block to include the first part as
well:

============ start code ============
Dim rs As DAO.Recordset
Dim lngID As Long
With Me
lngID = .CorrespondenceID
.Requery
Set rs = .RecordsetClone
If rs.RecordCount > 1 Then
DoEvents ' <<<<<< not sure if this would be necessary
rs.FindFirst "CorrespondenceID = " & lngID
If rs.NoMatch = False Then .Bookmark = rs.Bookmark
End If
End With
========== end code ==============

--
Good Luck!
Graham Mandeno [Access MVP]
Auckland, New Zealand

Return mail address is invalid in a vain attempt to reduce spam.
Feedback is welcome at: (e-mail address removed)
Please post new questions or followups to newsgroup.

Tom said:
Douglas:

I reckon the "If rs.RecordCount = 1 Then" may not be
needed.
Click to expand...
.... [snip]


.
Click to expand...
 
Graham:

Thanks again for your feedback. I did put the code into
the function as you suggested.

Unfortunately, the 1st record (and only the 1st record)
freezes after I execute the INSERT into tblLocationHistory
table.

Is there a way to treat the 1st record differently than
all other records in the record set?

Pseudo:
If Record # 1 then Me.Requery
If Record # 2 to e.g. record # 1000, then DAO.recordset

I truly would appreciate any additional feedback you might
have.

Thanks,
Tom

-----Original Message-----
Hi Tom

The trouble is, as Doug says, that you are referring to the variable "rs"
before you have assigned anything to it. An "object variable" (for example,
one Dim-ed as a Recordset) starts off with a value of "Nothing" (just as an
integer variable starts with a value of zero). You can literally do nothing
with "Nothing", hence the error "Object variable not set".

Your "rs" variable remains "Nothing" until you get to the line:
Set rs = .RecordsetClone
Because this line is inside a "With Me" block, it is interpreted as:
Set rs = Me.RecordsetClone

What you need to do is either (a) avoid referring to rs until after that
line, or (b) move that line further up your code.

I suggest (b), and also, extend your With block to include the first part as
well:

============ start code ============
Dim rs As DAO.Recordset
Dim lngID As Long
With Me
lngID = .CorrespondenceID
.Requery
Set rs = .RecordsetClone
If rs.RecordCount > 1 Then
DoEvents ' <<<<<< not sure if this would be necessary
rs.FindFirst "CorrespondenceID = " & lngID
If rs.NoMatch = False Then .Bookmark = rs.Bookmark
End If
End With
========== end code ==============

--
Good Luck!
Graham Mandeno [Access MVP]
Auckland, New Zealand

Return mail address is invalid in a vain attempt to reduce spam.
Feedback is welcome at: (e-mail address removed)
Please post new questions or followups to newsgroup.

Tom said:
Douglas:

I reckon the "If rs.RecordCount = 1 Then" may not be
needed.
Click to expand...
.... [snip]


.
Click to expand...
 
Tom

I hope you realise now why the Netiquette asks newsgroup users not to create
multiple threads and not to multi-post the same question. Since you posted
the same question in 3 different threads in 2 newsgroups (at least those
that I can identify), you have 5 people (Douglas J. Steele, Graham Mandeno,
David Epsom, Terry ? and I myself) trying to help you without really knowing
what's has been covered.

In addition, I don't think you have given enough information to any of the
threads so that respondents can make a useful suggestion. Out of about 10
messages you posted, you mentioned the Subform (it is a vital bit of info, I
think) ONCE and if my understanding the description you offered so far is
correct, you actually want to re-query the Subform, NOT the main Form. If
this is the case, virtually all the answers you have been given are off the
mark because all respondents have thought you wanted to re-query the main
Form and NOT the subform.

Please keep to one thread and if you want your question to appear in a
number of newsgroups, use cross-posting (sending one post with up to 3
relevant newsgroup addresses on the post). This way, yourself & all
respondents can see all replies / what has been offered on the thread
regardless of which newsgroup the respondent answered from. Also, please
note that all regular respondents read a large number of newsgroups so there
is no needs to cross-post to many newsgroup.

Also, please do wait for replies for 48 hrs have passed before creating a
new thread because lack of replies in the first thread. If you don't get
any response in the first post, it is possible that your description doesn't
give enough info. for potential respondents to respond. Thus you should
re-read your original description and re-phrase it (if appropriate) before
re-posting. Also, mark in the Subject as "2nd attempt" as some respondents
give more efforts on "2nd attempt" post (but they do check that you already
had the "1st attempt" post).

I will now revert back to the original thread ("Requery" pushes > 1st Record
in this newsgroup and ask you for some more info regarding your Form /
Subform set-up)
 
Back
Top