Error when moving to next record, runtime error '3021' for i

[jmarcrum]

i need some help

i have a table with 5 city divisions

1 = D1
2 = D2
3 = D3
4 = D4

and

8 = D5

I am trying to write a For loop in within a while statement that will search not only 1 To 4, but also 8 as well. I have tried...For 1 To 4 Or 8 as code but that doesn't work. After my code reads the first 4 divisions it tries to read the integer 5, and i don't have a 5; i have an 8. I am using 5, 6, and 7 for other important things. How can I accomplish reading my 8 integer division in my tbl?

my code:

Public Function ExportPlanRepor ts(path As String)

Dim years As String
Dim dbo As Database
Dim records As Recordset
Dim sqlStatement As String
Dim i As Integer

sqlStatement = "SELECT tblPlanExtra.Ye ar FROM tblPlanExtra WHERE tblPlanExtra.Ye ar >= " & getPlanStarting Year & " GROUP BY tblPlanExtra.Ye ar ORDER BY tblPlanExtra.Ye ar;"

Set dbo = CurrentDb
Set records = dbo.OpenRecords et(sqlStatement )

While Not records.EOF
For i = 1 To 4
If isPlanLocked(re cords!Year, i) Then
Forms!frmMain!f rameDivision.va lue = i
DoCmd.OpenForm "frmPlan"
Forms!frmPlan!t xtYear.value = records!Year
DoCmd.OpenForm "frmPrintPl an"
PrintPlan
DoCmd.OutputTo acOutputReport, "rptPlan", "Snapshot Format", path & Forms!frmPlan!t xtYear.value & " " & FindDivisionNam e(Forms!frmMain !frameDivision. value) & ".snp"
DoCmd.Close acReport, "rptPlan"
DoCmd.Close acForm, "frmPrintPl an"
DoCmd.Close acForm, "frmPlan"
End If
Next i
records.MoveNex t
Wend

records.Close

End Function

thanks for your help in advance!!!

[ADezii]

i need some help

i have a table with 5 city divisions

1 = D1
2 = D2
3 = D3
4 = D4

and

8 = D5

I am trying to write a For loop in within a while statement that will search not only 1 To 4, but also 8 as well. I have tried...For 1 To 4 Or 8 as code but that doesn't work. After my code reads the first 4 divisions it tries to read the integer 5, and i don't have a 5; i have an 8. I am using 5, 6, and 7 for other important things. How can I accomplish reading my 8 integer division in my tbl?

my code:

Public Function ExportPlanRepor ts(path As String)

Dim years As String
Dim dbo As Database
Dim records As Recordset
Dim sqlStatement As String
Dim i As Integer

sqlStatement = "SELECT tblPlanExtra.Ye ar FROM tblPlanExtra WHERE tblPlanExtra.Ye ar >= " & getPlanStarting Year & " GROUP BY tblPlanExtra.Ye ar ORDER BY tblPlanExtra.Ye ar;"

Set dbo = CurrentDb
Set records = dbo.OpenRecords et(sqlStatement )

While Not records.EOF
For i = 1 To 4
If isPlanLocked(re cords!Year, i) Then
Forms!frmMain!f rameDivision.va lue = i
DoCmd.OpenForm "frmPlan"
Forms!frmPlan!t xtYear.value = records!Year
DoCmd.OpenForm "frmPrintPl an"
PrintPlan
DoCmd.OutputTo acOutputReport, "rptPlan", "Snapshot Format", path & Forms!frmPlan!t xtYear.value & " " & FindDivisionNam e(Forms!frmMain !frameDivision. value) & ".snp"
DoCmd.Close acReport, "rptPlan"
DoCmd.Close acForm, "frmPrintPl an"
DoCmd.Close acForm, "frmPlan"
End If
Next i
records.MoveNex t
Wend

records.Close

End Function

thanks for your help in advance!!!

I'm not 100% sure that I understand your exact request, but using this code within your code body may be the answer. If this is not the case, please explain your situation in more detail.
[CODE=vb]
Dim aintDivisions(1 To 5) As Integer, intCounter As Integer

aintDivisions(1 ) = 1
aintDivisions(2 ) = 2
aintDivisions(3 ) = 3
aintDivisions(4 ) = 4
aintDivisions(5 ) = 8

Do While Not records.EOF
'Test for specific values of 1, 2, 3, 4, and 8
For intCounter = LBound(aintDivi sions) To UBound(aintDivi sions)
If isPlanLocked(re cords!Year, i) Then
Forms!frmMain!f rameDivision.Va lue = intCounter
DoCmd.OpenForm "frmPlan"
Forms!frmPlan!t xtYear.Value = records!Year
DoCmd.OpenForm "frmPrintPl an"
PrintPlan
DoCmd.OutputTo acOutputReport, "rptPlan", "Snapshot Format", Path & Forms!frmPlan!t xtYear.Value & _
" " & FindDivisionNam e(Forms!frmMain !frameDivision. Value) & ".snp"
DoCmd.Close acReport, "rptPlan"
DoCmd.Close acForm, "frmPrintPl an"
DoCmd.Close acForm, "frmPlan"
End If
Next intCounter
records.MoveNex t
Loop[/CODE]

[jmarcrum]

There's still an error in my code. The function isPlanLocked is where the error lies...

Public Function ExportPlanRepor ts(path As String)

Dim years As String
Dim dbo As Database
Dim records As Recordset
Dim sqlStatement As String
Dim aintDivisions(1 To 5) As Integer, intCounter As Integer

aintDivisions(1 ) = 1
aintDivisions(2 ) = 2
aintDivisions(3 ) = 3
aintDivisions(4 ) = 4
aintDivisions(5 ) = 8

sqlStatement = "SELECT tblPlanExtra.Ye ar FROM tblPlanExtra WHERE tblPlanExtra.Ye ar >= " & getPlanStarting Year & " GROUP BY tblPlanExtra.Ye ar ORDER BY tblPlanExtra.Ye ar;"

Set dbo = CurrentDb
Set records = dbo.OpenRecords et(sqlStatement )

Do While Not records.EOF
'Test for specific values of 1, 2, 3, 4, and 8
For intCounter = LBound(aintDivi sions) To UBound(aintDivi sions)
If isPlanLocked(re cords!Year, intCounter) Then
Forms!frmMain!f rameDivision.va lue = intCounter
DoCmd.OpenForm "frmPlan"
Forms!frmPlan!t xtYear.value = records!Year
DoCmd.OpenForm "frmPrintPl an"
PrintPlan
DoCmd.OutputTo acOutputReport, "rptPlan", "Snapshot Format", path & Forms!frmPlan!t xtYear.value & " " & FindDivisionNam e(Forms!frmMain !frameDivision. value) & ".snp"
DoCmd.Close acReport, "rptPlan"
DoCmd.Close acForm, "frmPrintPl an"
DoCmd.Close acForm, "frmPlan"
End If
Next intCounter
records.MoveNex t
Loop

records.Close

End Function

-----------------------------------------------------------------------------

Public Function isPlanLocked(Ye ar As Integer, Div As Integer) As Boolean
Dim Locked As Boolean

Dim dbs_curr As Database
Dim record As Recordset
Dim value As Boolean

sqlStatement = "SELECT tblPlanExtra.Lo cked FROM tblPlanExtra WHERE ((tblPlanExtra. Year = " & Year & ") AND (tblPlanExtra.D ivision = " & Div & "));"


Set dbs_curr = CurrentDb
Set record = dbs_curr.OpenRe cordset(sqlStat ement)

Locked = record("Locked" )

record.Close
isPlanLocked = Locked

End Function

------------------------------------------------------------------------------

in the isPlanLocked function, the error is on the Locked = record("Locked" ) line...above that, when I let my cursor hover over the sqlstatement line, the Div is set to "5" and I still do not have a 5 in my table, it's an 8.

[ADezii]

There's still an error in my code. The function isPlanLocked is where the error lies...

Public Function ExportPlanRepor ts(path As String)

Dim years As String
Dim dbo As Database
Dim records As Recordset
Dim sqlStatement As String
Dim aintDivisions(1 To 5) As Integer, intCounter As Integer

aintDivisions(1 ) = 1
aintDivisions(2 ) = 2
aintDivisions(3 ) = 3
aintDivisions(4 ) = 4
aintDivisions(5 ) = 8

sqlStatement = "SELECT tblPlanExtra.Ye ar FROM tblPlanExtra WHERE tblPlanExtra.Ye ar >= " & getPlanStarting Year & " GROUP BY tblPlanExtra.Ye ar ORDER BY tblPlanExtra.Ye ar;"

Set dbo = CurrentDb
Set records = dbo.OpenRecords et(sqlStatement )

Do While Not records.EOF
'Test for specific values of 1, 2, 3, 4, and 8
For intCounter = LBound(aintDivi sions) To UBound(aintDivi sions)
If isPlanLocked(re cords!Year, intCounter) Then
Forms!frmMain!f rameDivision.va lue = intCounter
DoCmd.OpenForm "frmPlan"
Forms!frmPlan!t xtYear.value = records!Year
DoCmd.OpenForm "frmPrintPl an"
PrintPlan
DoCmd.OutputTo acOutputReport, "rptPlan", "Snapshot Format", path & Forms!frmPlan!t xtYear.value & " " & FindDivisionNam e(Forms!frmMain !frameDivision. value) & ".snp"
DoCmd.Close acReport, "rptPlan"
DoCmd.Close acForm, "frmPrintPl an"
DoCmd.Close acForm, "frmPlan"
End If
Next intCounter
records.MoveNex t
Loop

records.Close

End Function

-----------------------------------------------------------------------------

Public Function isPlanLocked(Ye ar As Integer, Div As Integer) As Boolean
Dim Locked As Boolean

Dim dbs_curr As Database
Dim record As Recordset
Dim value As Boolean

sqlStatement = "SELECT tblPlanExtra.Lo cked FROM tblPlanExtra WHERE ((tblPlanExtra. Year = " & Year & ") AND (tblPlanExtra.D ivision = " & Div & "));"


Set dbs_curr = CurrentDb
Set record = dbs_curr.OpenRe cordset(sqlStat ement)

Locked = record("Locked" )

record.Close
isPlanLocked = Locked

End Function

------------------------------------------------------------------------------

in the isPlanLocked function, the error is on the Locked = record("Locked" ) line...above that, when I let my cursor hover over the sqlstatement line, the Div is set to "5" and I still do not have a 5 in my table, it's an 8.

Try changing Line #12 to:
[CODE=vb]If isPlanLocked(re cords!Year, intCounter) Then[/CODE]

[jmarcrum]

i changed it, but it still is giving me problems; the same error

[jaxjagfan]

Have you tried this:

While Not records.EOF
For i >= 8
If isPlanLocked(re cords!Year, i) Then
Forms!frmMain!f rameDivision.va lue = i
DoCmd.OpenForm "frmPlan"
Forms!frmPlan!t xtYear.value = records!Year
DoCmd.OpenForm "frmPrintPl an"
PrintPlan
DoCmd.OutputTo acOutputReport, "rptPlan", "Snapshot Format", path & Forms!frmPlan!t xtYear.value & " " & FindDivisionNam e(Forms!frmMain !frameDivision. value ) & ".snp"
DoCmd.Close acReport, "rptPlan"
DoCmd.Close acForm, "frmPrintPl an"
DoCmd.Close acForm, "frmPlan"
End If
Next i
If i > 4 then
i = 8
End if
records.MoveNex t
Wend

The if at bottom would look for and change i to 8 if greater than 4. Thus bypassing 5-7.

[ADezii]

Have you tried this:

While Not records.EOF
For i >= 8
If isPlanLocked(re cords!Year, i) Then
Forms!frmMain!f rameDivision.va lue = i
DoCmd.OpenForm "frmPlan"
Forms!frmPlan!t xtYear.value = records!Year
DoCmd.OpenForm "frmPrintPl an"
PrintPlan
DoCmd.OutputTo acOutputReport, "rptPlan", "Snapshot Format", path & Forms!frmPlan!t xtYear.value & " " & FindDivisionNam e(Forms!frmMain !frameDivision. value ) & ".snp"
DoCmd.Close acReport, "rptPlan"
DoCmd.Close acForm, "frmPrintPl an"
DoCmd.Close acForm, "frmPlan"
End If
Next i
If i > 4 then
i = 8
End if
records.MoveNex t
Wend

The if at bottom would look for and change i to 8 if greater than 4. Thus bypassing 5-7.

The whole concept behind placing legitimate values into a Fixed Array was to avoid that type of comparison, why are you still using it?

[jmarcrum]

Have you tried this:

While Not records.EOF
For i >= 8
If isPlanLocked(re cords!Year, i) Then
Forms!frmMain!f rameDivision.va lue = i
DoCmd.OpenForm "frmPlan"
Forms!frmPlan!t xtYear.value = records!Year
DoCmd.OpenForm "frmPrintPl an"
PrintPlan
DoCmd.OutputTo acOutputReport, "rptPlan", "Snapshot Format", path & Forms!frmPlan!t xtYear.value & " " & FindDivisionNam e(Forms!frmMain !frameDivision. value ) & ".snp"
DoCmd.Close acReport, "rptPlan"
DoCmd.Close acForm, "frmPrintPl an"
DoCmd.Close acForm, "frmPlan"
End If
Next i
If i > 4 then
i = 8
End if
records.MoveNex t
Wend

The if at bottom would look for and change i to 8 if greater than 4. Thus bypassing 5-7.

Now that gives me an error on the For i >=8 line, it says = is expected there.

[jmarcrum]

Hey yall, got it working thanks for the tips there at the end...

Public Function ExportPlanReports(path As String)

Dim years As String
Dim dbo As Database
Dim records As Recordset
Dim sqlStatement As String
Dim i As Integer

sqlStatement = "SELECT tblPlanExtra.Year FROM tblPlanExtra WHERE tblPlanExtra.Year >= " & getPlanStartingYear & " GROUP BY tblPlanExtra.Year ORDER BY tblPlanExtra.Year;"

Set dbo = CurrentDb
Set records = dbo.OpenRecordset(sqlStatement)

While Not records.EOF
    For i = 1 To 5
    If i > 4 Then
        i = 8
    End If
        If isPlanLocked(records!Year, i) Then
            Forms!frmMain!frameDivision.value = i
            DoCmd.OpenForm "frmPlan"
            Forms!frmPlan!txtYear.value = records!Year
            DoCmd.OpenForm "frmPrintPlan"
            PrintPlan
            DoCmd.OutputTo acOutputReport, "rptPlan", "Snapshot Format", path & Forms!frmPlan!txtYear.value & " " & FindDivisionName(Forms!frmMain!frameDivision.value) & ".snp"
            DoCmd.Close acReport, "rptPlan"
            DoCmd.Close acForm, "frmPrintPlan"
            DoCmd.Close acForm, "frmPlan"
        End If
    Next i
    records.MoveNext
Wend
records.Close

End Function

[jaxjagfan]

Glad we could be of assistance. I saw a possible solution to your problem without increasing the complexity of your code. It may not be the "Preferred" method of getting there but ...