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

Br@dley wrote in message <t_Nhf.3918$ea6.975@xxxxxxxxxxxxxxxxxxxxxxxxxx> :
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

.

Relevant Pages

  • RE: Jump ahead one month
    ... assigning a string to it, it can all be done in one line. ... Dim strSQL As String, strMessage As String ... Dim rst As Object ... MsgBox Err.Description, vbExclamation, "Error" ...
    (microsoft.public.access.gettingstarted)
  • Re: Email Subject Line
    ... Function ForecastnotificationFC(strOpCo As String, strEmail As String) As ... Dim strRecipient As String ... Call MsgBox(_ ...
    (microsoft.public.access.modulesdaovba)
  • Re: modify a macro with a macro
    ... Dim sOldMnthlyShtPWORD As String ... MsgBox "The old Monthly sheet password is blank. ...
    (microsoft.public.excel.programming)
  • Re: Opening email client (Outlook) from Form
    ... Dim strMailSubject As String ... ' MsgBox " Email message cancelled ", ... Should I format as a hyperlink, or leave as a text box? ...
    (microsoft.public.access.gettingstarted)
  • Re: Help with vlookup
    ... msgbox "no match" ... to convert the string that's returned with the Inputbox to a number. ... Dim Answer As Variant ...
    (microsoft.public.excel.programming)