ADP bound form - force record save, recordsetclone not populating after requery

[Br]

First issue:

When using ADPs you no longer have the ability to issue a me.refresh to
save the current record on a form (the me.refresh does a requery in an
ADP).

We usually do this before calling up another form or report that uses
some of the same data.

We came up with a work around that saves the current record's ID, does a
requery, then uses a recordsetclone to search for the ID and then set
the form's bookmark to the recordset clone's. This is the same method we
use for our search functions on our forms (without the requery
obviously).

This doesn't always work because of the second issue below.

How can you save the current record on a bound form in an ADP?

Second issue:

The above searching method seemed to work until we encountered larger
recordsets. It seems that Access doesn't get all the data immediately
after you requery so the recordsetclone only contains a small subset of
records... thus the search routine fails to find the record for higher
ID values. If you trace through the code a line at a time it works fine.
Adding a pause loop in the code doesn't work (and is a bad practice
IMO).

How can you make sure a recordsetclone contains all the records after a
requery?

For now I've had to implement a terrible practice... in code move the
form to the next record and then move back which causes the record to be
saved.

Using A2000, SQL2000
--
regards,

Bradley

A Christian Response
http://www.pastornet.net.au/response

[Lyle Fairfield]

"Br@dley" <br**@usenet.or g> wrote in news:d1thf.3241 $ea6.486@news-
server.bigpond. net.au:

Second issue:

The above searching method seemed to work until we encountered larger
recordsets. It seems that Access doesn't get all the data immediately
after you requery so the recordsetclone only contains a small subset of
records... thus the search routine fails to find the record for higher
ID values. If you trace through the code a line at a time it works fine.
Adding a pause loop in the code doesn't work (and is a bad practice
IMO).

This post addresses the problem you describe, I believe, but in different
circumstances.

http://groups.google.ca/group/comp.d...6d7ed38ae217d8

(I tried posting this through google groups but it not seem to go;
apologies if it appears more than once).

--
Lyle Fairfield

[Br]

Br@dley wrote:

First issue:

When using ADPs you no longer have the ability to issue a me.refresh
to save the current record on a form (the me.refresh does a requery
in an ADP).

We usually do this before calling up another form or report that uses
some of the same data.
And when returning from a form based on some of the same recordset to
refresh the main form to reflect any changes.
We came up with a work around that saves the current record's ID,
does a requery, then uses a recordsetclone to search for the ID and
then set the form's bookmark to the recordset clone's. This is the
same method we use for our search functions on our forms (without the
requery obviously).

This doesn't always work because of the second issue below.

How can you save the current record on a bound form in an ADP?

Second issue:

The above searching method seemed to work until we encountered larger
recordsets. It seems that Access doesn't get all the data immediately
after you requery so the recordsetclone only contains a small subset
of records... thus the search routine fails to find the record for
higher ID values. If you trace through the code a line at a time it
works fine. Adding a pause loop in the code doesn't work (and is a
bad practice IMO).

How can you make sure a recordsetclone contains all the records after
a requery?

For now I've had to implement a terrible practice... in code move the
form to the next record and then move back which causes the record to
be saved.

Using A2000, SQL2000

--
regards,

Bradley

A Christian Response
http://www.pastornet.net.au/response

[Lyle Fairfield]

This article addresses the problem you describe, but not in the same
circumstances.
http://groups.google.ca/group/comp.d...6d7ed38ae217d8

[Br]

Lyle Fairfield wrote:

"Br@dley" <br**@usenet.or g> wrote in news:d1thf.3241 $ea6.486@news-
server.bigpond. net.au:

Second issue:

The above searching method seemed to work until we encountered larger
recordsets. It seems that Access doesn't get all the data immediately
after you requery so the recordsetclone only contains a small subset
of records... thus the search routine fails to find the record for
higher ID values. If you trace through the code a line at a time it
works fine. Adding a pause loop in the code doesn't work (and is a
bad practice IMO).

This post addresses the problem you describe, I believe, but in
different circumstances.

http://groups.google.ca/group/comp.d...6d7ed38ae217d8

(I tried posting this through google groups but it not seem to go;
apologies if it appears more than once).

Thanks, I'll give that a try.
--
regards,

Bradley

A Christian Response
http://www.pastornet.net.au/response

[Br]

Lyle Fairfield wrote:

"Br@dley" <br**@usenet.or g> wrote in news:d1thf.3241 $ea6.486@news-
server.bigpond. net.au:

Second issue:

The above searching method seemed to work until we encountered larger
recordsets. It seems that Access doesn't get all the data immediately
after you requery so the recordsetclone only contains a small subset
of records... thus the search routine fails to find the record for
higher ID values. If you trace through the code a line at a time it
works fine. Adding a pause loop in the code doesn't work (and is a
bad practice IMO).

This post addresses the problem you describe, I believe, but in
different circumstances.

http://groups.google.ca/group/comp.d...6d7ed38ae217d8

(I tried posting this through google groups but it not seem to go;
apologies if it appears more than once).

He's a generic function I wrote based on your post:

Function RefreshForm(myF orm As Form, myField As String)
' sample call : RefreshForm Me, "EmployeeID "
On Error GoTo RefreshForm_err
Dim rs As ADODB.Recordset
Dim myvalue As long
myValue = myForm.Controls (myField).Value
Set rs = New ADODB.Recordset
rs.CursorLocati on = adUseClient
rs.ActiveConnec tion = CurrentProject. Connection
rs.Open myForm.RecordSo urce
rs.Find myfield & " = " & myValue
If rs.EOF Then rs.MoveFirst
myForm.Recordse t = rs.Source
Set rs = Nothing
RefreshForm_exi t:
Set rs = Nothing
Exit Function
RefreshForm_err :
MsgBox "Could not move to record." & vbCrLf & Err.Description ,
vbCritical + vbOKOnly, "Error in RefreshForm()"
Resume RefreshForm_exi t
End Function

--
regards,

Bradley

A Christian Response
http://www.pastornet.net.au/response

[Br]

Br@dley wrote:

Lyle Fairfield wrote:

"Br@dley" <br**@usenet.or g> wrote in news:d1thf.3241 $ea6.486@news-
server.bigpond. net.au:

Second issue:

The above searching method seemed to work until we encountered
larger recordsets. It seems that Access doesn't get all the data
immediately after you requery so the recordsetclone only contains a
small subset of records... thus the search routine fails to find
the record for higher ID values. If you trace through the code a
line at a time it works fine. Adding a pause loop in the code
doesn't work (and is a bad practice IMO).

This post addresses the problem you describe, I believe, but in
different circumstances.

http://groups.google.ca/group/comp.d...6d7ed38ae217d8

(I tried posting this through google groups but it not seem to go;
apologies if it appears more than once).

He's a generic function I wrote based on your post:

<>

Sorry, the correct code is:

Function RefreshForm(myF orm As Form, myField As String)
' sample call : RefreshForm Me, "EmployeeID "
On Error GoTo RefreshForm_err
Dim rs As ADODB.Recordset
Dim myvalue As Long
myvalue = myForm.Controls (myField).Value
Set rs = New ADODB.Recordset
rs.CursorLocati on = adUseClient
rs.ActiveConnec tion = CurrentProject. Connection
rs.Open myForm.RecordSo urce
rs.Find myField & " = " & myvalue
If rs.EOF Then rs.MoveFirst
Set myForm.Recordse t = rs
Set rs = Nothing
RefreshForm_exi t:
Set rs = Nothing
Exit Function
RefreshForm_err :
MsgBox "Could not move to record." & vbCrLf & Err.Description ,
vbCritical + vbOKOnly, "Error in RefreshForm()"
Resume RefreshForm_exi t
End Function

--
regards,

Bradley

A Christian Response
http://www.pastornet.net.au/response

[Br]

Br@dley wrote:
<>

He's a generic function I wrote based on your post:

<>

Sorry, the correct code is:

Function RefreshForm(myF orm As Form, myField As String)
' sample call : RefreshForm Me, "EmployeeID "
On Error GoTo RefreshForm_err
Dim rs As ADODB.Recordset
Dim myvalue As Long
myvalue = myForm.Controls (myField).Value
Set rs = New ADODB.Recordset
rs.CursorLocati on = adUseClient
rs.ActiveConnec tion = CurrentProject. Connection
rs.Open myForm.RecordSo urce
rs.Find myField & " = " & myvalue
If rs.EOF Then rs.MoveFirst
Set myForm.Recordse t = rs
Set rs = Nothing
RefreshForm_exi t:
Set rs = Nothing
Exit Function
RefreshForm_err :
MsgBox "Could not move to record." & vbCrLf & Err.Description ,
vbCritical + vbOKOnly, "Error in RefreshForm()"
Resume RefreshForm_exi t
End Function

Well this doesn't actually work yet.

Two issues:

- If user makes changes to the record and runs the code the changes
disappear. However running the code again, the changes reappear???

- the recordset is no longer editable once the code has run (i'm
probably missing something). I tried setting the UniqueTable value but
that didn't work.
--
regards,

Bradley

A Christian Response
http://www.pastornet.net.au/response

[Lyle Fairfield]

"Br@dley" <br**@usenet.or g> wrote in
news:d1******** ********@news-server.bigpond. net.au:

First issue:

When using ADPs you no longer have the ability to issue a me.refresh
to save the current record on a form (the me.refresh does a requery in
an ADP).

We usually do this before calling up another form or report that uses
some of the same data.

We came up with a work around that saves the current record's ID, does
a requery, then uses a recordsetclone to search for the ID and then
set the form's bookmark to the recordset clone's. This is the same
method we use for our search functions on our forms (without the
requery obviously).

This doesn't always work because of the second issue below.

How can you save the current record on a bound form in an ADP?
1. Press <Shift> <Enter>
or
2. From the Form Menu Click Records, Then Save Record
or
3. Run Code (from a button [UGH!]) or User Menu:
With DoCmd
.RunCommand acCmdSelectReco rd
.RunCommand acCmdSaveRecord
End With

I'm not sure I completely understand your second issue, but TTBOMK if
you run the code above the form record pointer will remain on the
correct record; resetting it will be unnecessary.
Second issue:

The above searching method seemed to work until we encountered larger
recordsets. It seems that Access doesn't get all the data immediately
after you requery so the recordsetclone only contains a small subset
of records... thus the search routine fails to find the record for
higher ID values. If you trace through the code a line at a time it
works fine. Adding a pause loop in the code doesn't work (and is a bad
practice IMO).

How can you make sure a recordsetclone contains all the records after
a requery?

For now I've had to implement a terrible practice... in code move the
form to the next record and then move back which causes the record to
be saved.

Using A2000, SQL2000

--
Lyle Fairfield

[Br]

Lyle Fairfield wrote:

"Br@dley" <br**@usenet.or g> wrote in
news:d1******** ********@news-server.bigpond. net.au:

First issue:

When using ADPs you no longer have the ability to issue a me.refresh
to save the current record on a form (the me.refresh does a requery
in an ADP).

We usually do this before calling up another form or report that uses
some of the same data.

We came up with a work around that saves the current record's ID,
does a requery, then uses a recordsetclone to search for the ID and
then set the form's bookmark to the recordset clone's. This is the
same method we use for our search functions on our forms (without the
requery obviously).

This doesn't always work because of the second issue below.

How can you save the current record on a bound form in an ADP?

1. Press <Shift> <Enter>
Just worked that one out :)

SendKeys "+{ENTER}"
or
2. From the Form Menu Click Records, Then Save Record
or
3. Run Code (from a button [UGH!]) or User Menu:
With DoCmd
.RunCommand acCmdSelectReco rd
.RunCommand acCmdSaveRecord
End With
That sounds better than SendKeys.

Why couldn't MS have made Me.Refresh do that?
I'm not sure I completely understand your second issue, but TTBOMK if
you run the code above the form record pointer will remain on the
correct record; resetting it will be unnecessary.

The remaining issue is if I open a second form I need to requery the
first form when it closes to reflect any changes on the second form.
After the requery I need to move back to what was the current record.
Second issue:

The above searching method seemed to work until we encountered larger
recordsets. It seems that Access doesn't get all the data immediately
after you requery so the recordsetclone only contains a small subset
of records... thus the search routine fails to find the record for
higher ID values. If you trace through the code a line at a time it
works fine. Adding a pause loop in the code doesn't work (and is a
bad practice IMO).

How can you make sure a recordsetclone contains all the records after
a requery?

For now I've had to implement a terrible practice... in code move the
form to the next record and then move back which causes the record to
be saved.

Using A2000, SQL2000

--
regards,

Bradley

A Christian Response
http://www.pastornet.net.au/response