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 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 4
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

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 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 4
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

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.

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(aintDivisions) To UBound(aintDivisions)
    If isPlanLocked(records!Year, i) Then
      Forms!frmMain!frameDivision.Value = intCounter
      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 intCounter
  records.MoveNext
Loop

[jmarcrum]

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

Public Function ExportPlanReports(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.Year FROM tblPlanExtra WHERE tblPlanExtra.Year >= " & getPlanStartingYear & " GROUP BY tblPlanExtra.Year ORDER BY tblPlanExtra.Year;"

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

Do While Not records.EOF
'Test for specific values of 1, 2, 3, 4, and 8
For intCounter = LBound(aintDivisions) To UBound(aintDivisions)
If isPlanLocked(records!Year, intCounter) Then
Forms!frmMain!frameDivision.value = intCounter
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 intCounter
records.MoveNext
Loop

records.Close

End Function

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

Public Function isPlanLocked(Year 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.Locked FROM tblPlanExtra WHERE ((tblPlanExtra.Year = " & Year & ") AND (tblPlanExtra.Division = " & Div & "));"


Set dbs_curr = CurrentDb
Set record = dbs_curr.OpenRecordset(sqlStatement)

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 ExportPlanReports(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.Year FROM tblPlanExtra WHERE tblPlanExtra.Year >= " & getPlanStartingYear & " GROUP BY tblPlanExtra.Year ORDER BY tblPlanExtra.Year;"

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

Do While Not records.EOF
'Test for specific values of 1, 2, 3, 4, and 8
For intCounter = LBound(aintDivisions) To UBound(aintDivisions)
If isPlanLocked(records!Year, intCounter) Then
Forms!frmMain!frameDivision.value = intCounter
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 intCounter
records.MoveNext
Loop

records.Close

End Function

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

Public Function isPlanLocked(Year 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.Locked FROM tblPlanExtra WHERE ((tblPlanExtra.Year = " & Year & ") AND (tblPlanExtra.Division = " & Div & "));"


Set dbs_curr = CurrentDb
Set record = dbs_curr.OpenRecordset(sqlStatement)

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:

If isPlanLocked(records!Year, intCounter) Then

[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(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
If i > 4 then
i = 8
End if
records.MoveNext
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(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
If i > 4 then
i = 8
End if
records.MoveNext
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(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
If i > 4 then
i = 8
End if
records.MoveNext
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 ...