Passing parameter from form to report

[Onslaughtdave]

Hi,

Is there anyway to select a name from a combobox and pass that value into a query as a input value? They do this on the form by pressing a "go" button which runs a report, which is created from a query.


thanks,

dave

 

[PHV]

In the query you may use parameter referencing an open mainform's control:
=[Forms]![form name]![control name]

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[Onslaughtdave]

Ok, it takes the parameter but doesnt display anything in the report.

any thoughts?

 

[PHV]

Does the query returns rows ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[1DMF]

You can also in a query place an expression rather than an real field and it will ask for input - i.e.

a table has a date field. we want all dates >= to a user input date. In the criteria section under that field in the design query screen, place...

>=#[Please Enter Date]#

on a blank column place...

userdate : [Please Enter Date]

Because [Please Enter Date] is not in the table a window will pop up asking for the value/input.

you will now have returned in the query a field called 'userdate' which is equal to the inputed date.

hope this is of use.

 

[Onslaughtdave]

PHV: no it only displays the headings.

1DMF: yes, but i dont want it to ask for input, i want it to take the combobox's value as the input

 

[PHV]

And when you replace the parameter by the value ?
Check the combo has the right column bound.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[Onslaughtdave]

Hmm, i dont think the column is bound i will try that now...

 

[Onslaughtdave]

ok, i bound it and now it gives me data not valid...??

I bound the form to the name table, and the combo to the emp name field, which is in that table.

is this correct??

 

[Onslaughtdave]

another update:

i think its passing in the wrong data.

How do i tell it which data to pass in?

 

[1DMF]

Let me get this right, you have a pull down on a form with options and you want the selection to be used in a query.

you would simply use the field name on the form in the query.

as PHV suggested only you do NOT use the BANG (!) you need a DOT (.) between form name and name of combo box

=[Forms]![form name].[combobox name]

The combo is unbound - setup using the combo wizard, saying store for use later.

And make sure you use the name of the combo box shown on the properties when referencing it. Also the form must stay open while the query runs.

 

[ProgramError]

How about creating a SQL statement in you recordsource, It worked for me.
For example I wanted to search for a programme number in a table and so created a routine which in turn built the SQL statement below
----- start code -----------------
Form_NameOfForm.RecordSource = "SELECT * FROM NameOfTable WHERE ProgNumber LIKE " & [PrognumSearch] & "ORDER BY NameOfTable.progNumber;"
-------------End code ----------------------

Substitute the following...
NameOfForm = the form on which the textbox is situated
NameOfTable = the table you are searching
ProgNumber = the the field in the table which is being searched
ProgNumSearch = the name of the input textbox or combobox (assuming the focus is still on the same form, you may have to change this to something like Form_NameOfForm.ProgNumSearch)

Remember to reset the RecordSource back to look at the whole Table by the following piece of code

Form_NameOfForm.RecordSource = "NameOfTable"

In my version, I programmed it to build the SQL statement depending on information entered in several textboxes.

Hope this helps