By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,456 Members | 1,400 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,456 IT Pros & Developers. It's quick & easy.

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

100+
P: 105
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!!!
Dec 28 '07 #1
Share this Question
Share on Google+
9 Replies


ADezii
Expert 5K+
P: 8,597
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.
Expand|Select|Wrap|Line Numbers
  1. Dim aintDivisions(1 To 5) As Integer, intCounter As Integer
  2.  
  3. aintDivisions(1) = 1
  4. aintDivisions(2) = 2
  5. aintDivisions(3) = 3
  6. aintDivisions(4) = 4
  7. aintDivisions(5) = 8
  8.  
  9. Do While Not records.EOF
  10.   'Test for specific values of 1, 2, 3, 4, and 8
  11.   For intCounter = LBound(aintDivisions) To UBound(aintDivisions)
  12.     If isPlanLocked(records!Year, i) Then
  13.       Forms!frmMain!frameDivision.Value = intCounter
  14.       DoCmd.OpenForm "frmPlan"
  15.       Forms!frmPlan!txtYear.Value = records!Year
  16.       DoCmd.OpenForm "frmPrintPlan"
  17.       PrintPlan
  18.       DoCmd.OutputTo acOutputReport, "rptPlan", "Snapshot Format", Path & Forms!frmPlan!txtYear.Value & _
  19.                                      " " & FindDivisionName(Forms!frmMain!frameDivision.Value) & ".snp"
  20.       DoCmd.Close acReport, "rptPlan"
  21.       DoCmd.Close acForm, "frmPrintPlan"
  22.       DoCmd.Close acForm, "frmPlan"
  23.     End If
  24.   Next intCounter
  25.   records.MoveNext
  26. Loop
Dec 28 '07 #2

100+
P: 105
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.
Dec 28 '07 #3

ADezii
Expert 5K+
P: 8,597
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:
Expand|Select|Wrap|Line Numbers
  1. If isPlanLocked(records!Year, intCounter) Then
Dec 28 '07 #4

100+
P: 105
i changed it, but it still is giving me problems; the same error
Dec 28 '07 #5

jaxjagfan
Expert 100+
P: 254
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.
Dec 28 '07 #6

ADezii
Expert 5K+
P: 8,597
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?
Dec 28 '07 #7

100+
P: 105
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.
Dec 28 '07 #8

100+
P: 105
Hey yall, got it working thanks for the tips there at the end...

Expand|Select|Wrap|Line Numbers
  1. Public Function ExportPlanReports(path As String)
  2.  
  3. Dim years As String
  4. Dim dbo As Database
  5. Dim records As Recordset
  6. Dim sqlStatement As String
  7. Dim i As Integer
  8.  
  9. sqlStatement = "SELECT tblPlanExtra.Year FROM tblPlanExtra WHERE tblPlanExtra.Year >= " & getPlanStartingYear & " GROUP BY tblPlanExtra.Year ORDER BY tblPlanExtra.Year;"
  10.  
  11. Set dbo = CurrentDb
  12. Set records = dbo.OpenRecordset(sqlStatement)
  13.  
  14. While Not records.EOF
  15.     For i = 1 To 5
  16.     If i > 4 Then
  17.         i = 8
  18.     End If
  19.         If isPlanLocked(records!Year, i) Then
  20.             Forms!frmMain!frameDivision.value = i
  21.             DoCmd.OpenForm "frmPlan"
  22.             Forms!frmPlan!txtYear.value = records!Year
  23.             DoCmd.OpenForm "frmPrintPlan"
  24.             PrintPlan
  25.             DoCmd.OutputTo acOutputReport, "rptPlan", "Snapshot Format", path & Forms!frmPlan!txtYear.value & " " & FindDivisionName(Forms!frmMain!frameDivision.value) & ".snp"
  26.             DoCmd.Close acReport, "rptPlan"
  27.             DoCmd.Close acForm, "frmPrintPlan"
  28.             DoCmd.Close acForm, "frmPlan"
  29.         End If
  30.     Next i
  31.     records.MoveNext
  32. Wend
  33. records.Close
  34.  
  35. End Function
Dec 28 '07 #9

jaxjagfan
Expert 100+
P: 254
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 ...
Dec 28 '07 #10

Post your reply

Sign in to post your reply or Sign up for a free account.