FindFirst problem

[Peter Bellamy]

I am using this for the first time and cannot gett he code to run,it errors with "Operation is not supported with this object"
I have seen that before and it has been a format problem, but I can't see it !!

The rec.seek line work perfectly ??

Public Function GetReturnRecNos(StrSerialNo As String)
Dim rec As Recordset
Dim n As Integer
On Error GoTo err_handler

' check for serial number = goods_aserial and bring back goods_recno, the Return Record nos
Set rec = CurrentDb().OpenRecordset("Goods")

rec.Index = "goods_aserialno"

If rec.NoMatch = True Then
MsgBox "No Match of " & StrSerialNo
Else
'rec.Seek "=", StrSerialNo
rec.FindFirst "goods_aserialno = " & StrSerialNo
Debug.Print "Serial No " & StrSerialNo & " in Return Record No " & rec("goods_recno")
End If

Can anyone help me please ?

 

[Galaxiom]

Put quotes around the string variable in the condition.

 rec.FindFirst "goods_aserialno = '" & StrSerialNo & "'"

The seek already knows what kind a value it is looking for from the data type of the Index.

 

[gemma-the-husky]

out of interest you cant use seek on linked tables - not sure about find operations offhand

is that the issue?

 

[Peter Bellamy]

Thanks for the replies.
The tables are linked but I have been working with a local table to debug the code.
However I use this code I have found on the web which 'enables' seek and index for linked tables and it works:

Public Function OpenForSeek(tablename As String) As Recordset
' Assume MS-ACCESS table
'************ Code Start ***************
'This code was originally written by Michel Walsh.
'It is not to be altered or distributed,
'except as part of an application.
'You are free to use it in any application,
'provided the copyright notice is left unchanged.
'
'Code Courtesy of
'Michel Walsh

Set OpenForSeek = DBEngine.Workspaces(0).OpenDatabase _
(Mid(CurrentDb().TableDefs(tablename).Connect, _
11), False, False, "").OpenRecordset(tablename, _
dbOpenTable)
End Function

I add: Set rec = OpenForSeek("Goods") and Index and Seek work with a linked Goods Table.

Unfortunately FindFirst is still not working even after changing it to:
rec.FindFirst "goods_aserialno = '" & StrSerialNo & "'"

Not sure what to do now ??

 

[gemma-the-husky]

not checked, but dont you have to select an index you wish to use first?

the reason this code works is because you are actually opening the table IN the back end - not trying ot use the linked tabledef/recordset directly

 

[Peter Bellamy]

I am no expert but I don't think you do, but that is taking an example from my reference book!
As it happens I do have the Index line, so the Seek will work, which I used to test with.

The full code is:
Public Function GetReturnRecNos(StrSerialNo As String)
Dim rec As Recordset
Dim n As Integer
On Error GoTo err_handler

' check for serial number = goods_aserial and bring back goods_recno, the Return Record nos
Set rec = CurrentDb().OpenRecordset("Goods")

Set rec = OpenForSeek("Goods") ' This function is required as .seek and .index do not work with linked tables

rec.Index = "goods_aserialno"

If rec.NoMatch = True Then
MsgBox "No Match of " & StrSerialNo
Else
'rec.Seek "=", StrSerialNo NOTE Commented out
Debug.Print "goods_aserialno = '" & StrSerialNo & "'"

rec.FindFirst "goods_aserialno = '" & StrSerialNo & "'"
Debug.Print "Serial No " & StrSerialNo & " in Return Record No " & rec("goods_recno")
End If

rec.Close

GetReturnRecNos_Exit:
Exit Function

err_handler:
MsgBox Err.Description, vbExclamation, "Error #: " & Err.number
Resume GetReturnRecNos_Exit

End Function

But it still does not work.
I think I will have to find another way

 

[gemma-the-husky]

I am no expert but I don't think you do, but that is taking an example from my reference book!
As it happens I do have the Index line, so the Seek will work, which I used to test with.

The full code is:
Public Function GetReturnRecNos(StrSerialNo As String)
Dim rec As Recordset
Dim n As Integer
On Error GoTo err_handler

' check for serial number = goods_aserial and bring back goods_recno, the Return Record nos
Set rec = CurrentDb().OpenRecordset("Goods")

Set rec = OpenForSeek("Goods") ' This function is required as .seek and .index do not work with linked tables

rec.Index = "goods_aserialno"

If rec.NoMatch = True Then
MsgBox "No Match of " & StrSerialNo
Else
'rec.Seek "=", StrSerialNo NOTE Commented out
Debug.Print "goods_aserialno = '" & StrSerialNo & "'"

rec.FindFirst "goods_aserialno = '" & StrSerialNo & "'"
Debug.Print "Serial No " & StrSerialNo & " in Return Record No " & rec("goods_recno")
End If

rec.Close

GetReturnRecNos_Exit:
Exit Function

err_handler:
MsgBox Err.Description, vbExclamation, "Error #: " & Err.number
Resume GetReturnRecNos_Exit

End Function

But it still does not work.
I think I will have to find another way

I realise what it is now

setting an index, and using seek mthods is faster than using find methods - but index/seek dont work on linked tables - so you have to open the back end directly - as your code does.

If you are using seek, then the time difference depends on the size of the recordset and for small datasets it wont matter -

Now - i think your problem is that you are setting an index - and immediately testing for no match

you have to do it the other way round

rst.index
rst.seek
if rst.momatch then
else

end if


---------
now aslo if you are using seek - then you dont need to use findfirst etc

seek is already searching records based on a selected index, and you can now step through the records with .movenext etc - which just advance on the current index

----------
so this segment will set an index, find a match, and then move through all the records until eof

rst.index
rst.seek
if rst.nomatch then
   msgbox("Nothing found")
else 
    while not rst.eof
       'do something
       rst.movenext
    wend
end if

hope that makes sense

 

[Peter Bellamy]

Dave, thanks for your suggestions.
The current code has no errors with the Seek line.
It only errors when I change to FindFirst.

However I prefer a solution based on Seek as it will be faster as you say.
I will now build on that.

Thanks again

Peter