This is somewhat similar to a post below but it is not exactly the same so 

I felt I needed to post it. I have a report all set up that runs off a 

Parameter Query. The query uses 4 parameters that when ran gives the pop 

up boxes to enter the parameters. I now want to take a form and use some 

text boxes so the user can enter in the parameters on the form and it will 

feed those values into the parameters so the pop up windows don't come up. 

First off, is this possible? How would you do it? I tried creating some 

code when the report opens to feed the values in but I can't figure out 

how to point to the paramter value to assign it what is in the text box. 

Thanks for the help.

This is very possible.  What you need to do is replace your parameters in

the query with references to the appropriate controls on your

form--something like:



Forms!frmMyForm!txtMyTextBox



The easiest way to get these references right is to open your form, open

your query in design view, and then use the expression builder.  In the

bottom-left part of the builder, expand Forms, then Loaded Forms, select the

appropriate form, select the appropriate control in the bottom-center box

and then click the Paste button.



HTH,



-Roy



-----Original Message-----

From: brose@u... [mailto:brose@u...]

Sent: Tuesday, July 17, 2001 8:00 AM

To: Access

Subject: [access] Feeding Parameters From Text Boxes





This is somewhat similar to a post below but it is not exactly the same so 

I felt I needed to post it. I have a report all set up that runs off a 

Parameter Query. The query uses 4 parameters that when ran gives the pop 

up boxes to enter the parameters. I now want to take a form and use some 

text boxes so the user can enter in the parameters on the form and it will 

feed those values into the parameters so the pop up windows don't come up. 

First off, is this possible? How would you do it? I tried creating some 

code when the report opens to feed the values in but I can't figure out 

how to point to the paramter value to assign it what is in the text box. 

Thanks for the help.

I use a linked form\report and create the SQL for the report (in the 

form) each time the report opens.



I like this, as most of the code is reusable. To create a new report 

with a different set of parameters, design your new report and form, 

then copy the code. Then change the 'GetRowSource' function to fit the 

new report and ensure the LINKED_FORM constant is pointing to the 

correct form.



The report contains code similar to this:

__________________



Option Compare Database

Option Explicit

Const LINKED_FORM =3D "frmParamForm"  ' Change this to the name of your 

parameter form





Private Sub Report_NoData(Cancel As Integer)



    MsgBox "There is no data to display in this report", vbExclamation + 

vbOKOnly, "No Records!"

    Cancel =3D True



End Sub



Private Sub Report_Open(Cancel As Integer)

On Error GoTo errReport_Open



    'opens the parameter form - all proceccessing stops until the form 

is closed

    DoCmd.OpenForm LINKED_FORM, , , , , acDialog

   

    'Check if cancel button pressed on parameter form. If it has, don't 

open the report

    If Forms(LINKED_FORM).Cancel Then

        Cancel =3D True

    Else

        'Pass a sort order to your report if you wish

        If Forms(LINKED_FORM).ReportSortOrder <> "" Then

            Me.OrderBy =3D Forms(LINKED_FORM).ReportSortOrder

            Me.OrderByOn =3D True

        End If

        'Pass a report filter to your report if you wish

        If Forms(LINKED_FORM).ReportFilter <> "" Then

            Me.Filter =3D Forms(LINKED_FORM).ReportFilter

            Me.FilterOn =3D True

        End If

        'Pass the SQL for the reports recordsource

        If Forms(LINKED_FORM).ReportRecordSource <> "" Then

            Me.RecordSource =3D Forms(LINKED_FORM).ReportRecordSource

        End If

    End If

   

exitReport_Open:

    On Error Resume Next

    DoCmd.Close acForm, LINKED_FORM

    Exit Sub

   

errReport_Open:

    MsgBox Err.Description

    Err =3D 0

    Resume exitReport_Open

   

End Sub

__________________



The form that allows the user to enter criteria has code like this



__________________



Option Compare Database

Option Explicit

'Use these module level variables to hold the data you'll pass to the 

report

Dim mbolFormCanceled As Boolean

Dim mstrReportSortOrder As String

Dim mstrReportFilter As String

Dim mstrRecordSource As String



Private Sub btnOK_Click()

On Error GoTo Err_btnOK_Click

Dim strSortOrder As String

Dim strFilter As String

Dim strRecordSource As String



    Me.Visible =3D False

    mbolFormCanceled =3D False

   

    strSortOrder =3D "" 'Build your sort order string from the controls 

on the form here

    strFilter =3D "" 'Build your filter string from the controls on the 

form here

    strRecordSource =3D GetRowSource 'Build a new recordsource string 

from the controls on the form here

   

    mstrReportSortOrder =3D strSortOrder

    mstrReportFilter =3D strFilter

    mstrRecordSource =3D strRecordSource

   

Exit_btnOK_Click:

    Exit Sub



Err_btnOK_Click:

    MsgBox Err.Description

    Resume Exit_btnOK_Click

   

End Sub



Private Sub btnCancel_Click()

On Error GoTo Err_btnCancel_Click



    Me.Visible =3D False

    mbolFormCanceled =3D True

   

Exit_btnCancel_Click:

    Exit Sub



Err_btnCancel_Click:

    MsgBox Err.Description

    Resume Exit_btnCancel_Click

   

End Sub



Private Function GetRowSource()

Dim strSQL As String



        'Construct your SQL statment here, incorporating the parameters 

the user has entered

        strSQL =3D "SELECT Table.Field1, Table.Field2, Table.Field3, 

Table.Field3 "

        strSQL =3D strSQL & "FROM Table1 "

        strSQL =3D strSQL & "WHERE Table.Field1 =3D me![txtParam1] AND 

Table.Field2 =3D me![txtParam2] AND "

        strSQL =3D strSQL & "Table.Field3 <=3D me![txtParam3] AND 

Table.Field3 <> me![txtParam4]

      

        GetRowSource =3D strSQL

      

   

End Function



Public Property Get Cancel() As Boolean

    'Pass Cancelled value to calling report

    Cancel =3D mbolFormCanceled

   

End Property



Public Property Get ReportSortOrder() As String

    'Pass sort order value to calling report

    ReportSortOrder =3D mstrReportSortOrder

   

End Property



Public Property Get ReportFilter() As String

    'Pass Filter value to calling report

    ReportFilter =3D mstrReportFilter

   

End Property



Public Property Get ReportRecordSource() As String

    'Pass record source value to calling report

    ReportRecordSource =3D mstrRecordSource

   

End Property

-----Original Message-----

From:	brose@u... [SMTP:brose@u...]

Sent:	17 July 2001 16:01

To:	Access

Subject:	[access] Feeding Parameters From Text Boxes



This is somewhat similar to a post below but it is not exactly the same 

so

I felt I needed to post it. I have a report all set up that runs off a

Parameter Query. The query uses 4 parameters that when ran gives the pop 



up boxes to enter the parameters. I now want to take a form and use some 



text boxes so the user can enter in the parameters on the form and it 

will

feed those values into the parameters so the pop up windows don't come 

up.

First off, is this possible? How would you do it? I tried creating some

code when the report opens to feed the values in but I can't figure out

how to point to the paramter value to assign it what is in the text box. 



Thanks for the help.

1) Create criteria collection form with required fields.

2) Create required query

3) Enter an expression in the Criteria cell for each required field.



For example for our Employee  Appraisal Date we select emplyees due 

between certain dates with a criteria in AppraisialDue field by the 

following expression:



               [Forms]![frmBetweenDates]![dtmBeginningDate] And [Forms]![fr

mBetweenDates]![dtmEndingDate]



Note:

Query Field: AppraisialDue

Form Name: frmBetweenDates

Form Field1: dtmBeginningDate

Form Field2: dtmEndingDate



4) Click on Query, Parameter and enter your parameter type

For example is your selected field is called EmplID and it is an autonumber

 and you getting a parameter value from a form called MyForm:



Enter [Forms]![MyForm[EmplID] in Parameter box.

Select Long Integer from Data Type combobox in Data Type.



5) Open you form, enter required values and run the query.



That's all...





_____________________________________



John Fejsa

Systems Analyst/Computer Programmer

Hunter Centre for Health Advancement

Locked Bag 10

WALLSEND NSW 2287

Phone: (02) 49246 336 Fax: (02) 49246 209

Sure you can.



Your query parameter is: forms!MyForm!MyTextBox. If you may have to hide

(not close) the form in your particular situation when you call the query,

then close it when the report closes.



HTH,



Peter



> -----Original Message-----

> From: brose@u... [mailto:brose@u...]

> Sent: Tuesday, July 17, 2001 3:01 PM

> To: Access

> Subject: [access] Feeding Parameters From Text Boxes

>

>

> This is somewhat similar to a post below but it is not exactly

> the same so

> I felt I needed to post it. I have a report all set up that runs off a

> Parameter Query. The query uses 4 parameters that when ran gives the pop

> up boxes to enter the parameters. I now want to take a form and use some

> text boxes so the user can enter in the parameters on the form

> and it will

> feed those values into the parameters so the pop up windows don't

> come up.

> First off, is this possible? How would you do it? I tried creating some

> code when the report opens to feed the values in but I can't figure out

> how to point to the paramter value to assign it what is in the text box.

> Thanks for the help.

> ---

>