Run-time error 3709 - Open recordset

[Anita]

I am getting run-time error 3709 when this code runs ... it happens when VBA
tries to execute the .Open line. Any idea what I'm doing wrong?

Dim conEntity As ADODB.Connection
Dim rstEntity As ADODB.Recordset
Set conEntity = CurrentProject.Connection
Set rstEntity = New Recordset

With rstEntity
'See if an Entity has already been created for this Person ID
.Open "Select * from Entity WHERE [Person ID] = intPersonID"
.ActiveConnection = conEntity
.CursorType = adOpenForwardOnly
.LockType = adLockOptimistic

'If there's no Entity associated with this person, create one
If .EOF And .BOF Then
.AddNew
![Person ID] = intPersonId
![Entity Type] = strPersonType
End If
![Entity] = strFullName
.Update
End With

 

[Douglas J. Steele]

Queries don't know anything about variables.

If Person ID is numeric, use:

Open "Select * from Entity WHERE [Person ID] = " & intPersonID

If it's text, use

Open "Select * from Entity WHERE [Person ID] = '" & intPersonID & "'"

or

Open "Select * from Entity WHERE [Person ID] = " & Chr$(34) & intPersonID &
Chr$(34)

 

[Anita]

A thousand thank yous -- problem solved!

Queries don't know anything about variables.

If Person ID is numeric, use:

Open "Select * from Entity WHERE [Person ID] = " & intPersonID

If it's text, use

Open "Select * from Entity WHERE [Person ID] = '" & intPersonID & "'"

or

Open "Select * from Entity WHERE [Person ID] = " & Chr$(34) & intPersonID &
Chr$(34)



--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

I am getting run-time error 3709 when this code runs ... it happens when
VBA
tries to execute the .Open line. Any idea what I'm doing wrong?

Dim conEntity As ADODB.Connection
Dim rstEntity As ADODB.Recordset
Set conEntity = CurrentProject.Connection
Set rstEntity = New Recordset

With rstEntity
'See if an Entity has already been created for this Person ID
.Open "Select * from Entity WHERE [Person ID] = intPersonID"
.ActiveConnection = conEntity
.CursorType = adOpenForwardOnly
.LockType = adLockOptimistic

'If there's no Entity associated with this person, create one
If .EOF And .BOF Then
.AddNew
![Person ID] = intPersonId
![Entity Type] = strPersonType
End If
![Entity] = strFullName
.Update
End With