Well I am at a loss to explain this but I had originally created the following function inside a form module. I decided to move them to a new module and compile them there. Everything seems to be working fine now? The code was only required to append data to a new table so it wasn't required by the form anyway. Still would like to know the reason for the error though. Thanks for your help ethorn10 and Roy-Vidar!
Function atstonts()
Dim rs As DAO.Recordset
Dim jobno As String
Dim dept As String
Dim fats As Long
Dim tats As Long
Dim sql As String
Dim adate As Date
sql = "SELECT * FROM FromToATS;"
Debug.Print sql
Set rs = Application.CurrentDb.OpenRecordset(sql)
rs.MoveFirst
Do While Not rs.EOF
ajobno = rs!jobno
adept = rs!dept
fats = rs!fromats
tats = rs!toats
fsec = rs!fromsec
tsec = rs!tosec
If IsNull(rs!dt) Then
adate = Date
Else
adate = rs!dt
End If
Debug.Print rs!fromsec
' Call updateNTS(fats, tats, jobno, dept, adate)
rs.MoveNext
Loop
rs.Close
Set rs = Nothing
End Function
Private Function updateNTS(fromnum As Long, tonum As Long, jobno As String, dept As String, adate As Date)
On Error GoTo Err_updateNTS
Dim fromstrg As String
Dim tostrg As String
Dim fromnum As Long
Dim tonum As Long
Dim jn As String
Dim det As String
Dim dt As Date
Dim sql As String
Dim sqlfrNTS As String
Dim sqltoNTS As String
Dim rsfm As DAO.Recordset
Dim rst As DAO.Recordset
'Get the current job and dept. listed on the Job entry form
jn = jobno
det = dept
dt = adate
'Create two more SQL statements to look for the corresponding From and To NTS records.
'Ordered by the X and Y rankings of the record.
'For the From value we need to sort the NTS records in ACSending (default sorting) order so that
'the first record returned has the lowest geographical ranking and is the lowermost
'Southeasterly record.
sqlfrNTS = "SELECT InterNTSATS.NTS20Ksheet FROM InterNTSATS WHERE " & _
"ATStwp = " & fromnum & " ORDER BY InterNTSATS.NTSrankx, InterNTSATS.NTSranky;"
'For the To value we need to sort the NTS records in DESCending order so that the first
'record returned has the highest geographical ranking and is the uppermost Northwesterly record.
sqltoNTS = "SELECT InterNTSATS.NTS20Ksheet FROM InterNTSATS WHERE " & _
"ATStwp = " & tonum & " ORDER BY InterNTSATS.NTSrankx DESC, InterNTSATS.NTSranky DESC;"
'Open the From and To NTS records returned from the SQL statements into a corresponding recordset.
Set rsfm = Application.CurrentDb.OpenRecordset(sqlfrNTS)
Set rst = Application.CurrentDb.OpenRecordset(sqltoNTS)
'Move to the first record of each recordset which should be order properly.
'These are the records we want to insert.
rsfm.MoveFirst
rst.MoveFirst
'Turn off the warnings again momentarily.
DoCmd.SetWarnings False
'Insert the record into the FromToNTS20k table using the From and To records
'as well as the current job and Dept.
DoCmd.RunSQL "INSERT INTO FromToNTS20k (FromNTS20k, ToNTS20k, jobno, dept, dt) VALUES" & _
"('" & rsfm!NTS20Ksheet & "','" & rst!NTS20Ksheet & "','" & _
jn & "','" & det & "',#" & dt & "#)"
DoCmd.SetWarnings True 'Set the normal warnings back on.
'Close the recordsets.
rsfm.Close
rst.Close
Exit_updateNTS:
Exit Function
Err_updateNTS:
If Err.Number = 2501 Then
Resume Exit_updateNTS
Else
MsgBox Err.Description
End If
End Function
