Problem with FindNext in Recordset object

  • Thread starter Thread starter Rick Brandt
  • Start date Start date
Hi,

The following code doesn't work. FindNext doesn't move at all and stays at
the first record found. While Loop seems entering into a never ending loop.
If I use .MoveFirst and .MoveNext in stead of .FindFirst and .FindNext, it
works and can move through the whole recordset. Anything wrong with my code?
Thank you.

Dim dbObject As Database
Dim rstObject As Recordset
Dim strSQL As String
Dim strChartNo As String

Set dbObject = CurrentDb
strSQL = "SELECT tblPatient.ChartNo, tblAdmission.* _
FROM tblPatient _
INNER JOIN tblAdmission _
ON tblPatient.ID = tblAdmission.PatientID"
Set rstObject = dbObject.OpenRecordset(strSQL)

With rstObject
.FindFirst "ICD9='650'"
Do While Not .EOF
strChartNo = strChartNo & " " & !ChartNo
.FindNext "ICD9='650'"
Loop
MsgBox strChartNo

.Close
End With

dbObject.Close
Set dbObject = Nothing
 
Suggestions:

1. After FindFirst, test NoMatch to see if there was an match, e.g.:
.FindFirst "ICD9='650'"
If .NoMatch Then
MsgBox "Not found"
Else
'other code in here
End If

2. Add an ORDER BY clause to your SQL statment to:
ORDER BY ICD9
You can now avoid the FindNext: just loop until ICD9 contains a different
value.

3. Better still, add a WHERE clause:
WHERE ICD9='650'
You can now avoid the FindFirst and the FindNext.

4. Consider declaring the recordset as:
Dim rstObject As DAO.Recordset
This can avoid problems with the Recordset object in other libraries.
 
SELECT tblPatient.ChartNo, tblAdmission.* _
FROM tblPatient _
INNER JOIN tblAdmission _
ON tblPatient.ID = tblAdmission.PatientID"
Set rstObject = dbObject.OpenRecordset(strSQL)

With rstObject
.FindFirst "ICD9='650'"
Do While Not .EOF
strChartNo = strChartNo & " " & !ChartNo
.FindNext "ICD9='650'"
Click to expand...

Your posted code doesn't actually do anything but pop a MsgBox() for
each matching record. If you are trying to update each row, then it
seems to me that you don't need a recordset at all, but should be
able to just write UPDATE SQL to do it all in one go:

UPDATE tblPatient INNER JOIN tblAdmission
ON tblPatient.ID = tblAdmission.PatientID
SET tblPatient.ChartNo = " & Chr(34) & strUpdateValue & Chr(34)
WHERE tblPatient.ChartNo = " & Chr(34) & "ICD9='650'" & Chr(34)

In general, it's very seldom the case that there is any real reason
to walk through a recordset like this. If you're updating records to
a single value, or even a value conditional on values in other
tables, it's almost always easier and faster to use UPDATE SQL. If
you're returning data from a single record or group of records, then
you just define a proper WHERE clause to return that record or group
of records.

The kind of code you've written is not something that you should be
writing very commonly at all.

On another note, you assign your database variable with CurrentDB,
so there is no reason to Close it, as you can't close the database
open in the UI of the current instance of Access, so the next to the
last line of your code should be deleted entirely.
 
Hi David,

Thank you for your comment. Actually this is part of my codes of a
calculation Sub. I am not going to Update my database with this code. I am
afraid people will not understand me if I didn't make it simpler or integral
when I posted.

What I want to do is to check through the records first to see if they are
all fit to be calculated. If some of them are not, Access will pop up a
message showing the number of these records.

I didn't know that I can't close the database in such instance. I'll fix it
as you suggest. Thank you very much.
 
Actually this is part of my codes of a
calculation Sub. I am not going to Update my database with this
code.
Click to expand...

The same point applies. It's very, very seldom that any calculation
requires a walk through a recordset.
I am
afraid people will not understand me if I didn't make it simpler
or integral when I posted.
Click to expand...

That's the hard part of posting, knowing which parts are going to
confuse things and which parts are essential. You're asking for a
fix for an issue in a particular solution to a problem, whereas I'm
suggesting that perhaps your solution is wrong to begin with, but
not knowing the actual problem, it's hard to say.
What I want to do is to check through the records first to see if
they are all fit to be calculated. If some of them are not, Access
will pop up a message showing the number of these records.
Click to expand...

This can't be done in SQL?
I didn't know that I can't close the database in such instance.
I'll fix it as you suggest. Thank you very much.
Click to expand...

The only time you should every Close a database variable if it's an
external database opened with DBEngine.OpenDatabase. Otherwise, just
set the variable to Nothing.
 
Hi David,

I am glad that we can discuss further. I am developing a small Access
program to use in a medical ward. This part is to calculate bed occupied rate
in a period of time, say a month. It requires that every patient admitted
within this period of time should have been discharged when the calculation
begins, because I need to know how many days each patient stays in a certain
bed. If any of these patient is still in ward, I have to defer the
calculation, but I need to know their chart numbers.

It seems to me to be more complicated to do it with SQL, so I go to Recordset.

First I open a recordset of patients who were admitted within a period of
time (use AdmissionDate), then

With Recordset
.FindFirst who is not discharged
Do While Not .NoMatch
Collect the chart number (strChartNo) of those who are not
discharged
.FindNext who is not discharged
Loop

If .RecordCount = 0 Then
MsgBox "No patient admitted in this period of time"
Else
If strChartNo <> "" Then
MsgBox to show these chart numbers
Else
.MoveFirst
Do While Not .EOF
Calculation........
.MoveNext
Loop
End If
End If
End With

I am not sure if this is a formal way to write an Access program, but it
works. I am appreciated to know any other ways to do this.
 
I am not sure if this is a formal way to write an Access program,
but it works. I am appreciated to know any other ways to do this.
Click to expand...

You left out the SQL so it's impossible to say if it can be done
with a SQL statement. I still suspect that it can be.
 
Back
Top