Runtime 2185 when updating wilcard search

[Thomasso]

Hello,

I'm trying to do "Search as you type" continuous form:

Private Sub txtCitySearch_Change()
    Me.Refresh
    
    Me.RecordSource = "SELECT CountryName, RegionName, CityName, ZIPCode " & _
                        "FROM dbo_vw_AddressDatabase " & _
                        "WHERE CountryName LIKE ""%" & txtCountrySearch & "%"" AND " & _
                        "RegionName LIKE ""%" & txtRegionSearch & "%"" AND " & _
                        "CityName LIKE ""%" & txtCitySearch & "%"" AND " & _
                        "ZIPCode LIKE ""%" & txtZIPSearch & "%"";"
    
    txtCitySearch.SetFocus
    txtCitySearch.SelStart = Nz(Len(txtCitySearch), 0)
    
End Sub

This works fine as long as it actually returns some rows. But once I type some non-existent search which doesn't return data, I get Runtime 2185 error.

Would you by any chance have any ideas how I could fix it?



Thanks a lot!

Tomas

[June7]

What is the exact error message?

[CJ_London]

if this is a query on an access table you need to use *, not % which is used with ADO and many rdbms's. access tables use DAO

I would also say this will become a very slow process once you have a large dataset since you are bringing data over the internet every time you add or remove a character. Plus the use of the initial '%' or '*' prevents the use of indexing so access has to do a sequential search of the whole table. I don't include the initial * when building sql like this - but train the user to use it when required, or include a button the user can click to add the * for them.

I would not bring any records through until the user has finished typing and then clicks a 'search' button to requery the form.

Or if you need to do it this way wait until a meaningful number of characters have been typed (perhaps 3 or 4) before refreshing the recordsource.

[Thomasso]

Hi,

it's an SQL Server View.

This is the error message:



This will never be more than a couple of hundreds records, so performance is no concern for me.

[Welshgasman]

Add some error checking?

[CJ_London]

Please don't be lazy - provide the translation of the error

Access VBA - Error '2185' - Can't reference a property unless the control has the focus


you need to step through the code to find out which line is causing the problem - note the change event only fires when the control has the focus

good luck

[Thomasso]

I don't see how googling a runtime number is more complicated than translating. My language is much more complicated than English and my translation would not necessarily reflect the real meaning of the runtime error message.

This is the source view:



This is the code and the error line:


It's actually quite strange. I didn't have the .SetFocus there before, but then the error would trigger after every change.
Now it works when there are records that match the search, but the error triggers when there is no record matching the search.

I would appreaciate any insights Thanks.

[CJ_London]

I don't see how googling a runtime number is more complicated than translating.

You are the one asking for help, the easier you make to for people to help you, the more likely you are to get help. We are all volunteers but want to make best use of our time, not waste time 'filling in the blanks'.

here is a link to the change event
https://docs.microsoft.com/en-us/off...textbox.change

suspect you need to use the .text property for what you are trying to do

[June7]

Googling an error number does not always provide information sufficient to understand the error and as noted, is time consuming. Always provide the exact error message with translation as well as the line that triggers error.

I tested SelStart when form does not have records and don't get error. However, did not test with Change event. I have never used it.

Review http://allenbrowne.com/AppFindAsUType.html

[Thomasso]

Thanks a lot everyone, I will try to provide better info in the future.