DAO-Error using the FindFirst Method

[Guest]

I am getting an error using the FindFirst Method.
I am using Access 2007, but am using the old technique of DAO. Does the
database need to be saved in an older version, and not the accdb 07 version
in order for the older DAO to work. Here is my code-

1Private Sub cboCounty_AfterUpdate()
2 Dim strCounty As String
3 Dim strCriteria As String
4 Dim rst As DAO.Recordset
5
6 strCounty = Me.cboCounty.Text
7
8 strCriteria = "[Prospect]= " & strProspect
9 Set rs = Me.RecordsetClone
10
11 rst.FindFirst (strCriteria)
12 If rs.NoMatch Then
13 MsgBox "Record not found"
14 Else
15 Me.Bookmark = rs.Bookmark
16 End If
17 End Sub

Line 11 is the problem
Error: Run time error '3077'
Syntax error (missing operator) in expression

Can someone help, please, Thanks

 

[Jason Lepack]

rs? or rst? Which is it? Take your pick and make all of your
variables the same?

Cheers,
Jason Lepack

 

[Guest]

Okay, okay, I fixed the variable identifier to be consistent, although, I
think I have pinpointed the problem. I think it is line 8. How does the db
engine handle quotes within a literal string, do you have to double up on the
quotes, i.e.

strProspect = cboProspect.Text
------------------------------------
Do I have to say [PROSPECT] =, or can I leave those [] out and just say,
PROSPECT = ,

Oh, and by the way strProspect is declared at the top using
Public strProspect as String,
meaning it is scoped for all events for the form in question, right?





--
Dan Rosa

rs? or rst? Which is it? Take your pick and make all of your
variables the same?

Cheers,
Jason Lepack

I am getting an error using the FindFirst Method.
I am using Access 2007, but am using the old technique of DAO. Does the
database need to be saved in an older version, and not the accdb 07 version
in order for the older DAO to work. Here is my code-

1Private Sub cboCounty_AfterUpdate()
2 Dim strCounty As String
3 Dim strCriteria As String
4 Dim rst As DAO.Recordset
5
6 strCounty = Me.cboCounty.Text
7 'strProspect is coming from the cboProspect_AfterUpdate event, how do I >> 7a 'string these along
8 strCriteria = "[Prospect]= " & strProspect
9 Set rs = Me.RecordsetClone
10
11 rst.FindFirst (strCriteria)
12 If rs.NoMatch Then
13 MsgBox "Record not found"
14 Else
15 Me.Bookmark = rs.Bookmark
16 End If
17 End Sub

Line 11 is the problem
Error: Run time error '3077'
Syntax error (missing operator) in expression

Can someone help, please, Thanks

 

[Jason Lepack]

You're right in the fact that the problem is in line 8!

strCriteria = "[Prospect]= " & strProspect

Now the problem is that Prospect is a text field (right?) so you need
to tell your firndfirst that you're looking for a text value.

Change the line to:
strCriteria = "[Prospect]='" & strProspect & "'"

That way if strProspect = Bob then strCriteria will evaluate to:
[Prospect]='Bob'

Cheers,
Jason Lepack

Okay, okay, I fixed the variable identifier to be consistent, although, I
think I have pinpointed the problem. I think it is line 8. How does the db
engine handle quotes within a literal string, do you have to double up on the
quotes, i.e.

strProspect = cboProspect.Text
------------------------------------
Do I have to say [PROSPECT] =, or can I leave those [] out and just say,
PROSPECT = ,

Oh, and by the way strProspect is declared at the top using
Public strProspect as String,
meaning it is scoped for all events for the form in question, right?

--
Dan Rosa

rs? or rst? Which is it? Take your pick and make all of your
variables the same?

Cheers,
Jason Lepack

I am getting an error using the FindFirst Method.
I am using Access 2007, but am using the old technique of DAO. Does the
database need to be saved in an older version, and not the accdb 07 version
in order for the older DAO to work. Here is my code-
1Private Sub cboCounty_AfterUpdate()
2 Dim strCounty As String
3 Dim strCriteria As String
4 Dim rst As DAO.Recordset
5
6 strCounty = Me.cboCounty.Text
7 'strProspect is coming from the cboProspect_AfterUpdate event, how do I >> 7a 'string these along
8 strCriteria = "[Prospect]= " & strProspect
9 Set rs = Me.RecordsetClone
10
11 rst.FindFirst (strCriteria)
12 If rs.NoMatch Then
13 MsgBox "Record not found"
14 Else
15 Me.Bookmark = rs.Bookmark
16 End If
17 End Sub
Line 11 is the problem
Error: Run time error '3077'
Syntax error (missing operator) in expression
Can someone help, please, Thanks

- Show quoted text -

 

[Jason Lepack]

Ok you're right in where the problem occurs.

However the problem is that Prospect is a text field (right?). So you
need to put quotes around the string you are looking for. Change:

8 strCriteria = "[Prospect]= " & strProspect

to:

8 strCriteria = "[Prospect]='" & strProspect & "'"

So that if strprospect = Bob then strCriteria evaluates to:
[Prospect]='Bob'

That should fix you right up.

Cheers,
Jason Lepack

Okay, okay, I fixed the variable identifier to be consistent, although, I
think I have pinpointed the problem. I think it is line 8. How does the db
engine handle quotes within a literal string, do you have to double up on the
quotes, i.e.

strProspect = cboProspect.Text
------------------------------------
Do I have to say [PROSPECT] =, or can I leave those [] out and just say,
PROSPECT = ,

Oh, and by the way strProspect is declared at the top using
Public strProspect as String,
meaning it is scoped for all events for the form in question, right?

--
Dan Rosa

rs? or rst? Which is it? Take your pick and make all of your
variables the same?

Cheers,
Jason Lepack

I am getting an error using the FindFirst Method.
I am using Access 2007, but am using the old technique of DAO. Does the
database need to be saved in an older version, and not the accdb 07 version
in order for the older DAO to work. Here is my code-
1Private Sub cboCounty_AfterUpdate()
2 Dim strCounty As String
3 Dim strCriteria As String
4 Dim rst As DAO.Recordset
5
6 strCounty = Me.cboCounty.Text
7 'strProspect is coming from the cboProspect_AfterUpdate event, how do I >> 7a 'string these along
8 strCriteria = "[Prospect]= " & strProspect
9 Set rs = Me.RecordsetClone
10
11 rst.FindFirst (strCriteria)
12 If rs.NoMatch Then
13 MsgBox "Record not found"
14 Else
15 Me.Bookmark = rs.Bookmark
16 End If
17 End Sub
Line 11 is the problem
Error: Run time error '3077'
Syntax error (missing operator) in expression
Can someone help, please, Thanks

- Show quoted text -