Can't See the Forest for the Trees....

[Carmen]

I am beating my head against the wall and just can't figure out why this isn't working. What I'm trying to do is compare the yr, sem, and modID of a new entry with previous entries. If there's a match, increment by one, if not then it's the first record. I keep getting "Invalid Use of Null", "Type Mismatch", or-the most frustrating-Error message 0 which is no message! The trouble starts right after the Line marked with **. Up til then it works great. Does anybody have any ideas?????

Function calcsecid()


'capture the year of session 1 date as yr and label the fall and _
spring semesters according to the month of session 1. Combine _
yr, sem, and module id to create the section ID field.
Set rst = Me.Recordset
ModID = Forms!frmClasses!ModuleID
session = Forms!frmClasses!Session1
yr = Year(session)
RecCnt = 1

If (DatePart("m", [session]) <= 6) Then
sem = "SP"
ElseIf (DatePart("m", [session]) >= 7) Then
sem = "FA"
End If

*** strSecID = yr & sem & "M" & ModID & "-"
If Not IsNull(SectionID) Then
varLeft = Left(SectionID, InStr(SectionID, "-"))
End If

rst.MoveFirst

Do Until rst.EOF
If strSecID = varLeft Then
RecCnt = RecCnt + 1
Else: RecCnt = RecCnt
End If
rst.MoveNext
Loop


calcsecid = strSecID & RecCnt
Me.SectionID = calcsecid

End Function

[This message has been edited by Carmen (edited 12-18-2001).]

 

[simongallop]

Changed the code slightly. The first problem is that you are testing SectionID outside of the loop so you are only looking against the first record. I have changed the code so that you are trying all matches from ...-1 up to ...-x stopping when it can't find a match.

Copy this code in and see if it works.

NOTE:: In the MySearch string, as you are trying to match a string against the field SectionID then the search string must be held within single quotes. Not so obvious here, but if you copy the code you will spot them!

Function calcsecid()

Set rst = CurrentDb.OpenRecordset("NameOfTableWithData", dbOpenDynaset)
ModID = Forms!frmClasses!ModuleID
session = Forms!frmClasses!Session1
yr = Year(session)

''NOTE CHANGED TO 0
RecCnt = 0

If (DatePart("m", [session]) <= 6) Then
sem = "SP"
ElseIf (DatePart("m", [session]) >= 7) Then
sem = "FA"
''Failure capture!!
Else
Msgbox "sem failure"
goto Lastline
End If

strSecID = yr & sem & "M" & ModID & "-"

''As you are trying to find the highest number after the - then loop through searching by adding one to the string

Do
RecCnt = RecCnt+1
'Presuming that SectionID is the field in the table

MySearch = "[SectionID] = '" & strSecID & RecCnt & "'"
rst.findfirst MySearch
Loop until rst.NoMatch = True

calcsecid = strSecID & RecCnt
Me.SectionID = calcsecid

Lastline:

End Function


HTH

 

[Carmen]

Harry! I don't know how to thank you! This seems to be working beautifully! Now that you've explained it to me I can see what I was doing wrong. Thanks again--this forum is a life saver!