Cannot figure out the Error Message

C

CK

I have a table called tblCourses. In it are 2 fields called strCourseCode and
strCourseNum. Both of them are Text fields. In one of my forms to add
courses, I tried to check for duplicate strCourseCode and strCourseNum
combinations when the Save button is clicked. The code looks like this:

Dim rs As DAO.Recordset
Dim db As DAO.Database
Set db = CurrentDb
Set rs = db.OpenRecordset("tblCourses", dbOpenDynaset)
rs.FindFirst "[strCourseCode] = " & Me.[cboCourseCode] & " AND
[strCourseNum] _ = " & Me.[strCourseNum] & ""
If Not rs.NoMatch Then
MsgBox "The Course Code combination already exists!"

But when I clicked on the Save button, I got his error instead:

"The Microsoft Jet database does not recognize 'BIBBH' as a valid field name
or expression."

BIBBH was a course code I added to simulate a duplicate entry. Any advise is
appreciated. Thanks.
ck
 
D

Douglas J. Steele

Since it's text, it needs to be enclosed in quotes. Otherwise, Access thinks
it's referring to some other field in the query.

rs.FindFirst "[strCourseCode] = '" & Me.[cboCourseCode] & "' AND
[strCourseNum] = '" & Me.[strCourseNum] & "'"

Exagerated for clarity, that's

rs.FindFirst "[strCourseCode] = ' " & Me.[cboCourseCode] & " ' AND
[strCourseNum] = ' " & Me.[strCourseNum] & " ' "

(I'm assuming strCourseNum is a text field as well)

Other options to use include

rs.FindFirst "[strCourseCode] = """ & Me.[cboCourseCode] & """ AND
[strCourseNum] = """ & Me.[strCourseNum] & """"

which is

rs.FindFirst "[strCourseCode] = " " " & Me.[cboCourseCode] & " " " AND
[strCourseNum] = " " " & Me.[strCourseNum] & " " " "


or

rs.FindFirst "[strCourseCode] = " & Chr(34) & Me.[cboCourseCode] & Chr(34)
& " AND
[strCourseNum] = " & Chr(34) & Me.[strCourseNum] & Chr(34)

Chr(34) returns a quote symbol
 
C

CK

Thanks a million, Douglas!
ck

Douglas J. Steele said:
Since it's text, it needs to be enclosed in quotes. Otherwise, Access thinks
it's referring to some other field in the query.

rs.FindFirst "[strCourseCode] = '" & Me.[cboCourseCode] & "' AND
[strCourseNum] = '" & Me.[strCourseNum] & "'"

Exagerated for clarity, that's

rs.FindFirst "[strCourseCode] = ' " & Me.[cboCourseCode] & " ' AND
[strCourseNum] = ' " & Me.[strCourseNum] & " ' "

(I'm assuming strCourseNum is a text field as well)

Other options to use include

rs.FindFirst "[strCourseCode] = """ & Me.[cboCourseCode] & """ AND
[strCourseNum] = """ & Me.[strCourseNum] & """"

which is

rs.FindFirst "[strCourseCode] = " " " & Me.[cboCourseCode] & " " " AND
[strCourseNum] = " " " & Me.[strCourseNum] & " " " "


or

rs.FindFirst "[strCourseCode] = " & Chr(34) & Me.[cboCourseCode] & Chr(34)
& " AND
[strCourseNum] = " & Chr(34) & Me.[strCourseNum] & Chr(34)

Chr(34) returns a quote symbol

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)



CK said:
I have a table called tblCourses. In it are 2 fields called strCourseCode and
strCourseNum. Both of them are Text fields. In one of my forms to add
courses, I tried to check for duplicate strCourseCode and strCourseNum
combinations when the Save button is clicked. The code looks like this:

Dim rs As DAO.Recordset
Dim db As DAO.Database
Set db = CurrentDb
Set rs = db.OpenRecordset("tblCourses", dbOpenDynaset)
rs.FindFirst "[strCourseCode] = " & Me.[cboCourseCode] & " AND
[strCourseNum] _ = " & Me.[strCourseNum] & ""
If Not rs.NoMatch Then
MsgBox "The Course Code combination already exists!"

But when I clicked on the Save button, I got his error instead:

"The Microsoft Jet database does not recognize 'BIBBH' as a valid field name
or expression."

BIBBH was a course code I added to simulate a duplicate entry. Any advise is
appreciated. Thanks.
ck
Click to expand...
Click to expand...
 
Top