Parameterized query feeding report - help!

[brhouser]

Scenario:

Oracle Database, ACcess 2000 front end. User fills out a form to prepare information that is passed to a query. The query gets 4 pieces of information from the form, these are:

cbo_app - application
cbo_inst - institution
cbo_src - source
cbo_grp - product group

These 4 parameters are fed into a crosstab query that produces a 3 month report (the 3 months are what is crosstabed). It should be noted that the months are not true months, they are a character field with YYMM as the format. so, the actual value in there is '0210' for October 2002. This is something I cannot change, as this is the way the DBA wanted to store the dates for whatever reason.

I am tyring to feed the information from the xtab query to a report for the user to print out. This happens once a button "Run Report" is clicked on the Form.

The following code to run the report I have built, and works fine with a non-parameterized query, but not with my parameterized query. The query is the recordsource in the report. The below code is in the REPORT_OPEN routine.


Option Compare Database

Private Sub Report_Open(Cancel As Integer)
Dim intcolcount As Integer
Dim intcontrolcount As Integer
Dim i As Integer
Dim strname As String

Dim rst As ADODB.Recordset

Set rst = New ADODB.Recordset

rst.Open _
Source:=Me.RecordSource, _
ActiveConnection:=CurrentProject.Connection, _
Options:=adCmdTable

intcolcount = rst.Fields.Count
intcontrolcount = Me.Detail.Controls.Count

If intcontrolcount < intcolcount Then
intcolcount = intcontrolcount
End If

For i = 1 To intcolcount
strname = rst.Fields(i - 1).Name
Me.Controls(&quot;lblHeader&quot; & i).Caption = strname
Me.Controls(&quot;txtData&quot; & i).ControlSource = strname
Next i

For i = intcolcount + 1 To intcontrolcount
Me.Controls(&quot;lblHeader&quot; & i).Visible = False
Me.Controls(&quot;txtData&quot; & i).Visible = False
Next i

rst.Close

End Sub

The error I get is:

&quot;No value given for one or more required parameters&quot;

Can anyone post some help how to get these parameters passed correctly from the query to the report?

Thanks!

 

[Falcon99]

In your rst.Open command you need to add the criteria with the Filter command for your query, the four inputs from your form.

Reference the values from your form as:
forms!yourformname!yourcontrolname

hope this helps

 

[brhouser]

Falcon,

can you elaborate a little bit. I have tried the createparameter command, to no avail.

Anyone help pls?

Brian