DCount Help Please!

[Carmen]

One of these days I'll figure out how to code on my own! I really appreciate all the help I get from this forum. Here's my latest problem: I have a field that is generated from 2 other fields-a combination of date and module id. I need to be able to compare the date and module id of a new entry with ones already entered, and if there is a match then increment by one. Here is my code, maybe that will help make this more clear:
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.

ModID = Forms!frmClasses!ModuleID
session = Forms!frmClasses!Session1
yr = Year(session)

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

strSecID = yr & sem & "M" & ModID & "-" & 0
If Not IsNull(SectionID) Then
RecCnt = DCount("me.SectionID", "Forms!frmClasses", _
strSecID = Left(Me.SectionID, InStr(Me.SectionID, "0")))
If RecCnt > 0 Then
rnum2 = RecCnt + 1
Else
rnum2 = 1
End If
End If

calcsecid = strSecID & rnum2
Me.SectionID = calcsecid

End Function

I works great until I get to the RecCnt line. Can anyone please give me a clue? Thanks!

 

[Matthew Snook]

What is the error message?

Matt

 

[Carmen]

No error message--it's just not incrementing. My number always ends in "-01" no matter how many entries I make.

 

[Matthew Snook]

I tried your code and noticed a couple of things. First of all, I don't know your format for dates and numbers. What format is a ModuleID? Two numbers, four?

yr = Year(Session)
'This gives me a four digit year.

Assigning the Semester works well.

strSecID = yr & sem & "M" & ModID & "-" & 0
'So, if my date is "9/15/01" and my ModuleID is "1001" then strSecID will be "2001FAM1001-0"


RecCnt = DCount("me.SectionID", "Forms!frmClasses", _
strSecID = Left(Me.SectionID, InStr(Me.SectionID, "0")))
'This part gives me errors. The first expression correctly indicates a field. The second expression should be a string expression for a table or query; you have listed the form. The optional third expression is supposed to be another string expression stating criteria for the count. Any field listed here (but you have listed a variable) must be part of the table mentioned in the second expression. Instead, your criteria attempts to reassign the value of strSecID, a variable.

If you'll tell me the format of ModuleID and SectionID (and Year), I'll try to be more help.

Matt

 

[Carmen]

Thanks for all your help Matt! In my tables, ModuleID is an alphanumeric field--for example "4E". The data entry for the date in the form is short date (MM/DD/YY) but the Year(session) gives the 4-digit year and that's fine. So the first part of my code goes like "2001FAM4E-{incrementing number}". The whole point of this is to keep track of the different modules given per semester. That is why I'm trying to compare a new entry with previous entries--to see if that particular module has already been given during that particular semester. I have also been trying something different since I couldn't get DCount to work:

If Not IsNull(Me.SectionID) Then
varLeft = Left(Me.SectionID, InStr(Me.SectionID, "-"))
End If

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





calcsecid = strSecID & RecCnt
Me.SectionID = calcsecid

But I can't get this to work either!!!! Thanks again, Matt!