Parameter query as a List box data source

[limperger]

Hello everyone!

Just in case anyone has any wonder about this... I have a parameter query that searchs for records that have the particular word provided by the user in a memo field (using the criteria Like "*" & [Enter the particular word] & "*"). I want those results displayed in a form. Additionally, a list box is mandatory, to skip from one record to another one.
Unfortunately, when using the list box wizard in this particular case, Access usually encounters a problem in the source data and does not generate the list box. Surprisingly, one day I eventually managed to have the list box made but I do not know why I was able too. It seems that, sometimes, Access does not display the "source data" message and succesfully completes all the steps in the list box wizard. Does anybody knows why is this happening? Has somebody encountered the same problems when setting a parameter query as a list box data source?

[NeoPa]

So, you don't know how you managed to get it to work, but you want someone to tell you why it does?

A big ask.

Let's see if we can get some basic facts down first in case there is something here that tells us anything.

I understand that you have a form which is bound to a query which has a parameter in it that needs to be entered whenever this query is run.
On the form are various controls bound to the fields to display from the said query.
Additionally you have an unbound ListBox control (not ComboBox) whose data source is exactly the same (the parameter query) as the form itself.
Now, whenever an item is selected in the ListBox, you have the form move the current record to match the newly selected value.

Is this an accurate description of your situation (Don't say yes unless you know it to be true)?

[limperger]

Dear NeoPa:

This is exactly the situation, word by word. Simply put, most people want to learn how to populate a parameter query via a listbox (and they are redirected to the excellent and very useful Martin Green's Access tips website); I would need the other way round: how to populate a listbox (in a form) with a parameter query.

I have very little idea of VBA, though I plan to learn more about it in the near future. I don't know, if you or anybody else would like to post its answer in this thread, if a VBA solution is mandatory here (I hope not), but I do know that, somehow, at least once I've managed to get it right.

Usually, when using the list box wizard (in a form in which its data source has already been set to be the parameter query of interest), once you select the fields you need, it is not possible to move forward to the next step (in which a sample of the final list box's appeareance is shown). Instead, I get a message like that (translated from spanish): "From the selected data source, data cannot be retrieved. A different query or table must be selected to continue with the wizard". Any wonder of why is this happening??

Everybody here has been very fond with me and the topics I have submitted. I have tried to help other people to the best of my knowledge too. Maybe this forum is not for me yet, but I am happy to post in here. Perhaps anybody could suggest other known forums in which user-level (beginner-intermediate; no VBA use) Access is discussed.

Anyway, I have got different copies of the version that works, so a solution to this enigma is not urgent at all. It is only that I don't know why this happens and I would like to find out. It is also true, and I acknowledge this point, that I should have written down some tips the time I succeded in get it right. In fact, I believed I did but, unfortunately, I'm afraid I did not...

Best regards!

[tkip]

Refer to the thread below. Someone at this post is looking for the solution you have and you are looking for the explanation of the solution.

I posted the codes that does what you describes. I think it should help.

http://bytes.com/forum/thread825372.html

[NeoPa]

Refer to the thread below. Someone at this post is looking for the solution you have and you are looking for the explanation of the solution.

I posted the codes that does what you describes. I think it should help.

http://bytes.com/forum/thread825372.html

I commend you for your courage in even reading that thread.

I'm not sure of the wisdom of directing hitherto sane members that way.

I think the wisest solution is to post a more succinct answer in here. Unfortunately my faculties this evening seem a little impaired (alcoholic sabotage). Please feel free to bump tomorrow if you've not heard more from me.

[limperger]

Hey!

Somehow I made it and this time I have been wise enough to keep some notes on how should it be done. Let me post it later. No VBA needed!!

[NeoPa]

Please do that Limperger :)

I will run my eyes over it for you if you do, and let you know if it seems good sense.

[limperger]

Hello everyone!

Here's my explanation:

First, just to set things straight: I have got an Access file with a main table ("tbl" from now on), and a main form ("frm" from now on) linked to the table (with a listbox that helps to skip from one record to another). There is a memo field intended to store a final comment regarding the whole entry.

The idea here is to search tbl looking for entries that have a keyword the user provides in the memo field (a parameter query is used here). But, taking advantage of frm, results should be displayed using frm; in other words, it is like filtering tbl (by the keyword provided by the user) but displaying the results in a form, instead of a query datasheet.

Beginning with frm, one should expect that changing the data source, for both the listbox and the form, to the parameter query, it should work. But, at least for me, it does not. Also, it doesn't work if you change the data source for the form (and choose the parameter query instead of tbl (the main table)), delete the original listbox and try to create a new one via the listbox wizard. Here (in a step of the wizard) appears the aforementioned (in my previous posts) textbox that the data source chosen is not valid.

I have been able to overcome this situation as follows:

Step 1: Starting with the original form, frm, delete the original listbox. The form's data source is still set to be the main table, tbl.

Step 2: Create a new listbox, using the listbox wizard. Since the form's data source is the main table, the list of fields available comes from the main table, by default.

Step 3: Change the listbox data source to the parameter query, creating a new query in the "data source" option of the listbox properties.

Step 4: Change the main form's data source to the parameter query, instead of the main table.

If Step 4 is implemented, when launching the new form, the user is prompted to enter the keyword twice: for the listbox and for the form. The parameter query launches, then, twice, as it serves as data source for both the form and the litsbox. This can be solved if step 4 is skipped. Then the situation would be the following one: the data source for the listbox is set to be the parameter query but the data source for the form is still set to be the main table, tbl. Obviously, the use of a mousehook here is mandatory so as to assure that users can only skip from one record to another via the listbox and not the mousewheel.

In conclusion, I do not know why the listbox in the form must be created again de novo. But I can safely say that I have tried every possible combination and this is the only one that works. Some kind of Access malfunction here?

I hope I will be understandable and of help!

Best regards