Access Userform populate listbox with query result (1 Viewer)

All,

Hope somebody can help me with something I can't get my head around with. I have a very simple userform of which I want to populate list box "Act_value" with the result of a query which criteria are dependent on selections made in comboboxes. I added the Access file with only the form and the table needed to do so.

Can you let a query (like I did in this file with grouping and criteria included) populate a list box if it comes to values? If so why does it not show my value in "Act_value"? I added a after change event in VBA to update the act value list box ("Me.Act_Value.Requery")

And when do you choose for Dlookup in a situation like this one?

I am very grateful for your response.

thanks,

Your use of a list box instead of a combobox for the year didn't help, as no value was being selected so you were never getting any data. Your query was set to SUM the year as well, which also would have stopped any data coming back.

I've made some changes which are now getting you results, no idea if they are what you needed, again a single line list box is not very sensible as a results viewer...

Really appreciated! Thanks. It works fine.

Two questions for my understanding.

I should never have used a list box in this situation? I expect the listbox could do the same as a combobox here?

And is the use of a query for getting the result in Act_Value the best way or should you recommend using dlookup in situations (like these)?

Regards,

Michiel

DLookup for related values is slower than using a query for the combo in which multiple fields can be returned even if not displayed. (Look up the trick of setting the column width to 0 so things will be returned but not seen.)

Michiel Soede said:

I should never have used a list box in this situation? I expect the listbox could do the same as a combobox here?

Click to expand...

If you only want to select a single value from a smallish list of fixed possible values, then a combo is definitely the way to go. You can display other columns to assist in the choice but will only every see the first visible column once it is selected.

A list box is better when you have a list of things that you might want to select more than one value from or to display multiple columns - either as a pick list or display of results. However often a sub-form can be better depending on what you want to do afterwards.