Using a form to feed a query with parameters

[Tanya Addison]

I saw somebody do this once!
I am trying to use a form to feed a query with three parmeters. The query is being used to produce a graph as a report. I have created three fields on the form. Two combos and one text.
I have then replaced the three parameters in the query using the expression builder with the name of the three fields
ie.
[Forms]![FrmGraphCategorySetup]![ChBranch]

When I open the form I then select the criteria I required and then I have a command button that opens the report (on the form )in Print preview but the Graph is nearly always (Not always!!!) blank. Can anybody help me with the missing link?
Many thanks

T

 

[parry]

Hi Tanya, you sound as though youve created things correctly and may just be the parameter.

Is the field your querying a text field? The parameter searches on an exact match basis, so for example if you put the word Test in your search form only records that show Test (not Test1, Test2 Test3 etc) will be shown.

To get around this try...

like [Forms]![FrmGraphCategorySetup]![ChBranch] & "*"

does this help?

 

[Tanya Addison]

Hi Parry

They are txt fields but two of the parameters have been set up using combo boxes that are looking up the correct values. It works if I just open up the report and type in the same values. I was wondering whether it was the number of parameters or the relationship between the query and report. I will try the wildcard though! Thanks for the response

T

 

[SydneyGeek]

If they are Combos, are the parameters connected to the correct fields? I'm betting that you have the parameters as criteria for the Text data fields but they should be criteria for the ID fields of the respective tables.
Give that a go.

Denis

 

[Tanya Addison]

Dear Denis.

I am not quite sure I understand what you mean by ID fields. The combo boxes have been given a name within their properties and I then refer to that name in the criteria of the query
[Forms]![FrmGraphCategorySetup]![ChBranch]
[chBranch] is the name of the combo which looks up branch names in the Branch table. The form then feeds this criteria of this feild in the query.

thanks for you help!
T

 

[SydneyGeek]

Hi Tanya,
ID fields are the glue that holds the Access database together. They ensure that that each item (record) in a table has a unique value that identifies it to the rest of the database. It can be text or a number, but it can never be duplicated or re-used.
Practically, this is how they work in your situation:
The Combo shows a name or description to make it user-friendly. This happens because the combo typically has 2 fields in its underlying query -- the ID of the table and another descriptive field, which is the one you see. When you use a Combo to choose parameters for a query or form, the stored value is the ID, not the name or description -- so the criteria need to go on the ID field. You'll recognise it in the query grid because (a) it will come from the same table as the descriptive field and (b) it will be in bold text at the top of the table layout.
So ... open the query. Cut the criteria / parameter for your combo. In the criteria for the ID (bold) field, place the parameter value. (It the ID field is not in the query grid, put it there by double-clicking or by dragging it onto the grid).
Long-winded, but it's hard to draw pictures with words sometimes!

HTH
Denis