runtime error 9 subscript out of range?

[dabruins]

I am attempting to create a very simple function that will call another iteratively from within a loop in the main function. I keep getting a runtime error 9 whenever I try to call the function from my immediate window. I am at a loss to explain why this is so. Could someone enlighten me as to the nature of the problem with my code? Thanks!

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;"
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

Call updateNTS(fats, tats, jobno, dept, adate)
rs.MoveNext
Loop
rs.Close
End Function

 

[ethorn10]

This isn't the problem, but you'll also want to:

Set rs = nothing

after the rs.close line.

 

[dabruins]

Hi ethorn10.

Does Set rs = nothing automatically imply rs.close? Or is this just a safe method of variable control?

 

[ethorn10]

just standard cleanup of your objects that you'll want to do anytime you do a:

Set var = something_object_related

like i said...this isn't the reason for your error...still looking at that though.

 

[RoyVidar]

Since it doesn't return anything, why not convert it to a sub?

Mind telling on which line it bombs? Would we need the other function/sub too?

Roy-Vidar

 

[dabruins]

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

 

[dabruins]

I'm sorry Roy-Vidar. To answer your earlier question, the calling function fails immediately. I commented out everything except a simple debug statement and I still got the same error.

 

[ethorn10]

yet again, i doubt this is the problem, but you declared 'jobno' and then made 'ajobno' = rs!jobno and then passed 'jobno'. maybe try passing 'ajobno' instead.

 

[dabruins]

Thanks for that ethorn10! I was just debugging my failed output when I got your reply.

 

[RoyVidar]

Same with adept/dept - do you have Option Explicit as the second line of the module? You really should - and also in Tools | Options - select Require Variable Declaration, to have it automaticly inserted in all new modules.

Hint - hint - naming convention;-)

[tt] Dim strJobNo As String
Dim strDept As String
Dim lngFats As Long
Dim lngTats As Long
...[/tt]

Roy-Vidar

 

[ethorn10]

dabruins -- is that what was causing the problem?

 

[dabruins]

No the problem no longer exists now that I have moved the code to a new module. Thanks for the tips on declaring the variables as explicit and using proper naming conventions. Still no answer as to why the original problem exisited? I've debugged the logic gaps and built my new table. Now I should make an effort to fix my poor naming conventions! I'm quite new to VBA so many thanks to you both for your help!