RecordCount not working

[Hildegoat15]

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&quot) = LCase(Me!txtPatLast) And _
LCase(rst("PFirstName&quot) = LCase(Me!txtPatFirst) And _
LCase(rst("PMI&quot) = 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&quot

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 -Matt

http://www.ranamedical.com

 

[cmmrfrds]

The record count in Access 97 and probably DAO recordsets in Access 2000 gets activated after you have done a move (movefirst, movelast, movenext) on the recordset. It is the number of records retrieved in the query in your case 10. Probably not your intent.

You could do the process much easier (less code and logic) by using an ADO recordset.

 

[Hildegoat15]

thanx for the tip about ADO, i'll look into that and i'll pry use ADO rather that DAO from now on. but for the time being i used rst.absoluteposition + 1 rather than rst.recordcount, and it seems to work. -Matt

http://www.ranamedical.com

 

[Hildegoat15]

how would i go about creating and opening an ADO recorset?

for example, how would i make one from my "Sleep - Referral" table? -Matt

http://www.ranamedical.com

 

[Hildegoat15]

disregard that last post. i figured it out -- i was just missing a character in my rst.open statment. -Matt

http://www.ranamedical.com

 

[RikHess]

As I have been moving to AOD, I built this module ("FidMe&quot that can do a fast check for a record on file. As it stands, it reports back True or False. Maybe it could help you.


Function FindMe(SQLWhere As String, TableName As String) As Boolean
On Error GoTo FindMe_Err

Dim recSet As ADODB.Recordset

If Not IsNull(SQLWhere) Then
' open a rec-set as read-only, forward-only for fast find
Set recSet = New ADODB.Recordset
recSet.Open _
Source:="SELECT * FROM " & TableName & _
" WHERE " & SQLWhere, _
ActiveConnection:=CurrentProject.Connection, _
CursorType:=adOpenForwardOnly, _
LockType:=adLockReadOnly

If recSet.BOF Or recSet.EOF Then
FindMe = False
Else
FindMe = True
End If

End If

FindMe_Exit:
recSet.Close
Set recSet = Nothing
Exit Function

FindMe_Err:
MsgBox "In basFindMe:" & vbCrLf & _
"Error:" & Err.Number & " - " & Err.Description, _
Buttons:=vbExclamation
Resume FindMe_Exit

End Function

 

[cmmrfrds]

Check thread701-334511

It shows an example of using the filter method id ADO, which is the fastest (probably easiest) way of finding records once they are retrieved from the database. It has an sql type of syntax that is easy to use and you can put in multiple criteria.

 

[CosmoKramer]

Whenever I want to check to see if a record already exists, I use the DLookup function. Something like this:

Dim strMsg1 As String
strMsg1 = "Can't add department number " & Forms!frmDeptmastermain!txtDeptNum & " , it already exists"
If Not (IsNull(strX = DLookup("[dept_num]", "tblDepts", "[dept_num] = " _
& Forms!frmDeptmastermain!txtDeptNum))) Then
  MsgBox (strMsg1)
  ....
  ..Other stuff to do ......
End If

 

[Rolliee]

Why not order the recordset by the right order ... lastname,firstname,anyotherthing.

Then as you move thru the records your dupe must be the next record. Store the fields in an array that is numfields long.

check the array against the new rec and set the array to the present rec and so forth. If all are hits, bingo!!!

This is somewhat succinct but should let you see. A FOR loop is min code and easy to loop through making re.fields(?) = ar(?) as the check. let okay be okay and re.fields(?) = ar(?)

rollie@bwsys.net

 

[scking]

DLookup, like CosmoKramer said, is a quick way of checking of the record exists, as is .FindFirst using DAO. Both merely required you to develop a criteria for the search and then you don't need to look at each record.

Dim strCriteria
strCriteria = _
"PLastName='" & LCase(Me!txtPatLast) & "' And " _
& "PFirstName='" & LCase(Me!txtPatFirst) & "' And " _
& "PMI='" & LCase(Me!txtPatientMI) & "'"
rst.FindFirst strCriteria
If Not rst.NoMatch Then
' you found it
' Then you could continue looking for more if wanted
Do While Not rst.NoMatch
rst.FindNext
Loop
Else
' you didn't find it
End If


----------------------
scking@arinc.com
Life is filled with lessons.
We are responsible for the
results of the quizzes.
-----------------------