adodb and .find

[stuartam]

hi guys, i have a module that is giving me a big pain in the ass

what i want is that when you press a button on a form it looks to see what value is in a combo box and then pass's this to the edit module to find that record and then update 2 fields.

i can get it to update the record if i put the number in the .find statement but if i try to pass it the value it give's me an error:

'runtime error 3001'
'arguments are of wrong type......'

i use this to call the function ( passing it the value directly so i can see whats not working )

Call statsupd(2)

and here is the function

Public Function statsupd(vbarecord As String)


' open a connection to the connection object
Set Cn = CurrentProject.Connection
' initialise the recordset object
Set rs = New ADODB.Recordset
' using the recordset object
With rs
.Open "tbl_personal_stats", Cn, adOpenStatic, adLockOptimistic ' open it
.Find User_Stats_an = vbarecord
.Fields("Pack_posted") = 99
.Fields("Ses_posted") = 44
.Update ' update the table
.Close ' close the recordset connection
End With

End Function

now this bit works but i have to put the .find value into the function

Public Function statsupd()


' open a connection to the connection object
Set Cn = CurrentProject.Connection
' initialise the recordset object
Set rs = New ADODB.Recordset
' using the recordset object
With rs
.Open "tbl_personal_stats", Cn, adOpenStatic, adLockOptimistic ' open it
.Find "User_Stats_an = 2"
.Fields("Pack_posted") = 99
.Fields("Ses_posted") = 44
.Update ' update the table
.Close ' close the recordset connection
End With

End Function

can anyone shead light on why this isn't working.

best regards

 

[mresann]

Instead of

.Find User_Stats_an = vbarecord

Try

.Find "User_Stats_an ='" & vbarecord & "'"

Since vbarecord is a string object (as to your function declaration) you need to put the string quotes around the variable. The whole .Find statement must be in the form of a string as well.

 

[stuartam]

ah, thanks for the help

best regards