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

Dynamic Crosstab Report Problem

P: n/a
Access 2002

I am trying to create a dynamic crosstab report that parameters come
from 3 different forms.

I get runtime error 3070 - The Microsoft Jet database engine does
not recognize 'Forms!frmDefaults!ProviderID' as a valid field name
or expression, and debug takes me to line 60 below.

Any Suggestions Would Be Truly Appreciated!

Private Sub Report_Open(Cancel As Integer)

' Create underlying recordset for report using criteria
entered in
' EmployeeSalesDialogBox form.

Dim intX As Integer
Dim qdf As QueryDef
Dim frm As Form
Dim frm1 As Form
Dim frm2 As Form
' Set database variable to current database.
10 Set dbsReport = CurrentDb
20 Set frm = Forms!frmDate
30 Set frm1 = Forms!frmDefaults
40 Set frm2 = Forms!frmOrgDest
' Open QueryDef object.
50 Set qdf = dbsReport.QueryDefs("qEmgergTransFrom_Crosstab")
' Set parameters for query based on values entered
' in EmployeeSalesDialogBox form.
60 qdf.Parameters("Forms!frmDefaults!ProviderID") =
frm1!ProviderID
70 qdf.Parameters("Forms!frmOrgDest!cmbTransCode1") =
frm2!cmbTransCode1
80 qdf.Parameters("Forms!frmOrgDest!cmbTransCode2") =
frm2!cmbTransCode2
90 qdf.Parameters("Forms!frmOrgDest!cmbLocatFrom") =
frm2!cmbLocatFrom
100 qdf.Parameters("Forms!frmOrgDest!cmbLocatTo") =
frm2!cmbLocatTo
110 qdf.Parameters("Forms!frmDate!txtStartDate") =
frm!txtStartDate
120 qdf.Parameters("Forms!frmDate!txtEndDate") = frm!txtEndDate

' Open Recordset object.
130 Set rstReport = qdf.OpenRecordset()

' Set a variable to hold number of columns in crosstab query.
140 intColumnCount = rstReport.Fields.Count

End Sub

Nov 13 '05 #1
Share this Question
Share on Google+
1 Reply


P: n/a
With most queries, you can get away without explicitly declaring paramters,
but you must declare them in a crosstab.

1. Open the query in design view.

2. Choose Parameters on the Query menu.

3. In the dialog, enter:
[Forms]![frmDefaults]![ProviderID] Long
or whatever data type applies.

You should then be able to assign a value to the parameter as your code
does.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

<mt****@yadtel.net> wrote in message
news:11**********************@g49g2000cwa.googlegr oups.com...
Access 2002

I am trying to create a dynamic crosstab report that parameters come
from 3 different forms.

I get runtime error 3070 - The Microsoft Jet database engine does
not recognize 'Forms!frmDefaults!ProviderID' as a valid field name
or expression, and debug takes me to line 60 below.

Any Suggestions Would Be Truly Appreciated!

Private Sub Report_Open(Cancel As Integer)

' Create underlying recordset for report using criteria
entered in
' EmployeeSalesDialogBox form.

Dim intX As Integer
Dim qdf As QueryDef
Dim frm As Form
Dim frm1 As Form
Dim frm2 As Form
' Set database variable to current database.
10 Set dbsReport = CurrentDb
20 Set frm = Forms!frmDate
30 Set frm1 = Forms!frmDefaults
40 Set frm2 = Forms!frmOrgDest
' Open QueryDef object.
50 Set qdf = dbsReport.QueryDefs("qEmgergTransFrom_Crosstab")
' Set parameters for query based on values entered
' in EmployeeSalesDialogBox form.
60 qdf.Parameters("Forms!frmDefaults!ProviderID") =
frm1!ProviderID
70 qdf.Parameters("Forms!frmOrgDest!cmbTransCode1") =
frm2!cmbTransCode1
80 qdf.Parameters("Forms!frmOrgDest!cmbTransCode2") =
frm2!cmbTransCode2
90 qdf.Parameters("Forms!frmOrgDest!cmbLocatFrom") =
frm2!cmbLocatFrom
100 qdf.Parameters("Forms!frmOrgDest!cmbLocatTo") =
frm2!cmbLocatTo
110 qdf.Parameters("Forms!frmDate!txtStartDate") =
frm!txtStartDate
120 qdf.Parameters("Forms!frmDate!txtEndDate") = frm!txtEndDate

' Open Recordset object.
130 Set rstReport = qdf.OpenRecordset()

' Set a variable to hold number of columns in crosstab query.
140 intColumnCount = rstReport.Fields.Count

End Sub

Nov 13 '05 #2

This discussion thread is closed

Replies have been disabled for this discussion.