DAO Recordset (instead of Me.Requery)

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

david epsom dot com dot au

rs.FindFirst "[AutoNumberFieldName] = " & lngID
If rs.NoMatch = False Then
Click to expand...

-------
if not rs.eof then
rs.FindFirst "[AutoNumberFieldName] = " & lngID
If rs.NoMatch = False Then
-------

(david)

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

Van T. Dinh

I am not sure when (which event) you use to call your
function but to prevent the function being called when you
are on the new Record, you can use something like:

If Me.NewRecord = False Then
'Call your function
End If

HTH
Van T. Dinh
MVP (Access)
 
T

Terry

I check for empty recordsets with something similar:

If rs.BOF=True and rs.EOF=True then
do something here
End If

The BOF check is included because both BOF and EOF will be true for an empty
recordset, whilst EOF may well be true for a non-empty recordset that is at
the last record.

Sometimes and I'm not sure why it should be the case, I have had to also
check for rs.Recordcount=0 to detect an empty recordset. I suspect this may
be due to what else I was doing at the time.

This may be overkill as code goes but it catches a recordets status:

If rs.RecordCount=0 then
' Do something here
ElseIf rs.BOF=True and rs.EOF=True then
' Do something here
Else
' Recordset has at least 1 record
End If

Regards

david epsom dot com dot au said:
rs.FindFirst "[AutoNumberFieldName] = " & lngID
If rs.NoMatch = False Then
Click to expand...

-------
if not rs.eof then
rs.FindFirst "[AutoNumberFieldName] = " & lngID
If rs.NoMatch = False Then
-------

(david)

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

Tom

Terry:

Thanks for your feedback... I wasn't entirely sure how to
integrate your suggestions. This is what I currently
have...


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-----
I check for empty recordsets with something similar:

If rs.BOF=True and rs.EOF=True then
do something here
End If

The BOF check is included because both BOF and EOF will be true for an empty
recordset, whilst EOF may well be true for a non-empty recordset that is at
the last record.

Sometimes and I'm not sure why it should be the case, I have had to also
check for rs.Recordcount=0 to detect an empty recordset. I suspect this may
be due to what else I was doing at the time.

This may be overkill as code goes but it catches a recordets status:

If rs.RecordCount=0 then
' Do something here
ElseIf rs.BOF=True and rs.EOF=True then
' Do something here
Else
' Recordset has at least 1 record
End If

Regards

rs.FindFirst "[AutoNumberFieldName] = " & lngID
If rs.NoMatch = False Then
Click to expand...

-------
if not rs.eof then
rs.FindFirst "[AutoNumberFieldName] = " & lngID
If rs.NoMatch = False Then
-------

(david)

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

Tom

Thanks for your help on this, however, I still have some
remaining problems with the function.

I wasn't entirely certain as to how I should integrate
your suggestions. Here's what I currently have...



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-----
rs.FindFirst "[AutoNumberFieldName] = " & lngID
If rs.NoMatch = False Then
Click to expand...

-------
if not rs.eof then
rs.FindFirst "[AutoNumberFieldName] = " & lngID
If rs.NoMatch = False Then
-------

(david)

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

Tom

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-----
I am not sure when (which event) you use to call your
function but to prevent the function being called when you
are on the new Record, you can use something like:

If Me.NewRecord = False Then
'Call your function
End If

HTH
Van T. Dinh
MVP (Access)



-----Original Message-----
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...
 
V

Van T. Dinh

You haven't set the Recordset. Read my answer carefully in the original
thread.

Please stick to ONE thread rather than creating new threads. Most
respondents don't want to have to read 2 threads to see what has been
covered. Besides, I would rather spend time trying to help someone who
bother to read the newsgroup netiquette and use the newsgroup properly.

See http://www.mvps.org/access/netiquette.htm
 
T

Tom

Got it! I'll read the Netiquette later on today.

Please see my reply in original thread.

Tom

-----Original Message-----
You haven't set the Recordset. Read my answer carefully in the original
thread.

Please stick to ONE thread rather than creating new threads. Most
respondents don't want to have to read 2 threads to see what has been
covered. Besides, I would rather spend time trying to help someone who
bother to read the newsgroup netiquette and use the newsgroup properly.

See http://www.mvps.org/access/netiquette.htm

--
HTH
Van T. Dinh
MVP (Access)



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
Click to expand...



.
Click to expand...
 
T

Terry

There are a couple of things you need to consider in your existing code Tom.

If rs.RecordCount = 1 Then

If the above test fails, i.e. recordcount is 0, or greater than 1 then the
logic will force a bookmark function. You will most probably need to test
recordcount >1.

If the recordset is empty you will get an error when the code tries to move
to the bookmark. You may wish to test for an empty recordset prior to using
the bookmark function.

I guess the controls on the form are unbound!
Regards

Tom said:
Terry:

Thanks for your feedback... I wasn't entirely sure how to
integrate your suggestions. This is what I currently
have...


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-----
I check for empty recordsets with something similar:

If rs.BOF=True and rs.EOF=True then
do something here
End If

The BOF check is included because both BOF and EOF will be true for an empty
recordset, whilst EOF may well be true for a non-empty recordset that is at
the last record.

Sometimes and I'm not sure why it should be the case, I have had to also
check for rs.Recordcount=0 to detect an empty recordset. I suspect this may
be due to what else I was doing at the time.

This may be overkill as code goes but it catches a recordets status:

If rs.RecordCount=0 then
' Do something here
ElseIf rs.BOF=True and rs.EOF=True then
' Do something here
Else
' Recordset has at least 1 record
End If

Regards

rs.FindFirst "[AutoNumberFieldName] = " & lngID
If rs.NoMatch = False Then

-------
if not rs.eof then
rs.FindFirst "[AutoNumberFieldName] = " & lngID
If rs.NoMatch = False Then
-------

(david)

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

Tom

Terry:

Thanks for your prompt feedback. Quick follow-up
though...

When you say If rs.RecordCount = 1 Then", does this mean
a) do XYZ if there's only 1 record in the table, or
b) do XYZ if I am on the 1st record

Currently, the function works fine on all records EXCEPT
the 1st one. Once I do the LocationHistory update (the
INSERT command of the 3 fields) and THEN change any other
value in the FORM of the 1st record, the record freezes up.

So, basically, I need to find an answer which would do a
simple "Me.Requery" when I'm on the 1st record... if not,
it should execute the DAO.Recordset function. Makes sense?

I truly would appreciate if you have any additional
pointers!!!

THANKS SO MUCH IN ADVANCE,
Tom


-----Original Message-----
There are a couple of things you need to consider in your existing code Tom.

If rs.RecordCount = 1 Then

If the above test fails, i.e. recordcount is 0, or greater than 1 then the
logic will force a bookmark function. You will most probably need to test
recordcount >1.

If the recordset is empty you will get an error when the code tries to move
to the bookmark. You may wish to test for an empty recordset prior to using
the bookmark function.

I guess the controls on the form are unbound!
Regards

Tom said:
Terry:

Thanks for your feedback... I wasn't entirely sure how to
integrate your suggestions. This is what I currently
have...


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-----
I check for empty recordsets with something similar:

If rs.BOF=True and rs.EOF=True then
do something here
End If

The BOF check is included because both BOF and EOF will be true for an empty
recordset, whilst EOF may well be true for a non-empty recordset that is at
the last record.

Sometimes and I'm not sure why it should be the case, I have had to also
check for rs.Recordcount=0 to detect an empty
Click to expand...
recordset.
I suspect this may
be due to what else I was doing at the time.

This may be overkill as code goes but it catches a recordets status:

If rs.RecordCount=0 then
' Do something here
ElseIf rs.BOF=True and rs.EOF=True then
' Do something here
Else
' Recordset has at least 1 record
End If

Regards

rs.FindFirst "[AutoNumberFieldName] = " & lngID
If rs.NoMatch = False Then

-------
if not rs.eof then
rs.FindFirst "[AutoNumberFieldName] = " & lngID
If rs.NoMatch = False Then
-------

(david)

Instead of using the "Me.Requery" on the form, I
Click to expand...
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
Click to expand...
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...
 
T

Terry

Hi Tom,

The recordcount will give the total number of records in a recordset, so if
it shows 1 then 1 is all you have. If you want to test to see which record
you are on then use rs.AbsolutePosition. If you need to go to the first
record use rs.MoveFirst, but if you try this with an empty recordset you
will get an error.

regards


Tom said:
Terry:

Thanks for your prompt feedback. Quick follow-up
though...

When you say If rs.RecordCount = 1 Then", does this mean
a) do XYZ if there's only 1 record in the table, or
b) do XYZ if I am on the 1st record

Currently, the function works fine on all records EXCEPT
the 1st one. Once I do the LocationHistory update (the
INSERT command of the 3 fields) and THEN change any other
value in the FORM of the 1st record, the record freezes up.

So, basically, I need to find an answer which would do a
simple "Me.Requery" when I'm on the 1st record... if not,
it should execute the DAO.Recordset function. Makes sense?

I truly would appreciate if you have any additional
pointers!!!

THANKS SO MUCH IN ADVANCE,
Tom


-----Original Message-----
There are a couple of things you need to consider in your existing code Tom.

If rs.RecordCount = 1 Then

If the above test fails, i.e. recordcount is 0, or greater than 1 then the
logic will force a bookmark function. You will most probably need to test
recordcount >1.

If the recordset is empty you will get an error when the code tries to move
to the bookmark. You may wish to test for an empty recordset prior to using
the bookmark function.

I guess the controls on the form are unbound!
Regards

Tom said:
Terry:

Thanks for your feedback... I wasn't entirely sure how to
integrate your suggestions. This is what I currently
have...


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-----
I check for empty recordsets with something similar:

If rs.BOF=True and rs.EOF=True then
do something here
End If

The BOF check is included because both BOF and EOF will
be true for an empty
recordset, whilst EOF may well be true for a non-empty
recordset that is at
the last record.

Sometimes and I'm not sure why it should be the case, I
have had to also
check for rs.Recordcount=0 to detect an empty recordset.
I suspect this may
be due to what else I was doing at the time.

This may be overkill as code goes but it catches a
recordets status:

If rs.RecordCount=0 then
' Do something here
ElseIf rs.BOF=True and rs.EOF=True then
' Do something here
Else
' Recordset has at least 1 record
End If

Regards

"david epsom dot com dot au" <david@epsomdotcomdotau>
wrote in message
rs.FindFirst "[AutoNumberFieldName] = " & lngID
If rs.NoMatch = False Then

-------
if not rs.eof then
rs.FindFirst "[AutoNumberFieldName] = " & lngID
If rs.NoMatch = False Then
-------

(david)

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

Van T. Dinh

(for the benefit of other respondents)

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 the "forms" newsgroup and ask you for some more info regarding your Form
/
Subform set-up)
 
T

Tom

Terry:

Do you have any additional feedback as to how I can solve
the "frozen record" problem?

Thanks,
Tom

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

The recordcount will give the total number of records in a recordset, so if
it shows 1 then 1 is all you have. If you want to test to see which record
you are on then use rs.AbsolutePosition. If you need to go to the first
record use rs.MoveFirst, but if you try this with an empty recordset you
will get an error.

regards


Tom said:
Terry:

Thanks for your prompt feedback. Quick follow-up
though...

When you say If rs.RecordCount = 1 Then", does this mean
a) do XYZ if there's only 1 record in the table, or
b) do XYZ if I am on the 1st record

Currently, the function works fine on all records EXCEPT
the 1st one. Once I do the LocationHistory update (the
INSERT command of the 3 fields) and THEN change any other
value in the FORM of the 1st record, the record freezes up.

So, basically, I need to find an answer which would do a
simple "Me.Requery" when I'm on the 1st record... if not,
it should execute the DAO.Recordset function. Makes sense?

I truly would appreciate if you have any additional
pointers!!!

THANKS SO MUCH IN ADVANCE,
Tom


-----Original Message-----
There are a couple of things you need to consider in
Click to expand...
your
existing code Tom.
If rs.RecordCount = 1 Then

If the above test fails, i.e. recordcount is 0, or greater than 1 then the
logic will force a bookmark function. You will most probably need to test
recordcount >1.

If the recordset is empty you will get an error when
Click to expand...
the
code tries to move
to the bookmark. You may wish to test for an empty recordset prior to using
the bookmark function.

I guess the controls on the form are unbound!
Regards

Terry:

Thanks for your feedback... I wasn't entirely sure
Click to expand...
how
to
integrate your suggestions. This is what I currently
have...


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
Click to expand...
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
Click to expand...
to
the
1st record (instead of remaining on the current record)
- I then included the "RecordClone" function
Click to expand...
(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
Click to expand...
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
Click to expand...
& "', #"
&
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-----
I check for empty recordsets with something similar:

If rs.BOF=True and rs.EOF=True then
do something here
End If

The BOF check is included because both BOF and EOF will
be true for an empty
recordset, whilst EOF may well be true for a non- empty
recordset that is at
the last record.

Sometimes and I'm not sure why it should be the case, I
have had to also
check for rs.Recordcount=0 to detect an empty recordset.
I suspect this may
be due to what else I was doing at the time.

This may be overkill as code goes but it catches a
recordets status:

If rs.RecordCount=0 then
' Do something here
ElseIf rs.BOF=True and rs.EOF=True then
' Do something here
Else
' Recordset has at least 1 record
End If

Regards

"david epsom dot com dot au"
Click to expand...
wrote in message
rs.FindFirst "[AutoNumberFieldName] = " & lngID
If rs.NoMatch = False Then

-------
if not rs.eof then
rs.FindFirst "[AutoNumberFieldName] = " & lngID
If rs.NoMatch = False Then
-------

(david)

Instead of using the "Me.Requery" on the form,
Click to expand...
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
Click to expand...
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...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

DAO.Recordset (instead of "Me.Requery) 9
Me.Requery & DAO.Recordset 4
Can't seem to retrieve primary key number of newly created record in VB 1
Why does me.requery result in "no current record" 18
You must use the dbSeeChanges option with Open Recordset whenaccessing a SQL Server table that has a 1
lost focus...? 3
me.requery 3
Duplicate record in Form and Subform 0

Top