RecordSet.FindFirst Syntax problem

[Robert Burns]

Hello there,

I am trying to perform a 'not in list' test on a contract number entered in a textbox by opening a recordset from a query and using the newly entered contract number in a findfirst operation, followed by a nomatch check. The jet dbengine does not recognize 'lngCRef' as a valid field name or reference in the findfirst operation - see below. Is it just poor syntax I'm using or something else?

Any help much appreciated.
Robert.

The code is as follows:

Private Sub txtContractID_BeforeUpdate(Cancel As Integer)
Dim lngCRef As Long, intReturn As Integer

Dim dbShip As DAO.Database
Dim rcdCRef As DAO.Recordset
Set dbShip = CurrentDb
Set rcdCRef = dbShip.OpenRecordSet("qryfrmContractsMain")

lngCRef = Me!txtContractID
rcdCRef.FindFirst "ContractID = lngCRef"
If rcdCRef.NoMatch Then
intReturn = MsgBox("There is no match for this Contract Ref. Do you " & _
"want to create a new contract?", vbCritical + vbYesNo, "New Contract Ref")
If intReturn = vbYes Then
DoCmd.OpenForm FormName:="frmContractsMain", _
DataMode:=acAdd, _
WindowMode:=acDialog
Else
Exit Sub
End If
End If

 

[Tim K.]

I guess the line below causes the problem.

...
rcdCRef.FindFirst "ContractID = lngCRef"
...

Change it to

...
rcdCRef.FindFirst "ContractID = " & lngCRef
...

 

[Robert Burns]

Thank you Tim,

Thats helped me a lot, I was being a bit clueless regarding my string variables.

Robert.