"Requery" pushes > 1st Record

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

Tom

Is there an obvious reason why I "jump" back to the first
record after a form or text field is refreshed.

Over the course of the last few days, I have included
multiple "Requery" command for a single subform and
multiple text boxes.

At this time, I can not trace down which one of the
many "Me.Requery" commands forces me to "leave the
current record" by going back to the 1st record of a
table.

The development of the Requery commands were iterative
and I must have never realized that this problem happens
(since I currently only work with 1 to 3 testing records).

Again, the question is... is there a specific event
(OnCurrent, AfterUpdate, OnError, etc.) which forces one
(in FORM view) to go back to the 1st records after a
form/text field is refreshed?

Thanks,
Tom
 
Requery of the Form (or the Form used as the SourceObject of the
SubformControl) will set the 1st Record as the CurrentRecord of the Form.
This happens regardless of the Event you use.

If you need to retain the CurrentRecord just before the Form requeried, you
need to retain a identifying value of this Record and then use the (new)
Form's Recordset (Clone) to navigate back to the previous CurrentRecord (the
one just before re-querying).

HTH
Van T. Dinh
MVP (Access)
 
Okay, that makes sense now...

However, I don't think I'm entirely clear on the "Clone
Record" scenario.

On the form, I do show the AUTONUMBER ID... is that what
you would use to navigate back to the record that was
changed. If yes, I wouldn't know how?

Do you have any additional pointers for me?

Thanks in advance!

Tom
 
See comments in line.

--
HTH
Van T. Dinh
MVP (Access)



Tom said:
Okay, that makes sense now...

However, I don't think I'm entirely clear on the "Clone
Record" scenario.
Click to expand...
In A97 or ealier, you don't have acces to the Recordset of the Form directly
and you need to manipulate the RecordsetClone (a Property of the Form). See
later for coding example.


On the form, I do show the AUTONUMBER ID... is that what
you would use to navigate back to the record that was
changed. If yes, I wouldn't know how?
Click to expand...
Yes, AutoNumberID (PrimaryKey or at least uniquely indexed) is perfect.


Do you have any additional pointers for me?
Click to expand...
*Untested* code example - ensure you include the Microsoft Data Access
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
****
 
Thanks! I'll give it a try... hopefully I can make it
work. I'll post the result in a later thread.

Thanks again,
Tom

-----Original Message-----
See comments in line.

--
HTH
Van T. Dinh
MVP (Access)



Tom said:
Okay, that makes sense now...

However, I don't think I'm entirely clear on the "Clone
Record" scenario.
Click to expand...
In A97 or ealier, you don't have acces to the Recordset of the Form directly
and you need to manipulate the RecordsetClone (a Property of the Form). See
later for coding example.


On the form, I do show the AUTONUMBER ID... is that what
you would use to navigate back to the record that was
changed. If yes, I wouldn't know how?
Click to expand...
Yes, AutoNumberID (PrimaryKey or at least uniquely indexed) is perfect.


Do you have any additional pointers for me?
Click to expand...
*Untested* code example - ensure you include the Microsoft Data Access
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
****

Thanks in advance!

Tom
Click to expand...



.
Click to expand...
 
This works great... except when I create a new record.
It results in an error (error 3201) since the field (that
causes to refresh the form) is a required field.

Is there a way around this?

Thanks,
Tom
-----Original Message-----
Thanks! I'll give it a try... hopefully I can make it
work. I'll post the result in a later thread.

Thanks again,
Tom

-----Original Message-----
See comments in line.

--
HTH
Van T. Dinh
MVP (Access)



Tom said:
Okay, that makes sense now...

However, I don't think I'm entirely clear on the "Clone
Record" scenario.
Click to expand...
In A97 or ealier, you don't have acces to the Recordset of the Form directly
and you need to manipulate the RecordsetClone (a Property of the Form). See
later for coding example.


On the form, I do show the AUTONUMBER ID... is that what
you would use to navigate back to the record that was
changed. If yes, I wouldn't know how?
Click to expand...
Yes, AutoNumberID (PrimaryKey or at least uniquely indexed) is perfect.


Do you have any additional pointers for me?
Click to expand...
*Untested* code example - ensure you include the Microsoft Data Access
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
****

Thanks in advance!

Tom
Click to expand...



.
Click to expand...
.
Click to expand...
 
Requery of the Form will cause Access to attempt to complete the update on
the Form AFAIK. Thus you should avoid doing Requery while there is a
pending update on the Form.

Besides, why would you want to requery during data entry???

Which Event do you use to run the Requery?
 
Again, thanks for your reply...

I did copy your original suggestion (function) in the
event. It work superb until I realized (during further
testing) that a record (primarily the 1st one) freezes up.

Here's the challenge... and why I'd like to refresh the
form:
- On the main form, I have a subform called Location
History
- If on the main form, the LOCATION and DATE changes, then
I insert the values of these 2 fields into a "history
table" (this allows me to track down where and when the
location of an item has changed)
- The location history form is the subform. Here's where
I like to see the changes immediately.
- Please see the other thread (I noted your info about the
Netiquette) for further info.

I truly would appreciate if you could provide me
additional pointers.

BTW, I have browsed through the Netiquette... will read it
in more detail later. Thanks for sharing the website w/
me! I didn't mean to violate any etiquette procedures.


Tom
 
(as mentioned in other threads)

If my guess is correct (i.e. you actually want to re-query the Subform and
NOT the Main Form) then you should describe:

1. The relevant Tables / Queries including Field names you use as the
RecordSource for the Form / Subform.

2. Describe relevant Controls on your main Form / Subform.
Advise whether the Subform is a "linked" Subform or not.
Advise the name of the Subform*CONTROL*. Note that this may or may not
be the same as the name of the Form you used as the SourceObject of the
SubformControl. You can only find this name by selecting the SubformControl
(i.e. the rectangle around the "Subform") in the DesignView of the main Form
and check the name in the Properties window. Whatever code we'll suggest
won't work without the correct name of the Subform Control.

3. Which Event you want to use to run your INSERT INTO SQL and the
re-query? Is it the Exit Event of a Control on the main Form as you posted
somewhere else.

4. Post your attempted code to date. Add comments in your code if
possible.
 
Van:

Again, thanks for your help. I truly appreciate it!

To provide to more information, please allow me to give
first you an overview of what I trying to achieve.

PURPOSE:
On a MAIN FORM (frmCorrespondence), there are 2 fields
(LOCATION = textbox, AS_OF_DATE = date field) which
values will change over the course of a document's "life
cycle". A document is passed through the organization
and we need to be in the position to trace a) its current
location and b) all of its previous locations and their
dates.

PROCESS:
On the frmCorrespondence (main form), I have linked the
AS_OF_DATE field to a function will will insert
the "current"
LOCATION AND AS_OF_DATE values into a table called
tblLocationHistory. The functionality of this process
works
just fine.

The tblLocationHistory resides as a subform (I pressed F11
and simply dragged the table on the frmCorrespondence).
[I believe the sub form is "linked" to the main form).

If I now change the AS_OF_DATE, the AfterExit
event "kicks" in and inserts the location and date values
into the
tblLocationHistory. And this is where the problem
lies... the newly inserted records are NOT visible in the
subform
unless I either a) move to another previous/next record
and then record to the current record, or b) close the
frmCorrespondence and re-open it.

At this time, let me provide you the entire function that
linked to the MAIN FORM's properties. I have marked
the comment lines with an "@" to allow you to quickly
identify some potentially useful comments.



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

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


***** INSERT works fine ******
'@ 'This will insert the current Location and date values
into the "location history table"
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




***** PROBLEM AREA ******
'@ ' This is where I requery the form/subform. I really
need to only refresh the subform (tblLocationHistory) since
'@ ' this is where the changes should be visible in "real-
time"
'@ ' With the EXCEPTION of the 1st record, I do see the
changes immediately in the subform (although it was the
main
'@ ' form that was refreshed).
'@ ' PROBLEM: When executing the INSERT function (after
changing and exiting the date field), the next time I may
'@ ' make changes to any other textfield/combo box, that
partilur textbox/combo box is THEN LOCKED... actually the
'@ ' entire record is locked up. When closing the
frmCorrespondence's window, a dialog box shows up that
indicates
'@ ' that the changes of the current record (record #1)
cannot be saved.
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_Location_Exit:
Exit Sub

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

End Sub

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




PROPERTIES - MAIN FORM (frmCorrespondence):

Location field:
- resides on frmCorrespondence


AsOfDate field:
- resides on frmCorrespondence
- linked to the function above [Private Sub AsOfDate_Exit
(Cancel As Integer)]


PROPERTIES - SUB FORM (frmLocationHistory_Subform):
- contains the same identical fields (CorrespondenceID,
Location, AsOfDate) as the main form
- resides as a subform on frmCorrespondence



Quick recap:
- Subform needs to be refreshed/requeryed after the INSERT
into tblLocationHistory occurred
- DAO.Recordset works fine EXCEPT for the 1st record.
The textboxes on the main form lock up
when changes are made after the INSERT occurred on the
1st record.



Van, I truly hope that I have provided you sufficient
information to further analyze this problem.

Please let me know if you have any additional questions!


Thanks,
Tom
 
1. Like I suspected, the problem is that everyone's
advice from the info you posted was aimed at re-querying
the main Form which messed thing up. If you go back to my
previous posts, I was wondering why you re-queried (the
Form) using the Textbox_Exit Event.

2. All you need to do is to add a few lines of code to re-
query the Subform rather than the Main form.

3. I would normally use the AsOfDate_AfterUpdate Event
but Exit Event should be OK. Continuing with your first
bit of code, you need:

....
CurrentDb().Execute strSQL, dbFailOnError
DBEngine.Idle dbRefreshCache

Me.{SubformControlName}.Form.Requery
****

1. Delete the rest of the code except for the exit & the
error-trapping.

2. You need to replace {SubformControlName} with the
actual name in your Form/Subform. If you re-read my
previous post, I am sure you would notice that I stressed
the importance of the SubformControl Name.

HTH
Van T. Dinh
MVP (Access)



-----Original Message-----
Van:

Again, thanks for your help. I truly appreciate it!

To provide to more information, please allow me to give
first you an overview of what I trying to achieve.

PURPOSE:
On a MAIN FORM (frmCorrespondence), there are 2 fields
(LOCATION = textbox, AS_OF_DATE = date field) which
values will change over the course of a document's "life
cycle". A document is passed through the organization
and we need to be in the position to trace a) its current
location and b) all of its previous locations and their
dates.

PROCESS:
On the frmCorrespondence (main form), I have linked the
AS_OF_DATE field to a function will will insert
the "current"
LOCATION AND AS_OF_DATE values into a table called
tblLocationHistory. The functionality of this process
works
just fine.

The tblLocationHistory resides as a subform (I pressed F11
and simply dragged the table on the frmCorrespondence).
[I believe the sub form is "linked" to the main form).

If I now change the AS_OF_DATE, the AfterExit
event "kicks" in and inserts the location and date values
into the
tblLocationHistory. And this is where the problem
lies... the newly inserted records are NOT visible in the
subform
unless I either a) move to another previous/next record
and then record to the current record, or b) close the
frmCorrespondence and re-open it.

At this time, let me provide you the entire function that
linked to the MAIN FORM's properties. I have marked
the comment lines with an "@" to allow you to quickly
identify some potentially useful comments.



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

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


***** INSERT works fine ******
'@ 'This will insert the current Location and date values
into the "location history table"
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




***** PROBLEM AREA ******
'@ ' This is where I requery the form/subform. I really
need to only refresh the subform (tblLocationHistory) since
'@ ' this is where the changes should be visible in "real-
time"
'@ ' With the EXCEPTION of the 1st record, I do see the
changes immediately in the subform (although it was the
main
'@ ' form that was refreshed).
'@ ' PROBLEM: When executing the INSERT function (after
changing and exiting the date field), the next time I may
'@ ' make changes to any other textfield/combo box, that
partilur textbox/combo box is THEN LOCKED... actually the
'@ ' entire record is locked up. When closing the
frmCorrespondence's window, a dialog box shows up that
indicates
'@ ' that the changes of the current record (record #1)
cannot be saved.
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_Location_Exit:
Exit Sub

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

End Sub

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




PROPERTIES - MAIN FORM (frmCorrespondence):

Location field:
- resides on frmCorrespondence


AsOfDate field:
- resides on frmCorrespondence
- linked to the function above [Private Sub AsOfDate_Exit
(Cancel As Integer)]


PROPERTIES - SUB FORM (frmLocationHistory_Subform):
- contains the same identical fields (CorrespondenceID,
Location, AsOfDate) as the main form
- resides as a subform on frmCorrespondence



Quick recap:
- Subform needs to be refreshed/requeryed after the INSERT
into tblLocationHistory occurred
- DAO.Recordset works fine EXCEPT for the 1st record.
The textboxes on the main form lock up
when changes are made after the INSERT occurred on the
1st record.



Van, I truly hope that I have provided you sufficient
information to further analyze this problem.

Please let me know if you have any additional questions!


Thanks,
Tom
Click to expand...
 
Van:

Again, thanks so much for your feedback.

YES!!! This finally works. I was under the impression
that I had shared in one of the previous threads that I
needed to refresh the subform. Well, maybe I didn't
highlighted it not enough.

Anyhow, I am very happy w/ this functionality now.
Again, thanks so much for tutoring me through this
process and sharing some other info as well.

This truly has been a learning experience.

You're truly an MVP!

Tom

-----Original Message-----
1. Like I suspected, the problem is that everyone's
advice from the info you posted was aimed at re-querying
the main Form which messed thing up. If you go back to my
previous posts, I was wondering why you re-queried (the
Form) using the Textbox_Exit Event.

2. All you need to do is to add a few lines of code to re-
query the Subform rather than the Main form.

3. I would normally use the AsOfDate_AfterUpdate Event
but Exit Event should be OK. Continuing with your first
bit of code, you need:

....
CurrentDb().Execute strSQL, dbFailOnError
DBEngine.Idle dbRefreshCache

Me.{SubformControlName}.Form.Requery
****

1. Delete the rest of the code except for the exit & the
error-trapping.

2. You need to replace {SubformControlName} with the
actual name in your Form/Subform. If you re-read my
previous post, I am sure you would notice that I stressed
the importance of the SubformControl Name.

HTH
Van T. Dinh
MVP (Access)



-----Original Message-----
Van:

Again, thanks for your help. I truly appreciate it!

To provide to more information, please allow me to give
first you an overview of what I trying to achieve.

PURPOSE:
On a MAIN FORM (frmCorrespondence), there are 2 fields
(LOCATION = textbox, AS_OF_DATE = date field) which
values will change over the course of a document's "life
cycle". A document is passed through the organization
and we need to be in the position to trace a) its current
location and b) all of its previous locations and their
dates.

PROCESS:
On the frmCorrespondence (main form), I have linked the
AS_OF_DATE field to a function will will insert
the "current"
LOCATION AND AS_OF_DATE values into a table called
tblLocationHistory. The functionality of this process
works
just fine.

The tblLocationHistory resides as a subform (I pressed F11
and simply dragged the table on the frmCorrespondence).
[I believe the sub form is "linked" to the main form).

If I now change the AS_OF_DATE, the AfterExit
event "kicks" in and inserts the location and date values
into the
tblLocationHistory. And this is where the problem
lies... the newly inserted records are NOT visible in the
subform
unless I either a) move to another previous/next record
and then record to the current record, or b) close the
frmCorrespondence and re-open it.

At this time, let me provide you the entire function that
linked to the MAIN FORM's properties. I have marked
the comment lines with an "@" to allow you to quickly
identify some potentially useful comments.



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

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


***** INSERT works fine ******
'@ 'This will insert the current Location and date values
into the "location history table"
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




***** PROBLEM AREA ******
'@ ' This is where I requery the form/subform. I really
need to only refresh the subform (tblLocationHistory) since
'@ ' this is where the changes should be visible in "real-
time"
'@ ' With the EXCEPTION of the 1st record, I do see the
changes immediately in the subform (although it was the
main
'@ ' form that was refreshed).
'@ ' PROBLEM: When executing the INSERT function (after
changing and exiting the date field), the next time I may
'@ ' make changes to any other textfield/combo box, that
partilur textbox/combo box is THEN LOCKED... actually the
'@ ' entire record is locked up. When closing the
frmCorrespondence's window, a dialog box shows up that
indicates
'@ ' that the changes of the current record (record #1)
cannot be saved.
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_Location_Exit:
Exit Sub

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

End Sub

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




PROPERTIES - MAIN FORM (frmCorrespondence):

Location field:
- resides on frmCorrespondence


AsOfDate field:
- resides on frmCorrespondence
- linked to the function above [Private Sub AsOfDate_Exit
(Cancel As Integer)]


PROPERTIES - SUB FORM (frmLocationHistory_Subform):
- contains the same identical fields (CorrespondenceID,
Location, AsOfDate) as the main form
- resides as a subform on frmCorrespondence



Quick recap:
- Subform needs to be refreshed/requeryed after the INSERT
into tblLocationHistory occurred
- DAO.Recordset works fine EXCEPT for the 1st record.
The textboxes on the main form lock up
when changes are made after the INSERT occurred on the
1st record.



Van, I truly hope that I have provided you sufficient
information to further analyze this problem.

Please let me know if you have any additional questions!


Thanks,
Tom
Click to expand...

.
Click to expand...
 
Back
Top