print preview report

[jasn_78]

hey everyone when i run the following code for a report i get the error message run time error 2465 cant find the field line12 referred to in your expression any help would be great

Regards Jason

Private Sub Report_Open(Cancel As Integer)
DoCmd.Maximize
Dim i As Integer
For i = 0 To 7
ReportLabel(i) = ""
Next i
Call CreateReportQuery
End Sub

Private Sub ReportFooter_Format(Cancel As Integer, FormatCount As Integer)
Dim i As Integer
For i = 1 To 6
If FillLabel(i) <> "" Then
Me("line" & i & "1").Visible = True
Me("line" & i & "2").Visible = True
Me("line" & i & "3").Visible = True
Else
Me("line" & i & "1").Visible = False
Me("line" & i & "2").Visible = False
Me("line" & i & "3").Visible = False
End If
Next i
End Sub

Sub CreateReportQuery()
On Error GoTo Err_CreateQuery
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim qdf As DAO.QueryDef
Dim tbf As DAO.TableDef
Dim tbg As DAO.TableDef
Dim tbh As DAO.TableDef
Dim tbi As DAO.TableDef
Dim fld As DAO.Field
Dim indexx As Integer
Dim FieldList As String
Dim strSQL As String
Dim i As Integer

Set db = CurrentDb
Set qdf = db.QueryDefs("qrySITESTOCK")
Set tbf = db.TableDefs("ITEMTBL")
Set tbg = db.TableDefs("ITMATBL")
Set tbh = db.TableDefs("ISDPTBL")
Set tbi = db.TableDefs("SYSCTBL")
indexx = 0
For Each fld In qdf.Fields
If fld.Type >= 1 And fld.Type <= 8 Or fld.Type = 10 Then
FieldList = FieldList & "[" & fld.Name & "] as Field" & indexx & ", "
ReportLabel(indexx) = fld.Name
End If
'MsgBox Label(indexx)
indexx = indexx + 1
Next fld
For i = indexx To 7
FieldList = FieldList & "null as Field" & i & ","
Next i
FieldList = Left(FieldList, Len(FieldList) - 1)

strSQL = "Select " & FieldList & ", ITEMTBL.ITEM_DESC, ISDPTBL.ISDP_DESC " _
& "FROM (ITEMTBL INNER JOIN QRYSITESTOCK ON ITEMTBL.ITEM_NUMBER = QRYSITESTOCK.PLU) " _
& "INNER JOIN ISDPTBL ON ITEMTBL.ITEM_ISDP = ISDPTBL.ISDP_NUMBER " _
& "WHERE ISDPTBL.ISDP_NUMBER = '10';"

db.QueryDefs.Delete "qryCrossTabReport"
Set qdf = db.CreateQueryDef("qryCrossTabReport", strSQL)

'MsgBox strSQL

Exit_CreateQuery:
Exit Sub

Err_CreateQuery:
If Err.Number = 3265 Then '*** if the error is the query is missing
Resume Next '*** then skip the delete line and resume on the next line
Else
MsgBox Err.DESCRIPTION '*** write out the error and exit the sub
Resume Exit_CreateQuery
End If
End Sub

 

[pbaldy]

Do all those lines exist?

11, 12, 13...61, 62, 63

 

[jasn_78]

thanks, pbaldy just took a sample off the net and edited still trying to figure stuff out as i go so what where those lines to do?

 

[pbaldy]

Well, you didn't say what line of code is blowing up, so I was guessing it was one of these:

Me("line" & i & "2").Visible = True

At some point during that loop it's going to be referring to "line12", so I'm assuming the error comes from there. There would have to be a control (presumably a line) with that name that it can make visible/invisible. If there isn't the code will blow up.

 

[jasn_78]

yeah pbaldy sorry that was where it was blowing up thanks for your help