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

[Br]

Lyle Fairfield wrote:

I'm still not clear about the sequence of your calls. Do you set rs to
nothing before you try to use RecordSetClone? I think this is unwise.
I am strongly of the opinion that one never has to set an ADO
recordset to nothing; others disagree, but I have not done this in
(at least) millions of code calls without any problem whatever.
ADO objects are not DAO objects. One should not treat them as one
treats DAO objects. If they were identical with DAO objects then there
would be no point in using them.
The main form has a button that calls up a second form.

Because of the limitation in A2000/SQL2000 you can only edit one of the
tables in a view. To get around this a second form is opened to edit the
details of the second table. (I didn't design the system but am now
maintaining it - in another version the developer has made the main form
unbound and calls up two secondary forms to edit the detail to get
around some of the issues).

Before the second form is opened some code is run to force the current
record to save.

When the form is closed we need to requery the first form. This is where
my code that does a requery and then moves back to the record we were on
is called.

This all works fine now and with the new code we get around the problem
of opening a recordsetclone and only getting the first 50 or so records.

However, on the main form there are some search routines (select a name
from a list returning the person's ID). This is where the recordsetclone
code is used to move to the searched record.

This code works usually, but if you try and run it after the "refresh"
code then I get an "ActiveX cannot create object" error.

Requery and move back to record code:
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
Dim myUniqueTable As String
myValue = myForm.Controls (myField).Value
myUniqueTable = myForm.UniqueTa ble
DoCmd.RunComman d acCmdSelectReco rd
DoCmd.RunComman d acCmdSaveRecord
Set rs = New ADODB.Recordset
With rs
.ActiveConnecti on = CurrentProject. Connection
.CursorLocation = adUseClient
.CursorType = adOpenKeyset
.LockType = adLockBatchOpti mistic
.Source = myForm.RecordSo urce
.Open
End With
rs.Find myField & " = " & myValue
If rs.EOF Then rs.MoveFirst
Set myForm.Recordse t = rs
Set rs = Nothing
myForm.UniqueTa ble = myUniqueTable
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
Search code:
Private Sub reposition(ByVa l findcode As Long)

Dim CRITERIA As String
Dim MYRS As ADODB.Recordset

If IsNull(findcode ) Then
MsgBox "YOU HAVE NOT MADE A SELECTION"
Exit Sub
End If

'Error occurs on this line. "AciveX component can't create object"
Set MYRS = Me.RecordsetClo ne

CRITERIA = "[Personnel_No]=" & findcode
MYRS.Find CRITERIA
If MYRS.EOF Then
MsgBox "COULD NOT FIND THE EMPLOYEE: " & findcode
Else
Me.Bookmark = MYRS.Bookmark
End If
Set MYRS = Nothing

End Sub

--
regards,

Bradley

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

[Br]

RoyVidar wrote:

Br@dley wrote in message
<t_************ ****@news-server.bigpond. net.au> :

Lyle Fairfield wrote:

I don't have any problem with:
With Me.RecordsetClo ne
.Find "Amount > 1000"
MsgBox .Collect(0)
End With

Perhaps, you should post your updated code?

I thought I did?

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
Dim myUniqueTable As String
myValue = myForm.Controls (myField).Value
myUniqueTable = myForm.UniqueTa ble
DoCmd.RunComman d acCmdSelectReco rd
DoCmd.RunComman d acCmdSaveRecord
Set rs = New ADODB.Recordset
With rs
.ActiveConnecti on = CurrentProject. Connection
.CursorLocation = adUseClient
.CursorType = adOpenKeyset
.LockType = adLockBatchOpti mistic
.Source = myForm.RecordSo urce
.Open
End With
rs.Find myField & " = " & myValue
If rs.EOF Then rs.MoveFirst
Set myForm.Recordse t = rs
Set rs = Nothing
myForm.UniqueTa ble = myUniqueTable
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
The code on the form that does the search is (I didn't write it...).
It works before the above code is run.

Private Sub reposition(ByVa l findcode As Long)

Dim CRITERIA As String
Dim MYRS As ADODB.Recordset

If IsNull(findcode ) Then
MsgBox "YOU HAVE NOT MADE A SELECTION"
Exit Sub
End If

'Error occurs on this line. "AciveX component can't create object"
Set MYRS = Me.RecordsetClo ne

CRITERIA = "[Personnel_No]=" & findcode
MYRS.Find CRITERIA
If MYRS.EOF Then
MsgBox "COULD NOT FIND THE EMPLOYEE: " & findcode
Else
Me.Bookmark = MYRS.Bookmark
End If
Set MYRS = Nothing

End Sub

There are a lot of things I do not understand
with this, so I hope you'll excuse me if some
of this is only a shot in the dark

1 - I seem to recall some anomalities whith
recordsetclone on ADO form recordsets in the
2000 version - could you try just using the
.clone method of the form recordset in
stead, and see if that changes anything?

Set MYRS = Me.Recordset.Cl one

in stead of

Set MYRS = Me.RecordsetClo ne

It seems to have worked! Thanks champ.
<>

--
regards,

Bradley

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

[Lyle Fairfield]

"Br@dley" <br**@usenet.or g> wrote in news:V5iif.5225 $ea6.362@news-
server.bigpond. net.au:

Before the second form is opened some code is run to force the current
record to save.

When the form is closed we need to requery the first form. This is where
my code that does a requery and then moves back to the record we were on
is called.

Threads like this remind me that I should just shut up when I'm puzzled
about problems that appear here.
It seems that my way of working is not so similar to that of many others.
For instance
1. the notion of editing (or trying to edit) two tables from one form fills
me with fear;
2. when I get to the point where some code dealing with user interaction
(not calculating code) is involved, long or error prone, I delete the code
and start over; this is based on my strong belief that the Access GUI is
and should be kept simple; when a procedure in a form's module exceeds ten
lines it's suspect;
3. I've never used a VIEW as the recordsource for a form;
4. I use the latest versions of whatever technologies are available to me.

--
Lyle Fairfield