RecordCount not working properly

[Matty]

Hi,

i've ran into a bit of a snag with my code, but i think it just needs a fresh set of eyes looking at it.

I have a form with textboxes full of patient information, and i'm trying to make sure duplicate patients can't be entered. I made up this function called DupCheck that checks the table to make sure the person's not there and if they are, it issues a messagebox, allowing the user to go to that record. Going to the record is my problem.

for an example, i duplicated patient #2 (there are 10 sample records in total). it goes thru this function, checks out patient #1, and since it's not the correct patient, it does a rst.movenext. i'm watching my rst.recordcount, and when it does the movenext, the recordcount goes from 1 to 10. so when it finds the correct patient, it's going to the 10th record, not the 2nd one. make any sense?

here's the function -- lemme know if you figure anything out.

Private Sub DupCheck()
'If this is a new record, search thru Referral table to see if someone by this name is
' already in there. If there is, a messagebox is issued.
Dim rst As DAO.Recordset
Dim db As DAO.Database
Dim lngDuplicate As Long

If Me.NewRecord = True Then
Set db = CurrentDb()
Set rst = db.OpenRecordset("Sleep - Referral", dbOpenDynaset)

rst.MoveFirst

Do Until rst.EOF
If LCase(rst("PLastName")) = LCase(Me!txtPatLast) And _
LCase(rst("PFirstName")) = LCase(Me!txtPatFirst) And _
LCase(rst("PMI")) = LCase(Me!txtPatientMI) Then

lngDuplicate = MsgBox("A person by this name has already been entered into the Referral table. Would you like to go to that record?", _
vbYesNo, "Duplicate Data")

Select Case lngDuplicate
Case 6
'If Yes is clicked in the messagebox, this record entry is undone and the form goes to the
' already existing record with that name
Me.Undo
DoCmd.GoToRecord acDataForm, "Sleep - Referral", acGoTo, rst.RecordCount
Case 7
'If No is clicked, focus goes back to the Patient Last Name textbox and the text is selected
With Me!txtPatLast
.SetFocus
.SelStart = 0
.SelLength = Len(.Text)
End With
End Select

rst.MoveLast
End If

rst.MoveNext
Loop

rst.close
Set rst = Nothing
End If
End Sub

 

[ritchieroo]

The "RecordCount" property gives you the number of records in total in the recordset, not the current position. You need to use the "AbsolutePosition" property.

You should probably think about what would happen if there were more than one match.

TIP: You loop through every record until you find a value that matches. I would probably construct a query to select all possible matches in a single step passing it the parameters as needed. Assuming each patient has a unique ID I'd build a function that returns a comma-separated list of those IDs...

Private Function DupCheck(PLastName, PFirstName, PMI) As String
  Dim rs As DAO.Recordset
  Dim sSQL As String 
  Dim sResult As String
  
  sSQL = "select * from [Sleep - Referral] " & _
      "where PLastName='" & PLastName & _
      "' and PFirstName='" & PFirstName & _
      "' and PMI='" & PMI & "'"

  Set rs = CurrentDB.OpenRecordset(sSQL)

  If Not rs.EOF Then
    Do Until rs.EOF
      sResult = sResult & CStr(rs!PatientID) & ","
      rs.MoveNext
    Loop
    sResult = Left$(sResult,Len(sResult)-1)
  Else 
    sResult = "0"
  End If

  DupCheck = sResult

  Set rs = Nothing
End Function

You could the use this list to apply a filter to your form.

 

[Matty]

thanx for the suggestion. the absoluteposition thing works, but i'll see if i can improve on this function with the info you gave me.

thanx again.