How to handle an empty DAO.RecordSet?

[Richard Hollenbeck]

I want to test for the existance of an activity discription
within a college course for a gradebook program before adding a
new activity to that course. This should prevent duplicate
activities in that course. In other words, if the activity does
not exist the program will continue but if it does exist it will
stop the user from adding that activity again. But if it does
not exist I get an error saying that no record exists. I don't
need that error. If the user types the name of an already
existing activity name, I want to alert the user that this
activity already exists. Otherwise they can continue. How do I
get rid of the error? Here's the beginning of my code with
superfluous details omitted:

Private Sub cmdOK_Click()
On Error GoTo Err_cmdOK_Click

Dim strSQL As String
Dim db As DAO.Database
Dim rs As DAO.Recordset

strSQL = "SELECT courses.courseCode,
activities.activityDescription FROM (courses INNER JOIN groups ON
courses.courseCode = groups.courseCode) INNER JOIN activities ON
groups.groupID = activities.groupID WHERE (((courses.courseCode)=
'" & [Forms]![frmSelectCourse]![cboSelectCourse] & "') AND
((activities.activityDescription)= '" & txtAD & "'))"
Set db = CurrentDb()
Set rs = db.OpenRecordset(strSQL, dbOpenDynaset)
rs.MoveLast
rs.MoveFirst

Then do one thing if the rs.RecordCount <1 and another thing if
the rs.RecordCount >0.

I just want to get rid of the "No Record" error, because it's not
an error in this case. Any ideas?

Thanks.

Rich Hollenbeck

 

[Brendan Reynolds]

You only need to do a .MoveLast if you want an actual count of the number of
records. You don't need an actual count, you just need to know whether the
count is anything other than zero. So just open the recordset and check
rs.Recordcount <> 0 - no .MoveLast, no error message.

 

[Richard Hollenbeck]

So simple! Thanks! I was working on setting up some elaborate
coding in the error handler like:
"If Err.Number = 3021 then Resume NoRecord" then puting the
"NoRecord:" label right after that recordset. Now all that isn't
necessary. Thank you very much. This cleans up my code nicely.

"Brendan Reynolds" <anonymous at discussions dot microsoft dot
com> wrote in message

You only need to do a .MoveLast if you want an actual count of the number of
records. You don't need an actual count, you just need to know whether the
count is anything other than zero. So just open the recordset and check
rs.Recordcount <> 0 - no .MoveLast, no error message.

(snipped)

 

[David C. Holley]

While NOT rs.EOF
wend

or If rs.EOF

Also, Didn't your mother ever tell you to clean up after yourself?
Be certain to

close anything you open rs.Close

and throw out the trash when you're finished

Set rs = Nothing
Set db = Nothing

Hmmm...maybe everything I learned about programming, I learned cleaning
up my house.

David h

 

[david epsom dot com dot au]

close anything you open rs.Close

Son, the world has overtaken you....

C# has automatic memory management and garbage
collection, just like VB.Net, VB, VBA, and Access.

(david)

While NOT rs.EOF
wend

or If rs.EOF

Also, Didn't your mother ever tell you to clean up after yourself?
Be certain to

close anything you open rs.Close

and throw out the trash when you're finished

Set rs = Nothing
Set db = Nothing

Hmmm...maybe everything I learned about programming, I learned cleaning up
my house.

David h

I want to test for the existance of an activity discription
within a college course for a gradebook program before adding a
new activity to that course. This should prevent duplicate
activities in that course. In other words, if the activity does
not exist the program will continue but if it does exist it will
stop the user from adding that activity again. But if it does
not exist I get an error saying that no record exists. I don't
need that error. If the user types the name of an already
existing activity name, I want to alert the user that this
activity already exists. Otherwise they can continue. How do I
get rid of the error? Here's the beginning of my code with
superfluous details omitted:

Private Sub cmdOK_Click()
On Error GoTo Err_cmdOK_Click

Dim strSQL As String
Dim db As DAO.Database
Dim rs As DAO.Recordset

strSQL = "SELECT courses.courseCode,
activities.activityDescription FROM (courses INNER JOIN groups ON
courses.courseCode = groups.courseCode) INNER JOIN activities ON
groups.groupID = activities.groupID WHERE (((courses.courseCode)=
'" & [Forms]![frmSelectCourse]![cboSelectCourse] & "') AND
((activities.activityDescription)= '" & txtAD & "'))"
Set db = CurrentDb()
Set rs = db.OpenRecordset(strSQL, dbOpenDynaset)
rs.MoveLast
rs.MoveFirst

Then do one thing if the rs.RecordCount <1 and another thing if
the rs.RecordCount >0.

I just want to get rid of the "No Record" error, because it's not
an error in this case. Any ideas?

Thanks.

Rich Hollenbeck

 

[David C. Holley]

Old habits die hard. Besides do you actually trust anything that MS
buildt in as an aid?

close anything you open rs.Close

Son, the world has overtaken you....

C# has automatic memory management and garbage
collection, just like VB.Net, VB, VBA, and Access.

(david)

While NOT rs.EOF
wend

or If rs.EOF

Also, Didn't your mother ever tell you to clean up after yourself?
Be certain to

close anything you open rs.Close

and throw out the trash when you're finished

Set rs = Nothing
Set db = Nothing

Hmmm...maybe everything I learned about programming, I learned cleaning up
my house.

David h

I want to test for the existance of an activity discription
within a college course for a gradebook program before adding a
new activity to that course. This should prevent duplicate
activities in that course. In other words, if the activity does
not exist the program will continue but if it does exist it will
stop the user from adding that activity again. But if it does
not exist I get an error saying that no record exists. I don't
need that error. If the user types the name of an already
existing activity name, I want to alert the user that this
activity already exists. Otherwise they can continue. How do I
get rid of the error? Here's the beginning of my code with
superfluous details omitted:

Private Sub cmdOK_Click()
On Error GoTo Err_cmdOK_Click

Dim strSQL As String
Dim db As DAO.Database
Dim rs As DAO.Recordset

strSQL = "SELECT courses.courseCode,
activities.activityDescription FROM (courses INNER JOIN groups ON
courses.courseCode = groups.courseCode) INNER JOIN activities ON
groups.groupID = activities.groupID WHERE (((courses.courseCode)=
'" & [Forms]![frmSelectCourse]![cboSelectCourse] & "') AND
((activities.activityDescription)= '" & txtAD & "'))"
Set db = CurrentDb()
Set rs = db.OpenRecordset(strSQL, dbOpenDynaset)
rs.MoveLast
rs.MoveFirst

Then do one thing if the rs.RecordCount <1 and another thing if
the rs.RecordCount >0.

I just want to get rid of the "No Record" error, because it's not
an error in this case. Any ideas?

Thanks.

Rich Hollenbeck