findfirst problem

[W]

Hi all,

I have this piece of code which gives me an error : 3072 unknown function
name when I execute rst2.FindFirst cKey.
In immediate mode my cKey seems to be correct.



Sub UpdateCustomer()
Dim cCustomer As String, cNameCustomer As String, cKey As String
Dim db As DAO.Database, rst As Recordset, rst2 As Recordset, wsp As Workspace
Set wsp = DBEngine.Workspaces(0)
Set db = CurrentDb
Set rst2 = db.OpenRecordset("Customers", dbOpenSnapshot, dbReadOnly)
rst2.MoveLast: rst2.MoveFirst
Set rst = db.OpenRecordset("MyOtherTable", dbOpenDynaset)
rst.MoveLast: rst.MoveFirst
Do While Not rst.EOF
cCustomer = Left$(rst.Fields("Customer"), 8)
cKey = "Left$(rst2.Fields(" "Customer" "), 8) = " " " & cCustomer & " "
" "
rst2.FindFirst cKey
…
Loop
rst2.Close
rst.Close
Set rst2 = Nothing: Set rst = Nothing
End Sub

Where do I go wrong ?

Thanks in advance,

W

 

[RoyVidar]

W pretended :

Hi all,

I have this piece of code which gives me an error : 3072 unknown
function name when I execute rst2.FindFirst cKey.
In immediate mode my cKey seems to be correct.



Sub UpdateCustomer()
Dim cCustomer As String, cNameCustomer As String, cKey As String
Dim db As DAO.Database, rst As Recordset, rst2 As Recordset, wsp As
Workspace Set wsp = DBEngine.Workspaces(0)
Set db = CurrentDb
Set rst2 = db.OpenRecordset("Customers", dbOpenSnapshot, dbReadOnly)
rst2.MoveLast: rst2.MoveFirst
Set rst = db.OpenRecordset("MyOtherTable", dbOpenDynaset)
rst.MoveLast: rst.MoveFirst
Do While Not rst.EOF
cCustomer = Left$(rst.Fields("Customer"), 8)
cKey = "Left$(rst2.Fields(" "Customer" "), 8) = " " " & cCustomer
& " " " "
rst2.FindFirst cKey
…
Loop
rst2.Close
rst.Close
Set rst2 = Nothing: Set rst = Nothing
End Sub

Where do I go wrong ?

Thanks in advance,

W

Would it make any difference if you qualified the declarations?

Dim db As DAO.Database, rst As DAO.Recordset, rst2 As DAO.Recordset...

 

[W]

Hi mr Vidar,

I've never done like that and I do not think it would help. Moreover I
already tried former version of this code, which worked out fine, but I
forgot to save it and since then I do not find the correct syntax (which I
think is in the definition of cKey).

Thanks for your suggestion anyhow; I'll give it a try.

W

 

[Marshall Barton]

I have this piece of code which gives me an error : 3072 unknown function
name when I execute rst2.FindFirst cKey.
In immediate mode my cKey seems to be correct.

Sub UpdateCustomer()
Dim cCustomer As String, cNameCustomer As String, cKey As String
Dim db As DAO.Database, rst As Recordset, rst2 As Recordset, wsp As Workspace
Set wsp = DBEngine.Workspaces(0)
Set db = CurrentDb
Set rst2 = db.OpenRecordset("Customers", dbOpenSnapshot, dbReadOnly)
rst2.MoveLast: rst2.MoveFirst
Set rst = db.OpenRecordset("MyOtherTable", dbOpenDynaset)
rst.MoveLast: rst.MoveFirst
Do While Not rst.EOF
cCustomer = Left$(rst.Fields("Customer"), 8)
cKey = "Left$(rst2.Fields(" "Customer" "), 8) = " " " & cCustomer & " "
" "
rst2.FindFirst cKey
…
Loop

It would help if you had posted what you thought was correct
in the immediate window. Either that was not correct or you
did not post the same code you used.

You have extra spaces all over the place and an incorrect
field reference. Try changing to:

cKey = "Left$(Customer, 8) = """ & cCustomer & """"

Note that those MoveLast:MoveFirst lines are not doing
anything but wasting time. You are also declaring and
setting a workspace variable, but you never use it.

 

[RoyVidar]

W was thinking very hard :

Hi mr Vidar,

I've never done like that and I do not think it would help. Moreover
I already tried former version of this code, which worked out fine,
but I forgot to save it and since then I do not find the correct
syntax (which I think is in the definition of cKey).

Thanks for your suggestion anyhow; I'll give it a try.

W

Ah, yes, sorry.

The criterion for the .FindFirst will probably need to include the
field name of the field you wish to search on

cCustomer = Left$(rst.Fields("Customer"), 8)
cKey = "Left(Customer), 8) = '" & cCustomer & "'"

The latter can probably be written like this

cKey = "Left(Customer), 8) = """ & cCustomer & """"

if there's a chance it can contain a single quote.

Explicit declaration, as I suggested first, is probably not part of
this challenge, but it is not uncommon to have a reference to both
ADO and DAO. Both of these have a recordset object. Without any
qualification, Access will pick the one "higher" up in the reference
list. This can/will fail. I find it safer to be explicit.

 

[W]

Thanks for your answer.

I put those extra spaces for better visibility.
When I use the snippet of code both of you sent me I keep on getting te same
error.

In direct mode I get :

debug.print cKey :

left$(Customer) = "00000000"
00000000 being the first key of that table

there are no more parenthesis around this function, so the result of the
debug.print is like you see it

The error subsists however, so I got stuck.

I removed the movefirst and movelast; I thought they were necessary.

W

 

[Powderfinger]

How about :

cKey ="[Customer] Like '" & cCustomer & "*'"

 

[Marshall Barton]

In the future, be sure to post a Copy/Paste of relevant
line(s) of code along with the result.

Somewhere along the way you lost the 8 in the Left function.

 

[W]

I lost the 8, but then of course I retyped what I had on the screen.
I have no answer to my problem though. I keep getting the same error,
namely :

3072 unknown function name.

I checked my references, which seem to be OK.

I think it has something to do with the syntax, maybe the quotes ?

W

 

[W]

I thought

rst.fields("Customer") was correct syntax ?

W

 

[Marshall Barton]

Don't retype when Copy/Paste can be used. Neither one of us
wants to waste time going back and forth debugging typos
when we could be working on the real problem.

I still need to see a Copy/Paste of the relevant lines of
code.

 

[W]

I eventually decided to add an extra field in which I put the left$(Customer,
8) of the particular key field. I then did a findfirst on this field and all
went smooth. So, IMHO, the problem resides in the syntax or the call of the
left$ function.

Thanks for your helping hand anyhow.

W

Don't retype when Copy/Paste can be used. Neither one of us
wants to waste time going back and forth debugging typos
when we could be working on the real problem.

I still need to see a Copy/Paste of the relevant lines of
code.
--
Marsh
MVP [MS Access]

I lost the 8, but then of course I retyped what I had on the screen.
I have no answer to my problem though. I keep getting the same error,
namely :

3072 unknown function name.

I checked my references, which seem to be OK.

I think it has something to do with the syntax, maybe the quotes ?