Having Combo Box in MS ACCESS show all possible results

0
votes

I have an extremely wide dataset with a lot of columns. Most only have only 2-6 possible options. The user may have different options in the future for these columns.

My solutions is to use MS Access to create a search form to allow the user to find records based on input, most of these columns being combo box's.

I have successfully gotten distinct values for my combo boxs and returned values, however I only get one record returned when for example selecting "Yes" even though my data set has many that match Yes. I have tried to use a LIKE encapsulated with * and even hard corded YES but get the same results. When I Choose "No" I get no results and I expected to get the one record with "No". How can I get all the records? Also I would like my combo box to start blank and or include a blank option.

My Row Source

SELECT DISTINCT WeddingList.[Location Shots/drone shots] FROM WeddingList;

My "After Update" VBA

Private Sub cboLocationShot_AfterUpdate()
Dim locationShot As String
locationShot = "Select * from WeddingList Where ([Location Shots/drone shots] LIKE '*" & Me.cboLocationShot & "*')"
'locationShot = "SELECT * from WeddingList WHERE [Location Shots/drone shots] LIKE '*YES*'"
Me.WeddingList_subform2.Form.RecordSource = locationShot
Me.WeddingList_subform2.Form.Requery

Screenshot of my Form

2
ms-accesscomboboxdistinct
however I only get one record returned - where? This is unclear. – Andre
I edited my question, please let me know if that clarifies the problem. – Jacob Lynn
Hm, this should (both) work. Things to check: are there any additional filters on the subform? -- How to debug dynamic SQL in VBA i.e. paste your generated SQL into a new query. – Andre
I do not have any other filters on the subform. This will be my first combo box, however I will have to create many more once I get the syntax down. – Jacob Lynn
Also I ran my SQL into a new query in SQL View and the output was as expected. i.e. I had to hardcode NO and YES values as I couldn't use Me.cboLocationShot but it returned all of the records that matched YES when chosen and all the records that matched NO when chosen. – Jacob Lynn

2 Answers

0
votes

In your code, there is no column specified in the 'LIKE' match that refers to your combo box. The match will occur on the bound column - usually the first one in the combo box.

0
votes

Here is what solved my problem.

Select * FROM Mytable Order By Desc

Run the form

Re add the logic coming from my combo box.