Looping Through Records in a Table - MS Access 97

[salisha]

I am entering data into a database concerning complaints, and I need to use some sort of "red flag" or "early warning signal", to identify if this person has submitted complaints before.. This is the code, I have so far. It only works by identifying the first record in the table. How do i get it to loop through the table.

Private Sub txtVictimName_AfterUpdate()
Dim rst As Recordset, dbs As Database, iRecCount As Long, iCount As Long, vTemp As Variant
Set dbs = CurrentDb
Set rst = dbs.OpenRecordset("select victimname from tblFPU order by victimname asc&quot

If Not rst.EOF Then
rst.MoveLast
iCount = rst.RecordCount
End If

Do Until rst.EOF
If Me.txtVictimName = rst.Fields("VictimName&quot.Value Then
vTemp = "Victim's Name: " & rst.Fields("VictimName&quot.Value & vbCrLf & "Number of Complaints: " & iCount
MsgBox vTemp
End If
rst.MoveNext
Loop
rst.Close

End Sub

 

[salisha]

PLEASE HELP!!!!!!!!!!!!!!!!!!!!

 

[unifex]

I think this should work - although i haven't tried it!!! All this does is select a count of the times the name in your textbox appears on the database already. Hope this is useful....actually i hope it works! If not let me know...

Private Sub txtVictimName_AfterUpdate()
Dim Table as recordset

set table=currentdb.openrecordset("Select count(*) from [yourtable] where victimname='" & txtVictimName & "'&quot

if not table.eof then
msgbox ("This customer has complained " & table.fields(0) & " times&quot
end if

end sub

 

[salisha]

hey unifex,
THANK YOU SO MUCH!!!!!!! IT TOTALLY WORKED.
YOU ARE A LIFE SAVER.
THANKS!
I TOTALLY APPRECIATE IT!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!

 

[salisha]

actually, can I add some sort of code in there where the msgbox will not pop up for a customer that has never been entered in the database before. so like when I am entering a new person and then i TAB over to the next field, I will get no msgbox. I only get the msgbox why someone is in the database.

 

[salisha]

..

 

[unifex]

Hi salisha,

I'm glad it worked. Looking back on it i thought there were a few errors - whew!

It's not a problem to alter to code the way you want, i think my code could have been a tad better to begin with actually!

Replace this:
if not table.eof then
msgbox ("This customer has complained " & table.fields(0) & " times&quot
end if

with this:
if table.fields(0) > 0 then
msgbox ("This customer has complained " & table.fields(0) & " times&quot
end if

This will give you any customer on the table 1 or more times. You can obviously change the value checked for from 0 to whatever depending on how many complaints you feel are important!

Unifex

P.S I'd have replied sooner but i don't log in that often!

 

[salisha]

UNIFEX,,,,,,,
thank you so much....you are the best. it worked!!!!!
that's exactly what I was looking for. thanks. i appreciate it greatly...