Access 2016 Using combobox in SQL query (1 Viewer)

tonylomax

Registered User.
Local time
Today, 16:53
Joined
Oct 19, 2018
Messages
16
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

Well-known member
Local time
Today, 12:53
Joined
Apr 9, 2015
Messages
4,339
instead of using the combo in the query, just filter the records.
show all records in the form, then when user picks one :

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

tonylomax

Registered User.
Local time
Today, 16:53
Joined
Oct 19, 2018
Messages
16
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

AWF VIP
Local time
Today, 16:53
Joined
Jul 26, 2013
Messages
10,354
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

Registered User.
Local time
Today, 16:53
Joined
Oct 19, 2018
Messages
16
@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

MVP / VIP
Local time
Today, 16:53
Joined
Jan 14, 2017
Messages
18,186
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.
Code:
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
Code:
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
 
Last edited:

MajP

You've got your good things, and you've got mine.
Local time
Today, 12:53
Joined
May 21, 2018
Messages
8,463
As previously stated the property is Column not Columns, although indexed and seems like it should be Columns
Code:
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

MVP / VIP
Local time
Today, 16:53
Joined
Jan 14, 2017
Messages
18,186
Thanks. Corrected the code I had copied without thinking
 

Users who are viewing this thread

Top Bottom