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.org> 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.org> 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.org> 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(myForm 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.CursorLocation = adUseClient
rs.ActiveConnection = CurrentProject.Connection
rs.Open myForm.RecordSource
rs.Find myfield & " = " & myValue
If rs.EOF Then rs.MoveFirst
myForm.Recordset = rs.Source
Set rs = Nothing
RefreshForm_exit:
Set rs = Nothing
Exit Function
RefreshForm_err:
MsgBox "Could not move to record." & vbCrLf & Err.Description,
vbCritical + vbOKOnly, "Error in RefreshForm()"
Resume RefreshForm_exit
End Function

--
regards,

Bradley

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

[Br]

Br@dley wrote:

Lyle Fairfield wrote:

"Br@dley" <br**@usenet.org> 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(myForm 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.CursorLocation = adUseClient
rs.ActiveConnection = CurrentProject.Connection
rs.Open myForm.RecordSource
rs.Find myField & " = " & myvalue
If rs.EOF Then rs.MoveFirst
Set myForm.Recordset = rs
Set rs = Nothing
RefreshForm_exit:
Set rs = Nothing
Exit Function
RefreshForm_err:
MsgBox "Could not move to record." & vbCrLf & Err.Description,
vbCritical + vbOKOnly, "Error in RefreshForm()"
Resume RefreshForm_exit
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(myForm 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.CursorLocation = adUseClient
rs.ActiveConnection = CurrentProject.Connection
rs.Open myForm.RecordSource
rs.Find myField & " = " & myvalue
If rs.EOF Then rs.MoveFirst
Set myForm.Recordset = rs
Set rs = Nothing
RefreshForm_exit:
Set rs = Nothing
Exit Function
RefreshForm_err:
MsgBox "Could not move to record." & vbCrLf & Err.Description,
vbCritical + vbOKOnly, "Error in RefreshForm()"
Resume RefreshForm_exit
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.org> 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 acCmdSelectRecord
.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.org> 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 acCmdSelectRecord
.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

[Lyle Fairfield]

"Br@dley" <br**@usenet.org> wrote in news:Tdvhf.3304$ea6.227@news-
server.bigpond.net.au:

SendKeys "+{ENTER}"

No reputable or knowledgeable Developer uses or recommends SendKeys. This
is because we cannot be absolutely 100% sure what application or window may
receive the key that is sent, and disaster may occur.

--
Lyle Fairfield

[Br]

Lyle Fairfield wrote:

"Br@dley" <br**@usenet.org> wrote in news:Tdvhf.3304$ea6.227@news-
server.bigpond.net.au:

SendKeys "+{ENTER}"

No reputable or knowledgeable Developer uses or recommends SendKeys.
This is because we cannot be absolutely 100% sure what application or
window may receive the key that is sent, and disaster may occur.

Yep, I totally agree which is why I was extremely hesitant using it.
--
regards,

Bradley

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

[Lyle Fairfield]

"Br@dley" <br**@usenet.org> wrote in news:Tdvhf.3304$ea6.227@news-
server.bigpond.net.au:

Why couldn't MS have made Me.Refresh do that?

When we Save Souls do we use the term, "Refresh" or do we use the term
"Save".
Why would "Refresh" Save? It seems to me Refresh should synchronise the
Form with its recordsource in its most up to date state; if the current
record hasn't been saved, then I think its current values should not be
shown in a refresh; after all they have not been saved. Perhaps it seems to
you that refresh should do something else. We might have different
expectations for Me.Refresh.
But
Docmd.SaveRecord
?

even an Atheist and a Christian might agree on this.

--
Lyle Fairfield

[Br]

Lyle Fairfield wrote:

"Br@dley" <br**@usenet.org> wrote in news:Tdvhf.3304$ea6.227@news-
server.bigpond.net.au:

Why couldn't MS have made Me.Refresh do that?

When we Save Souls do we use the term, "Refresh" or do we use the term
"Save".
Why would "Refresh" Save? It seems to me Refresh should synchronise
the Form with its recordsource in its most up to date state; if the
current record hasn't been saved, then I think its current values
should not be shown in a refresh; after all they have not been saved.
Perhaps it seems to you that refresh should do something else. We
might have different expectations for Me.Refresh.
But
Docmd.SaveRecord
?

even an Atheist and a Christian might agree on this.

What I would naturally expect is the method to do the same thing that it
does in an MDB (or have an equivelant method instead of having to call
up a menu command).

.......

Anyways, I've solved it.

The changes weren't being saved. Obviously I needed to apply the
solution to the first issue... force a save of the record first.

The recordset returned was not updateable but that was because the
LockType was not set.

The following works well. Thanks to your post that inspired it.

Function RefreshForm(myForm 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.UniqueTable

'save current record first
DoCmd.RunCommand acCmdSelectRecord
DoCmd.RunCommand acCmdSaveRecord

'create new recordset based on form's recordsource
Set rs = New ADODB.Recordset
With rs
.ActiveConnection = CurrentProject.Connection
.CursorType = adOpenDynamic
.CursorLocation = adUseClient
.LockType = adLockOptimistic
.Source = myForm.RecordSource
.Open
End With

'move to the previously selected record on the form
rs.Find myField & " = " & myValue
If rs.EOF Then rs.MoveFirst
Set myForm.Recordset = rs
Set rs = Nothing
myForm.UniqueTable = myUniqueTable
RefreshForm_exit:
Set rs = Nothing
Exit Function
RefreshForm_err:
MsgBox "Could not move to record." & vbCrLf & Err.Description,
vbCritical + vbOKOnly, "Error in RefreshForm()"
Resume RefreshForm_exit
End Function

--
regards,

Bradley

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

[Br]

Br@dley wrote:
<>

The following works well. Thanks to your post that inspired it.

Function RefreshForm(myForm 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.UniqueTable

'save current record first
DoCmd.RunCommand acCmdSelectRecord
DoCmd.RunCommand acCmdSaveRecord

'create new recordset based on form's recordsource
Set rs = New ADODB.Recordset
With rs
.ActiveConnection = CurrentProject.Connection
.CursorType = adOpenDynamic
.CursorLocation = adUseClient
.LockType = adLockOptimistic
.Source = myForm.RecordSource
.Open
End With

'move to the previously selected record on the form
rs.Find myField & " = " & myValue
If rs.EOF Then rs.MoveFirst
Set myForm.Recordset = rs
Set rs = Nothing
myForm.UniqueTable = myUniqueTable
RefreshForm_exit:
Set rs = Nothing
Exit Function
RefreshForm_err:
MsgBox "Could not move to record." & vbCrLf & Err.Description,
vbCritical + vbOKOnly, "Error in RefreshForm()"
Resume RefreshForm_exit
End Function

Haha, I spoke too soon!

The code works wonderfully.....

BUT

When I then go to use our search function it generates an error. It
seems the RecordSet returned by the above function will not allow a
RecordsetClone to be created???
--
regards,

Bradley

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

[Lyle Fairfield]

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

Perhaps, you should post your updated code?

[Br]

Lyle Fairfield wrote:

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

Perhaps, you should post your updated code?

I thought I did?

Function RefreshForm(myForm 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.UniqueTable
DoCmd.RunCommand acCmdSelectRecord
DoCmd.RunCommand acCmdSaveRecord
Set rs = New ADODB.Recordset
With rs
.ActiveConnection = CurrentProject.Connection
.CursorLocation = adUseClient
.CursorType = adOpenKeyset
.LockType = adLockBatchOptimistic
.Source = myForm.RecordSource
.Open
End With
rs.Find myField & " = " & myValue
If rs.EOF Then rs.MoveFirst
Set myForm.Recordset = rs
Set rs = Nothing
myForm.UniqueTable = myUniqueTable
RefreshForm_exit:
Set rs = Nothing
Exit Function
RefreshForm_err:
MsgBox "Could not move to record." & vbCrLf & Err.Description,
vbCritical + vbOKOnly, "Error in RefreshForm()"
Resume RefreshForm_exit
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(ByVal 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.RecordsetClone

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

[RoyVidar]

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

Lyle Fairfield wrote:

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

Perhaps, you should post your updated code?

I thought I did?

Function RefreshForm(myForm 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.UniqueTable
DoCmd.RunCommand acCmdSelectRecord
DoCmd.RunCommand acCmdSaveRecord
Set rs = New ADODB.Recordset
With rs
.ActiveConnection = CurrentProject.Connection
.CursorLocation = adUseClient
.CursorType = adOpenKeyset
.LockType = adLockBatchOptimistic
.Source = myForm.RecordSource
.Open
End With
rs.Find myField & " = " & myValue
If rs.EOF Then rs.MoveFirst
Set myForm.Recordset = rs
Set rs = Nothing
myForm.UniqueTable = myUniqueTable
RefreshForm_exit:
Set rs = Nothing
Exit Function
RefreshForm_err:
MsgBox "Could not move to record." & vbCrLf & Err.Description, vbCritical +
vbOKOnly, "Error in RefreshForm()"
Resume RefreshForm_exit
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(ByVal 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.RecordsetClone

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

in stead of

Set MYRS = Me.RecordsetClone

2 - I also recall having some challenges with
getting form recordets updateable in 2000
version, but don't recall whether that was
only when working with mdb's or also when
testing ADP's (separate connection,
MSDataShape provider ...)

--
Roy-Vidar

[Lyle Fairfield]

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.

[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.RecordsetClone
.Find "Amount > 1000"
MsgBox .Collect(0)
End With

Perhaps, you should post your updated code?
I thought I did?

Function RefreshForm(myForm 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.UniqueTable
DoCmd.RunCommand acCmdSelectRecord
DoCmd.RunCommand acCmdSaveRecord
Set rs = New ADODB.Recordset
With rs
.ActiveConnection = CurrentProject.Connection
.CursorLocation = adUseClient
.CursorType = adOpenKeyset
.LockType = adLockBatchOptimistic
.Source = myForm.RecordSource
.Open
End With
rs.Find myField & " = " & myValue
If rs.EOF Then rs.MoveFirst
Set myForm.Recordset = rs
Set rs = Nothing
myForm.UniqueTable = myUniqueTable
RefreshForm_exit:
Set rs = Nothing
Exit Function
RefreshForm_err:
MsgBox "Could not move to record." & vbCrLf & Err.Description,
vbCritical + vbOKOnly, "Error in RefreshForm()"
Resume RefreshForm_exit
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(ByVal 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.RecordsetClone

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

in stead of

Set MYRS = Me.RecordsetClone

I'll try it when I'm back in the office.
2 - I also recall having some challenges with
getting form recordets updateable in 2000
version, but don't recall whether that was
only when working with mdb's or also when
testing ADP's (separate connection,
MSDataShape provider ...)

Yeah, there are a few "tricks" to get recordsets from SQL server 2000 to
be updatable in Access2000. (One is you have to manually go into
Enterprise Manager and enter an option - WITH METADATA - into the Views
definition, another is you can't use more than one table in a View).

I tried my database in A2003 and the code works fine.

All the documentation of the error message I get is to do with an
invalid reference (but I've reinstalled and manually registered the
DLLs).
--
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(myForm 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.UniqueTable
DoCmd.RunCommand acCmdSelectRecord
DoCmd.RunCommand acCmdSaveRecord
Set rs = New ADODB.Recordset
With rs
.ActiveConnection = CurrentProject.Connection
.CursorLocation = adUseClient
.CursorType = adOpenKeyset
.LockType = adLockBatchOptimistic
.Source = myForm.RecordSource
.Open
End With
rs.Find myField & " = " & myValue
If rs.EOF Then rs.MoveFirst
Set myForm.Recordset = rs
Set rs = Nothing
myForm.UniqueTable = myUniqueTable
RefreshForm_exit:
Set rs = Nothing
Exit Function
RefreshForm_err:
MsgBox "Could not move to record." & vbCrLf & Err.Description,
vbCritical +
vbOKOnly, "Error in RefreshForm()"
Resume RefreshForm_exit
End Function
Search code:
Private Sub reposition(ByVal 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.RecordsetClone

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.RecordsetClone
.Find "Amount > 1000"
MsgBox .Collect(0)
End With

Perhaps, you should post your updated code?

I thought I did?

Function RefreshForm(myForm 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.UniqueTable
DoCmd.RunCommand acCmdSelectRecord
DoCmd.RunCommand acCmdSaveRecord
Set rs = New ADODB.Recordset
With rs
.ActiveConnection = CurrentProject.Connection
.CursorLocation = adUseClient
.CursorType = adOpenKeyset
.LockType = adLockBatchOptimistic
.Source = myForm.RecordSource
.Open
End With
rs.Find myField & " = " & myValue
If rs.EOF Then rs.MoveFirst
Set myForm.Recordset = rs
Set rs = Nothing
myForm.UniqueTable = myUniqueTable
RefreshForm_exit:
Set rs = Nothing
Exit Function
RefreshForm_err:
MsgBox "Could not move to record." & vbCrLf & Err.Description,
vbCritical + vbOKOnly, "Error in RefreshForm()"
Resume RefreshForm_exit
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(ByVal 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.RecordsetClone

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

in stead of

Set MYRS = Me.RecordsetClone

It seems to have worked! Thanks champ.
<>

--
regards,

Bradley

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

[Lyle Fairfield]

"Br@dley" <br**@usenet.org> 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