Loop Not Looping

[Carmen]

Hi All,
I am racking my brain trying to figure out this loop...

Set rst = CurrentDb.OpenRecordset("ModuleStartDates", dbOpenDynaset)
mysearch = "[SSN]='" & Me.SSN & "'"


Do
rst.FindFirst mysearch
ModID = DLookup("[ModNbr]", "ModuleStartDates", mysearch)
For i = 1 To Len(ModID)
strChar = Mid$(ModID, i, 1)
Select Case strChar
Case "0"
'do nothing
Case Else
ID1 = ID1 & strChar
End Select
Next i
ID2 = ID1


If StrComp(ID2, onechar1, vbTextCompare) = 0 Or _
StrComp(ID2, onechar2, vbTextCompare) = 0 Then
answer2 = MsgBox("This teacher has already taken this Module.", vbOKOnly)
If answer2 = vbOK Then Exit Sub
End If
ID1 = ""
ID2 = ""
rst.MoveNext

Loop Until rst.NoMatch = True

This works great the first time through, but it is not looping through the records so ModID never changes. What I'm trying to do is loop through a person's class history to see if they are enrolling for a Module they have already taken. I have tried putting rst.FindFirst mysearch & the Dlookup outside the loop and that doesn't help. Can anyone please see what I'm doing wrong??? Thanks in advance!!!

Carmen

 

[Travis]

What jumps at me is that you are using FindFirst. This will always start at the beging of a RecordSet and search for the first occurance. Which means you will get a perpetual loop when the data exists in the [SSN] field. Change your code to the below.

Set rst = CurrentDb.OpenRecordset("ModuleStartDates", dbOpenDynaset) 
mysearch = "[SSN]='" & Me.SSN & "'" 

[i]rst.MoveFirst[/i]
Do 
rst.Find mysearch  'This was [B]FindFirst[/B]
ModID = DLookup("[ModNbr]", "ModuleStartDates", mysearch) 
For i = 1 To Len(ModID) 
strChar = Mid$(ModID, i, 1) 
Select Case strChar 
Case "0" 
'do nothing 
Case Else 
ID1 = ID1 & strChar 
End Select 
Next i 
ID2 = ID1 


If StrComp(ID2, onechar1, vbTextCompare) = 0 Or _ 
StrComp(ID2, onechar2, vbTextCompare) = 0 Then 
answer2 = MsgBox("This teacher has already taken this Module.", vbOKOnly) 
If answer2 = vbOK Then Exit Sub 
End If 
ID1 = "" 
ID2 = "" 
[i]'rst.MoveNext [/i]

Loop Until rst.NoMatch = True

 

[Carmen]

Thanks for your help Travis, but still no luck........

I see what you are saying about the FindFirst, and I added the MoveFirst line outside of the loop like you said, comment the MoveNext line, and then tried to change the FindFirst line to just Find and I get the "Method or Data Member Not Found". My recordset is DAO and isn't Find an ADO method? I could be wrong about that........

Anyway, it's still doing (or rather, NOT doing ) the same thing!!

 

[pono1]

Possible Workaround

dim strSSN as String

strSSN = DLOOKUP("ssn","ModuleStartDates","[ssn] ='" & strSSN & "'")

If NOT ISNULL(strSSN) Then
msgBox "Wo. This teacher already took this module",vbOKONLY,"Error"
End if

Regards,
Tim

 

[Carmen]

Tim,
I tried your solution, but what I was afraid was going to happen, did happen! By comparing the SSN I get the message box if a teacher has taken any classes, not necessarily the same one that is being entered for a new enrollment. It really needs to compare the ModID...... I appreciate the help though and if you think of anything else let me know!!

 

[pono1]

Carmen,

If both the modid and the ssn are in the same table (ModuleStartDates), something like the following might work...

dim strSSN as String
dim lngMODID as Long

lngMODID = me.txtmodid  'assign modID control value to variable
strSSN= me.txtssn	'assign SSN control value to variable

If Not IsNull(DLookup("ssn", "ModuleStartDates", _
"[ssn] ='" & strSSN & "' AND [modID] = " & lngMODID)) Then

msgBox "Wo. This teacher already took this module",vbOKONLY,"Error"

End if

Regards,
Tim

 

[Travis]

Sorry, I forgot to convert back to DAO.

Set rst = CurrentDb.OpenRecordset("ModuleStartDates", dbOpenDynaset) 
mysearch = "[SSN]='" & Me.SSN & "'" 
'Find the first Instance
[b]rst.FindFirstFirst mysearch[/b]

'Loop only if one is found
Do While not rst.NoMatch
ModID = DLookup("[ModNbr]", "ModuleStartDates", mysearch) 
For i = 1 To Len(ModID) 
strChar = Mid$(ModID, i, 1) 
Select Case strChar 
Case "0" 
'do nothing 
Case Else 
ID1 = ID1 & strChar 
End Select 
Next i 
ID2 = ID1 


If StrComp(ID2, onechar1, vbTextCompare) = 0 Or _ 
StrComp(ID2, onechar2, vbTextCompare) = 0 Then 
answer2 = MsgBox("This teacher has already taken this Module.", vbOKOnly) 
If answer2 = vbOK Then Exit Sub 
End If 
ID1 = "" 
ID2 = "" 
'rst.MoveNext 
 [b]rst.FindNext mysearch[/b]
Loop Until rst.NoMatch = True

 

[simongallop]

Not sure about this, but doesn't the DLookUp bit change the record pointer to the first instance that it finds therefore nullifying the findfirst findnext bits?

Presuming that the ModID number that you are looking for is the number on the recordset where SSN = Me.SSN. If that is the case then there is no need for DLookUp as your rst.FindFirst MySearch has already selected the record and you just want to take the ModID from that record.

ie replace ModID = DLookup("[ModNbr]", "ModuleStartDates", mysearch)

with ModID=rst("ModNbr")

Now the FindNext bit ought to work

HTH

 

[Carmen]

Travis and Harry...You guys are awesome!! The looping part works and Harry's trick with the ModNbr was just the ticket. Thanks so much for your help!

Carmen