Runtime 3061 Error

J

jasn_78

Registered User.
Local time
Tomorrow, 02:01
Joined
Aug 1, 2001
Messages
214
Ok guys i have had a look online and cant seem to figure out what is wrong with this.

On opening my cross tab report I get this error
Too Few parameters. Expected 0.

Attached is my sql for that query and the code on the report

HELP please :)

Code: 
PARAMETERS [Forms]![frmACCTRX]![txtDATEFROM] DateTime, [Forms]![frmACCTRX]![txtDATETO] DateTime;
TRANSFORM Sum(ACOPTBL.ACOP_TRX_AMT) AS AMOUNT
SELECT ACOPTBL.ACOP_ACCT_NO AS ACCOUNT, 
	ACOPTBL.ACOP_TRX_DATE AS TRXDATE, 
	ACOPTBL.ACOP_SYSC_NUMBER AS STORE, 
	ACOPTBL.ACOP_AREA_NUMBER AS AREA, 
	ACOPTBL.ACOP_TILL_NUMBER AS TILL, 
	SYSCTBL.SYSC_COMPANY, 
	TILLTBL.TILL_DESC, 
	AREATBL.AREA_DESC, 
	ACMFTBL.ACMF_NAME, ACMFTBL.ACMF_CURR_BALANCE
FROM SYSCTBL, 
	AREATBL, 
	TILLTBL, 
	ACMFTBL, 
	ACOPTBL
WHERE (((ACOPTBL.ACOP_TRX_DATE) Between [Forms]![frmACCTRX]![txtDATEFROM] And [Forms]![frmACCTRX]![txtDATETO]) 
AND ((SYSCTBL.SYSC_NUMBER)=[AREATBL].[AREA_SYSC_NUMBER] 
And (SYSCTBL.SYSC_NUMBER)=[ACOPTBL].[ACOP_SYSC_NUMBER]) 
AND ((AREATBL.AREA_NUMBER)=[TILLTBL].[TILL_AREA_NUMBER] 
And (AREATBL.AREA_NUMBER)=[ACOPTBL].[ACOP_AREA_NUMBER]) 
AND ((ACMFTBL.ACMF_NUMBER)=[ACOPTBL].[ACOP_ACCT_NO]) 
AND ((TILLTBL.TILL_NUMBER)=[ACOPTBL].[ACOP_TILL_NUMBER]))
GROUP BY ACOPTBL.ACOP_ACCT_NO, 
	ACOPTBL.ACOP_TRX_DATE, 
	ACOPTBL.ACOP_SYSC_NUMBER, 
	ACOPTBL.ACOP_AREA_NUMBER, 
	ACOPTBL.ACOP_TILL_NUMBER, 
	SYSCTBL.SYSC_COMPANY, 
	TILLTBL.TILL_DESC, 
	AREATBL.AREA_DESC, 
	ACMFTBL.ACMF_NAME, 
	ACMFTBL.ACMF_CURR_BALANCE, 
	SYSCTBL.SYSC_NUMBER, 
	AREATBL.AREA_NUMBER, 
	ACMFTBL.ACMF_NUMBER, 
	TILLTBL.TILL_NUMBER, 
	ACOPTBL.ACOP_TRX_DATE
ORDER BY ACOPTBL.ACOP_TRX_DATE
PIVOT ACOPTBL.ACOP_TRX_TYPE;

Code: 
Dim rst As DAO.Recordset
Dim db As DAO.Database
Private Sub Report_Open(Cancel As Integer)
Dim i As Integer
Dim j As Integer

Set db = CurrentDb
Set rst = db.OpenRecordset("select * from crstabACCTRX")

Select Case Forms!frmACCTRX!subfrmGROUPBY!frameGROUPBY.VALUE
    Case 1
    Me.GroupHeader2.Visible = False
    Me.GroupHeader3.Visible = False
    Case 2
    Me.GroupHeader1.Visible = False
    Me.GroupHeader3.Visible = False
    Case 3
    Me.GroupHeader1.Visible = False
    Me.GroupHeader2.Visible = False
End Select

Me.lbl0.Caption = ""
Me.lbl1.Caption = ""
Me.lbl2.Caption = ""
Me.lbl3.Caption = ""

Me.txt0.ControlSource = ""
Me.txt1.ControlSource = ""
Me.txt2.ControlSource = ""
Me.txt3.ControlSource = ""



rst.MoveFirst
j = -1
i = 0
For i = 0 To rst.Fields.count - 1
If rst.Fields(i).Name Like "ACCOUNT" Or "STORE" Or "AREA" _
            Or "TILL" Or "SYSC_COMPANY" Or "TRX_DATE" Or _
            "TILL_DESC" Or "AREA_DESC" Or "ACMF_NAME" Or _
            "ACMF_CURR_BALANCE" Then GoTo skip_it
j = j + 1
    Select Case j
        Case 0
            Me.txt0.ControlSource = rst.Fields(i).Name
        Case 1
            Me.txt1.ControlSource = rst.Fields(i).Name
        Case 2
            Me.txt2.ControlSource = rst.Fields(i).Name
        Case 3
            Me.txt3.ControlSource = rst.Fields(i).Name
    End Select
skip_it:
Next i
rst.Close
Set rst = Nothing

End Sub
Private Sub ReportHeader_Format(Cancel As Integer, FormatCount As Integer)
Dim i As Integer
Dim j As Integer

Set db = CurrentDb
Set rst = db.OpenRecordset("select * from crstabACCTRX")

rst.MoveFirst
j = -1
i = 0
For i = 0 To rst.Fields.count - 1
If rst.Fields(i).Name Like "ACCOUNT" Or "STORE" Or "AREA" _
            Or "TILL" Or "SYSC_COMPANY" Or "TRX_DATE" Or _
            "TILL_DESC" Or "AREA_DESC" Or "ACMF_NAME" Or _
            "ACMF_CURR_BALANCE" Then GoTo skip_it
j = j + 1
Select Case j
        Case 0
            Me.lbl0.Caption = rst.Fields(i).Name
        Case 1
            Me.lbl1.Caption = rst.Fields(i).Name
        Case 2
            Me.lbl2.Caption = rst.Fields(i).Name
        Case 3
            Me.lbl3.Caption = rst.Fields(i).Name
End Select

skip_it:
Next
rst.Close
Set rst = Nothing
End Sub
 
Paul thanks I am now past that error but I am getting the following

Runtime error 3265
Item not found in this collection on the following code

Code: 
If qdf.Fields(i).Name Like _
            "ACCOUNT" Or "STORE" Or "AREA" _
            Or "TILL" Or "SYSC_COMPANY" Or "TRX_DATE" Or _
            "TILL_DESC" Or "AREA_DESC" Or "ACMF_NAME" Or _
            "ACMF_CURR_BALANCE" Then
 
Shouldn't it be rst.fields(i) instead of qdf.fields(i)?
 
Alisa spot on thanks for that :) :)

one last one I am now trying to dynamically change my group totals option

Code: 
Private Sub Report_Open(Cancel As Integer)
Dim i As Integer
Dim j As Integer

Set db = CurrentDb
Set qdf = db.QueryDefs("crstabACCTRX")
qdf.Parameters("[Forms]![frmACCTRX]![txtDATEFROM]") = [Forms]![frmACCTRX]![txtDATEFROM]
qdf.Parameters("[Forms]![frmACCTRX]![txtDATETO]") = [Forms]![frmACCTRX]![txtDATETO]

DoCmd.OpenQuery "crstabACCTRX"
DoCmd.Close


Set rst = qdf.OpenRecordset()

'Set rst = db.OpenRecordset("select * from crstabACCTRX")

Select Case Forms!frmACCTRX!subfrmGROUPBY!frameGROUPBY.VALUE
    Case 1
    Me.GroupLevel(1).ControlSource = "STORE"
    Me.txtGROUPNO.ControlSource = "STORE"
    Me.txtGROUPDESC.ControlSource = "SYSC_COMPANY"
    Case 2
    Me.GroupLevel(1).ControlSource = "AREA"
    Me.txtGROUPNO.ControlSource = "AREA"
    Me.txtGROUPDESC.ControlSource = "AREA_DESC"
    Case 3
    Me.GroupLevel(1).ControlSource = "TILL"
    Me.txtGROUPNO.ControlSource = "TILL"
    Me.txtGROUPDESC.ControlSource = "TILL_DESC"
End Select

Me.lbl0.Caption = ""
Me.lbl1.Caption = ""
Me.lbl2.Caption = ""
Me.lbl3.Caption = ""

Me.txt0.ControlSource = ""
Me.txt1.ControlSource = ""
Me.txt2.ControlSource = ""
Me.txt3.ControlSource = ""



rst.MoveFirst
j = -1
i = 0

For i = 0 To rst.Fields.count - 1

If rst.Fields(i).Name Like "ACCOUNT" Then GoTo skip_it
If rst.Fields(i).Name Like "AREA" Then GoTo skip_it
If rst.Fields(i).Name Like "TILL" Then GoTo skip_it
If rst.Fields(i).Name Like "SYSC_COMPANY" Then GoTo skip_it
If rst.Fields(i).Name Like "STORE" Then GoTo skip_it
If rst.Fields(i).Name Like "TRXDATE" Then GoTo skip_it
If rst.Fields(i).Name Like "TILL_DESC" Then GoTo skip_it
If rst.Fields(i).Name Like "AREA_DESC" Then GoTo skip_it
If rst.Fields(i).Name Like "ACMF_NAME" Then GoTo skip_it
If rst.Fields(i).Name Like "ACMF_CURR_BALANCE" Then GoTo skip_it

j = j + 1
    Select Case j
        Case 0
            Me.txt0.ControlSource = rst.Fields(i).Name
            Me.txtSUM0.ControlSource = "Sum([" & rst.Fields(i).Name & "])"
        Case 1
            Me.txt1.ControlSource = rst.Fields(i).Name
            Me.txtSUM1.ControlSource = "Sum([" & rst.Fields(i).Name & "])"
        Case 2
            Me.txt2.ControlSource = rst.Fields(i).Name
            Me.txtSUM2.ControlSource = "Sum([" & rst.Fields(i).Name & "])"
        Case 3
            Me.txt3.ControlSource = rst.Fields(i).Name
            Me.txtSUM3.ControlSource = "Sum([" & rst.Fields(i).Name & "])"
    End Select
skip_it:
Next i
rst.Close
Set rst = Nothing

End Sub

my problem lies on the txtSUM lines but I cant see what i am typing in wrong as its just a sum of the fields above it or should i just be doing a sum on those actual text boxes instead? i.e Sum(txt0) etc?
 
btw this is the error msg i am getting
 

Attachments

  • 3075 error.jpg
    3075 error.jpg
    9.8 KB · Views: 149
Try

Me.txtSUM0.ControlSource = "=Sum([" & rst.Fields(i).Name & "])"
 
Paul thanks for that but now for some reason instead of totalling that field it is returning the account balance field (ACMF_CURR_BALANCE) even though it is saying its summing the right field :(
 
never mind sorry bout that one guys all sorted :) thank you so much for your help and patience
 
You must log in or register to reply here.

Similar threads

L
REFERENCING Form's instances controls
2
Replies
36
Views
2,660
Mark_
M
S
Solved The expression after update you entered as the event property setting produced the following error:
Replies
9
Views
4,864
SaranPriya-7954
S
F
Crosstab report error
Replies
8
Views
2,117
foshizzle
F
Nagesh
Solved Crosstab query report - Column order
Replies
12
Views
2,470
theDBguy
theDBguy
M
Switchboard error
2
Replies
25
Views
4,821
JMongi
JMongi

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Back
Top Bottom