Cannot figure out the Error Message

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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
 
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
 
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...
 
Back
Top