RecordsetClone Find failing

[groengoen]

When I use the following code I get the message "Object doesn't support this property or method". What can I do about this?

Private Sub ID1_GotFocus()
' Find the next ID1 to be used if a new one is being input
Dim rst As Recordset

Dim strSearchID1 As String
Dim GroupUpperLim As String
Dim UpperLim As Integer
Dim strFirstChr As String

If IsNull(Me.ID1) Then
strFirstChr = Mid(Me.Name1, 1, 1)
strSearchID1 = strFirstChr & "*"
Me.RecordsetClone.Sort = "ID1"
Me.RecordsetClone.Find "ID1 LIKE " & strSearchID1
If Me.RecordsetClone.EOF Then
MsgBox "Record not found"
Else
Do While Mid(Me.RecordsetClone(ID1), 1, 1) = Mid(Me.Name1, 1, 1)
GroupUpperLim = Int(Mid(Me.RecordsetClone(ID1), 2, 3))
Loop
UpperLim = GroupUpperLim + 1
Me.ID1 = strFirstChr & Str(UpperLim)
End If
Me.RecordsetClone.Close
End If
End Sub

 

[groengoen]

when I try to define "rst" as a recordsetclone as follows, I get the message
"Run time error '13', Type mismatch"

dim rst as RecordSet

If IsNull(Me.ID1) Then
strFirstChr = Mid(Me.Name1, 1, 1)
strSearchID1 = strFirstChr & "*"
Set rst = Me.RecordsetClone
rst.Sort = "ID1"
rst.Find "ID1 LIKE " & strSearchID1
If rst.EOF Then

 

[groengoen]

I wonder if this is something to do with the version of VBA that I am using. I have version 9108, which is VB 6.3 1987-2001.

 

[groengoen]

It is ok, I seem to have got around it by not defining the RecordsetClone as a recordset object and only using the verbs available for this as follows:

Private Sub ID1_GotFocus()
' Find the next ID1 to be used if a new one is being input
Dim rst As ADODB.Recordset

Dim strSearchID1 As String
Dim intCurrNum As Integer
Dim intNextNum As Integer
Dim strFirstChr As String
Dim strRecAlph As String
Dim strRecNum As String

If IsNull(Me.ID1) Then
strFirstChr = Mid(Me.Name1, 1, 1)
strSearchID1 = strFirstChr & "*"
With Me.RecordsetClone
.Sort = "ID1"
.MoveFirst

If (.RecordCount) Then
Do While Not .EOF
If IsNull(!ID1) = False Then
strRecAlph = Mid(!ID1, 1, 1)
strRecNum = Mid(!ID1, 2, 3)
If strRecAlph = strFirstChr Then
intCurrNum = Int(strRecNum)
End If
End If
.MoveNext
Loop
intNextNum = intCurrNum + 1
Me.ID1 = strFirstChr & Str(intNextNum)
End If
.Close
End With
End If
End Sub

 

[Jon K]

when I try to define "rst" as a recordsetclone as follows, I get the message
"Run time error '13', Type mismatch"

dim rst as RecordSet

Set rst = Me.RecordsetClone

Since you are using ADO, rst is an ADO recordset, but Me.RecordsetClone is always a DAO recordset. That's why you got the Type Mismatch error.
.