Access Macro to Show All in Query in Form

squeakums

Well-known Member
Joined
May 15, 2007
Messages
602
Currently, my form has a combo box, and it is only showing that in which is selected in the combo box. How can I have it show all items item an item is selected in the combo box? Macro? If so, how would I write that please?
 

xenou

MrExcel MVP, Moderator
Joined
Mar 2, 2007
Messages
16,510
The combo box record source should not be the same as the bound item (the record that it is bound to).

For instance, the bound item is one single product for an actual order line, while the record source is the list of possible products that can be chosen for that order line.
 

squeakums

Well-known Member
Joined
May 15, 2007
Messages
602
I apologize, but that was not helpful at all. I actually, have my record source bound to the name column and if it isn't the query won't function at all with the combo box?
 

xenou

MrExcel MVP, Moderator
Joined
Mar 2, 2007
Messages
16,510
I apologize too but it's not clear what your problem is - I'm guessing English is not your first language. I think you should give an example with some sample values.
 

squeakums

Well-known Member
Joined
May 15, 2007
Messages
602
I have a sub query in a form. Due to the secure information I am unfortunately unable to share a screen shot of this data. The query only shows for example the names I have setup for the Link Master and Link Child Fields, if I remove that out, it will show all instances in the query. I would like the query to show all instances, but also function with the combo box I created. The combo box, only seems to work when I have the Link Master and Link Child fields setup. I feel like I am missing a setup of some type? The combo box is has a search for the name. I would either like the combo box to have an option or need a macro to show all names in the query as well, and I tried the show all macro but it doesn't work when link master and link child fields are selected. Sorry, hope that helps.
 

xenou

MrExcel MVP, Moderator
Joined
Mar 2, 2007
Messages
16,510
What is the purpose of the combo box? How many names would you expect to be in the combo box when you search for names in the combobox (10 names, 100 names, 100,000 names?)
 

squeakums

Well-known Member
Joined
May 15, 2007
Messages
602
It's only about 20 names. The combo box is used as a quick search field with the query table. It works when the Master Link and Child Link are setup with Name; and it doesn't work when nothing is selected for Master and Child Links (but all data does show). It doesn't when those are deselected. The query box, I'd prefer to show all instances unless they user wants to narrow down using the combo box. I'd like the best of both abilities. Either have some sort of macro that shows all in the query.. with Master and Child Link filled in... or some other fix to where I can show all instances in the query box until something is selected in the combo box.. I don't know why access makes this so darn complicated?
 

xenou

MrExcel MVP, Moderator
Joined
Mar 2, 2007
Messages
16,510
Master and Child are for finding related records only. It cannot be used as part of a generalized search.

The simplest way to search is to right click in a bound field and use the in-built access search features.

Other than that it is is little complicated (this is not Access fault for making it complicated. When you set up a bound field you are saying "please show the value for this field for this record". But a search field is saying "please let me pick any value for this field from any record". These are incompatible things (for navigation, at any rate).

Here is an example of how to create your own search combobox for a form:
http://allenbrowne.com/ser-03.html
 

Forum statistics

Threads
1,077,849
Messages
5,336,736
Members
399,100
Latest member
darcob

Some videos you may like

This Week's Hot Topics

Top