Report parameters from a List box not working - any help please?

[SharonT]

Hi

I have read the faq on how to set up a report to accept criteria from a list box on a form and whilst I seem to have most of the relevant steps in place I don't seem to be able to tie them together to get it working. I think I am missing a crucial step.

FORM:
I have a form with two list boxes -
the first list box is Country - the row source is my 'Countries' table
the second list box is Pre-sort - with a list of Pre-sort options from another table

The form has a button which just calls up my report - Summary1 for now


Query:
I have a query which formats the data ready for the report and references my form for the criteria:

[Forms]![Desp_Parameter_Form]![Country_Select]

[Forms]![Desp_Parameter_Form]![Presort_Select]


Report:
My report just runs from the form and has no other 'tie in' to the criteria.

When I click the run report button the report opens in preview with no data in it.

i did have a macro embedded in the page_footer.on format event. However, when this was in the report wouldn't run at all and came up with a type mismatch error.


If I remove the criteria above and put a standard [Please enter country] prompt and another for the presort the report work fine. I just wanted to use the listbox to stop any user error in keying in options.

Any help gratefully received as I do not have a clue. It is about 10 years since I did any VB and bags of code just goes right over my head now.

Thanks

 

[Gammachaser]

If the listbox is called 'Country' then just use

[Forms]![Desp_Parameter_Form]![Country]

The value of the listbox will be the selected value. Use the same logic for 'Presort.'

Things get more complicated if it is a multi-select listbox, but based on what you have shown us I don't see that being the case.

 

[SharonT]

Thanks for that. I have used this code on a different form where the user just keys data straight into the field and it works fine but it does not work for me with a list box.

Do you need to do something with the 'On Click' event or similar when the user makes their selection from the list box? It does not seem to want to pass the selection back through to the query criteria.

Much appreciated

 

[lameid]

Please post your SQL statement under the report (record source or the SQL behind the query that is the record source), the name of your form and the names of your controls.

 

[SharonT]

SELECT [Data_for_despatch_note SUB QUERY].h_deladd6_1, [Data_for_despatch_note SUB QUERY].Presort, [Data_for_despatch_note SUB QUERY].h_import_order_no, [Data_for_despatch_note SUB QUERY].l_oldproductid, [Data_for_despatch_note SUB QUERY].l_qty_required, [Data_for_despatch_note SUB QUERY].Product_Description, [Data_for_despatch_note SUB QUERY].Despatch_date, [Data_for_despatch_note SUB QUERY].h_del_method, [Data_for_despatch_note SUB QUERY].h_cusname1, [Data_for_despatch_note SUB QUERY].h_custadd1_1, [Data_for_despatch_note SUB QUERY].h_cusadd2_1, [Data_for_despatch_note SUB QUERY].h_custown1, [Data_for_despatch_note SUB QUERY].h_cuscounty1, [Data_for_despatch_note SUB QUERY].h_cuspcode1, [Data_for_despatch_note SUB QUERY].h_cuscountry1, [Data_for_despatch_note SUB QUERY].h_delname1, [Data_for_despatch_note SUB QUERY].h_deladd1_1, [Data_for_despatch_note SUB QUERY].h_deladd2_1, [Data_for_despatch_note SUB QUERY].h_deladd4_1, [Data_for_despatch_note SUB QUERY].h_deladd5_1, [Data_for_despatch_note SUB QUERY].h_deladd7_1
FROM [Data_for_despatch_note SUB QUERY]
WHERE ((([Data_for_despatch_note SUB QUERY].h_deladd6_1)=[Forms]![Despatch_Parameter_Form]![Country_Select]) AND (([Data_for_despatch_note SUB QUERY].Presort)=[Forms]![Despatch_Parameter_Form]![Presort_Select]));


The Form is called Despatch_Parameter_Form
It has two unbound controls called: Country_Select and Presort_select

The report opened by the form just uses the "Data_for_despatch_notes".

 

[Gammachaser]

OK... I am a bit confused (don't worry, I am used to that feeling.)

In you first post you have:

FORM:
I have a form with two list boxes -
the first list box is Country - the row source is my 'Countries' table
the second list box is Pre-sort - with a list of Pre-sort options from another table

Now you have:

It has two unbound controls called: Country_Select and Presort_select

And in the last post you say:

The report opened by the form just uses the "Data_for_despatch_notes".

but your sql statement has

SELECT [Data_for_despatch_note SUB QUERY]...

So are the controls called 'Country' and 'Presort' or 'Country_Select' and 'Presort_Select'? Did you change their names?

And what is the name of the query?

 

[lameid]

The syntax looks good to me.

Do you have more than one column in your list boxes? Is the bound column the one that has the data appropriate for your criteria?

 

[dhookom]

Are the list boxes both single selection?

Do you see what you expect if you open the debug window (press Ctrl+G) and enter

? [Forms]![Despatch_Parameter_Form]![Country_Select]

Duane
Hook'D on Access
MS Access MVP

 

[SharonT]

I don't think the bound column is correct as I let the table generate a unique id so that may be causing the problem.

Also, I can't debug at the moment as I am out of the office today but will give it a go when I am back in tomorrow.

Many thanks for helping - this is turning me grey!

 

[SharonT]

Hi, just to let folks know that I am not ignoring this but we had a network crash yesterday and things are not fully restored yet so I don't have access to my Access DB at the moment and I am out of the country next week! Thanks for all questions and hints to date and I will be picking it up and no doubt making a pigs ear of this on my return. Many thanks