Access 2016 Using combobox in SQL query

[tonylomax]

I'm trying to create a query that uses the selection of a single combobox as part of the WHERE clause.

The query:

SELECT Database.Contact_ID, Database.[Full Name], Database.[Job Title],
Institution.Institution, Database.Email, Database.[Email 2], Database.[Work
phone], Database.[Work phone 2], Database.Mobile, Database.Notes, Database.
[Date created], [Keyword Junction].Keywords, Database.[Record Type]

FROM Institution INNER JOIN (Keywords INNER JOIN ([Database] INNER JOIN
[Keyword Junction] ON Database.[Contact_ID] = [Keyword Junction].Contact_ID)
ON Keywords.Keyword_ID = [Keyword Junction].Keywords.Value) ON
Institution.ID = Database.InstitutionLookup

WHERE ((Keywords.Keyword)=[Forms]![Keyword Search Mk 2]![SelectKeyword]);

The 'Keyword Search Mk 2' form is a simple pop out form with the combobox that allows the user to select the desired keyword. I can select the keyword in the form but when I run the search the results are empty.

The previous WHERE clause was:

WHERE ((Keywords.[Keyword]) Like "*" & [Forms]![Keyword Search Mk 2]!
[SelectKeyword] & "*");

This had the same issue. I've also tried specifying the column number e.g.

WHERE ((Keywords.[Keyword]) Like "*" & [Forms]![Keyword Search Mk 2]!
[SelectKeyword].[Columns](1) & "*");

Again with no luck.

Appreciate any suggestions, thanks.

 

[Ranman256]

instead of using the combo in the query, just filter the records.
show all records in the form, then when user picks one :

sub cboBox_Afterupdate()
If IsNull(cboBox) Then
  Me.FilterOn = False
Else
  Me.Filter = "[CCode]='" & cboBox & "'"
  Me.FilterOn = True
End If
end sub

 

[MajP]

Not sure if the problem, but two things.
Weirdly the property is Column not Columns
https://docs.microsoft.com/en-us/office/vba/api/access.combobox.column
You would think Columns was correct.
They property is also 0 indexed. The first column is 0, second is 1.

 

[tonylomax]

Thanks for the replies. @MajP I've adjusted the query as suggested and no luck, I get an Undefined Function error.

@Ranman256 I will look into your suggestion though that would make the interface to my database inconsistent between different searches. i.e. I have other queries that work in the way I describe and want to keep things consistent for my users.

 

[Minty]

You can't refer to a combo column in a saved query unfortunately, only the bound column value.

If the form is a pop up is it closed again before the query actually runs ?

 

[tonylomax]

@Minty The form does close however I've figured out what I needed to do. I didn't realise you needed to specify which column was bound even if you only have one column visible on the combo box. I change the bound column and now I can run the search in the way I wanted.

Thanks for the replies, I knew it would be something obvious I was missing.

 

[isladogs]

If you need to refer to a column that isn't the bound column in a query, then do one of the following

1. Set a public variable equal to the value required e.g.

strSelection = [Forms]![Keyword Search Mk 2]![SelectKeyword].[Column](1)

However variables can't be used in query criteria
So create a function in a standard module

Public Function GetSelection()
          GetSelection=strSelection
End Function

Then reference the function GetSelection() in your query criteria

2. Alternatively, set a Tempvar equal to your required value
Tempvars can be used in query criteria

 

[MajP]

As previously stated the property is Column not Columns, although indexed and seems like it should be Columns

From this
strSelection = [Forms]![Keyword Search Mk 2]![SelectKeyword].Column[COLOR="Red"]s[/COLOR](1)
to
strSelection = Forms![Keyword Search Mk 2]!SelectKeyword.Column(1)

 

[isladogs]

Thanks. Corrected the code I had copied without thinking