Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Parameterized query feeding report - help!

Status
Not open for further replies.

brhouser

Programmer
Jan 3, 2003
5
US
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!
 
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
 
Falcon,

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

Anyone help pls?

Brian
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top