Smart Search/Dumb Programmer - Populating a combo box; Error 2118

tfurnivall

Registered User.
Local time
Today, 09:35
Joined
Apr 19, 2012
Messages
81
I'm learning by example (and by failure - who ever learned anything from success?) about an Access 2010 combobox.

This is an unbound control on an unbound form. and I'm using it to display an increasingly refined set of entries from a table. Just like the thing that Google does when you're searching. Except in Access.

I have a combo box, and a set of fields (which correspond to the fields in a table entry).

In the combo box, as I type, I want to display results that match the value I'm typing. What I'm doing to facilitate this is setting the combo box RowSourceType to "Table/Query", and then constructing a SQL select statement to use as the actual RowSource value.

I can construct the SQL statement, and it returns the values I want. So I return that string from my "ConstructA SQL Select Statement: function, and put it into the combo box:
Code:
Private Sub cboSmartSearch_Change()

Dim SearchKey As String
Dim SQLCommand As String

SearchKey = Me.cboSmartSearch.Text
SQLCommand = SmartSearch(SearchKey)
Me.cboSmartSearch.RowSourceType = "Table/Query"
Me.cboSmartSearch.RowSource = SQLCommand
Me.cboSmartSearch.Requery

End Sub
The SmartSearch function is operating fine. I've thrown all manner of test cases (including some pretty perverse ones) at it, and it does what it's meant to. The code gets as far as the Requery statement, when I get the following (apparently well known) message:
Code:
Run time error '2118'
You must save the current field before you run the Requery action.
SO I'm intrigued. This is an unbound control on an unbound form, and I am having trouble figuring out
a) What 'field' to save (the combo box? the SQL statement?)
b) Where to save it and
c) How to tell Access that I've done this

Doing a search on error 2118 leads to all sorts of suggestions, but most of them are either incredible complex situations (this is a single stand-alone form with just a few fields) or are tightly bound to underlying tables etc.

I'm only using Access as a sort of front-end User Interface, and I don't want to commit to a bound form. (I know this is a red flag to some people, sorry)

Can anyone give me an idea of how to solve this annoying little problem?

Thanks,

Tony
 
Presumably the combo's type during development is already "Table/Query"? Specifying that is unnecessary.
Given that you're setting the RowSource property, then calling the Requery method is redundant. (By setting that, you've already requeried it.)

Do you not see the results you want?

(Resisting "red flag" urges to question why the avoidance of a form - and yet deeming a combo acceptable... Well... sorta - and also essentially asking just that right now. :-p)

Is the rest of your question asking about using an unbound form to perform data updates? I'm not at all sure if you are.

Cheers
 

Users who are viewing this thread

Back
Top Bottom