Access Macro to Show All in Query in Form

squeakums

Well-known Member
Joined
May 15, 2007
Messages
723
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?
 

Excel Facts

Excel motto
Not everything I do at work revolves around Excel. Only the fun parts.

xenou

MrExcel MVP
Joined
Mar 2, 2007
Messages
16,836
Office Version
  1. 2019
Platform
  1. Windows
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
723
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
Joined
Mar 2, 2007
Messages
16,836
Office Version
  1. 2019
Platform
  1. Windows
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
723

ADVERTISEMENT

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
Joined
Mar 2, 2007
Messages
16,836
Office Version
  1. 2019
Platform
  1. Windows
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
723
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
Joined
Mar 2, 2007
Messages
16,836
Office Version
  1. 2019
Platform
  1. Windows
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
 

Watch MrExcel Video

Forum statistics

Threads
1,132,902
Messages
5,655,879
Members
418,249
Latest member
JOYADA

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top