cant get past error 3251 for .findfirst (1 Viewer)

I get error 3251 (Operation is not supported by this type of Object) when on the following part of the code.

rec.FindFirst "[acct_num] = """ & acct_str & """"

The full version of the code is as follows:

Private Sub acct_num_AfterUpdate()
Dim rec As DAO.Recordset
Dim db As Database
Set db = CurrentDb()

acct_str = [acct_num].Text
MsgBox (acct_str)
strtable = "tbl_order_entry_buy"
Set rec = db.OpenRecordset(strtable)



rec.FindFirst "[acct_num] = """ & acct_str & """" -->get an error here


If rec.NoMatch = True Then
MsgBox ("No Match")
rec.Close
Set rec = Nothing
Exit Sub
Else
MsgBox ("Matching record found")
rec.Close
Set rec = Nothing
Exit Sub
End If

End Sub

Would appreciate help as to what could be wrong.

I came across this in the help file:

Locates the first, last, next, or previous record in a dynaset- or snapshot-type Recordset object

Click to expand...

Try changing your code to :

Try replacing;

rec.FindFirst "[acct_num] = """ & acct_str & """"

with

rec.FindFirst "[acct_num] ='" & acct_str & "'"

When qualifying (is that the correct expression???) text within a string you need to use single quote ' instead of double quote "

bretto said:

When qualifying (is that the correct expression???) text within a string you need to use single quote ' instead of double quote "

Click to expand...

No you don't. He is correct the first time.

How would you qualify a string with a single quote within it such as a person's name:


The above would crash. Using "" is the same as puttng one " within a string rather than act as a string delimiter.

The problem is most likely these lines:


With the recordset you are declaring it as DAO but you do not make reference to the Database object (I'm guessing you are using A2000 or above) which, when you are not explicit is an ADO object.

So, when you try to set an ADO object to a DAO concept (CurrentDB) the error thrown up will be that detailed in the title of the post.

All that needs changed is the line Dim db As Database to Dim db As DAO.Database

Appologies for clouding the issue maybe it's still too early in the morning

Going back to the " thing, while the original does work;

rec.FindFirst "[acct_num] ='" & strName & "'"

also works unless i'm losing it which is highly likely

It does work but I was saying that it does not always work as sometimes a string can contain a single quote so, in the example I gave:

"John O'Donnel" is more robust than 'John O'Donnel' as the latter would read 'John O' as being the string and would wonder what the hell the Donnel part at the end was about since it is not a VBA keyword.

Point well made, i'll go and stand in the corner for half an hour