No UD functions return values...

[cheuschober]

Heya folks. IT just switched all our pc's over to Office 2k3 (from 2002) and now I'm having some -very- curious behaviour in my modules and wondered if anyone else had anything similar...

So, I've created a few custom functions to do various pieces of business for me in a new database, the following one auto-populates a table for me:

Public Function PopulateSemesters(intStartYr As Integer, intEndYr As Integer, bytStartSem As Byte, bytEndSem As Byte) As Long

Dim intYear As Integer
Dim intSemester As Integer
Dim bytSem As Byte
Dim lngCntr As Long
Dim strSQL As String

lngCntr = 0

For intYear = intStartYr To intEndYr
    For bytSem = IIf(intYear = intStartYr, bytStartSem, 1) To IIf(intYear = intEndYear, bytEndSem, 4)
        intSemester = (10 * intYear) + bytSem
        
        strSQL = "INSERT INTO tblSemester ( Sem ) " & _
        "SELECT " & intSemester & " AS Sem;"
        
        DoCmd.RunSQL strSQL
        
        lngCntr = lngCntr + 1
    Next bytSem
Next intYear

PopulateSemesters = lngCntr

End Function

Now the trouble I've run into gets very curious. You see if I attempt to call that function in a query, I get a null result and a bunch of key violates (more on the key violations later.

To test what was going on I created the following:

Function TestMe() As Integer
TestMe = 345
End Function

Run in the immediate window I am returned 345.
Run in a query again? Null (and let's assume I know how to write a simple select query)

Okay, so, back to the key violations. Seeing the result I was returned in the immediate window I thought I would at the very least be able to run my PopulateSemesters function there to populate my table (tblSemester) which is a linked table.

No so, it would seem. The value it attempts to append is null again and the returned result, also null despite peppering it with msgboxes like so:

...
 MsgBox "Semester to Append: " & intSemester
DoCmd.RunSQL strSQL
MsgBox "Semester to Append: " & intSemester
...

The first box = 20001, the append value = NULL, and the second box = 20001.

So I am brain fried on this--has anyone seen anything like it before?

~Chad