Error 13 when setting rowsource of combobox using a union query

[HowardB]

Hi All,

I shamelessly plagiarised another persons code to try to design an unbound combobox whose rowsource can be dynamically set based on a union query in order to search 1000's of names returning each name that contains a user defined string. The search does not start until the user defined string reaches 3 characters in length.

There are more details in the attachment but basically the vba code I used all saves OK in the vba editor but I get a runtime error 13 type mismatch when the code attempts to utilise the query to populate the rowsource of the combobox.

I am hoping somebody can elighten me as to where the issue lies and what I need to do to fix it,

Thanks in advance

HowardB

 

[jdraw]

A couple of thoughts:

Combo columns are 0 based.

Union ALL <---- will include duplicates --suggest you remove ALL

You have 7 posts, and admit to having "plagiarised" someone's code. How familiar are you with vba? And debugging?

see debugging techniques here.

 

[HowardB]

Hi jd,

Thx for reply, in response to your questions:

1. Learning vba, but I think I get the gist of how the solution is supposed to work. Even if I wasn't a beginner no point in re-inventing the wheel, code's meant to be re-usable right?
2. Combo columns are 0 based, are you saying that if my query has 2 fields NLMCID and [Test Name] that the bound column should be 1 not 2 as I want to search the Test Names?
3. I want duplicates so I can find them if they slip in, I know there are a few there
4. How familiar am I with debugging vba code. Not that familiar but I did use the debug function in access 2013 that's how I arrived at the conclusion of the error code and the location of the lines in the vba code that were causing the problem. It had a big yellow arrow next to it and was highlighted in yellow. I realise this does not necessarily mean the actual problem lies with the rowsource assignment but its a place to start. I do need some help with the debugging though,

hope that helps

 

[jdraw]

OK.
So in plain English WHAT are you trying to do? Pretend you're talking to a six year old.

Why are there duplicates in your data? Part of designing a database is to prevent entry of duplicates.

0 based means column1 is (0) and column2 is (1)....

 

[HowardB]

I'd really not rather talk to you like a 6-year old. The task was explained in the first para of the attached doc that was submitted in the original post to elaborate a little:

Attempting to use a combobox to search a 1000’s of records. Rowsource is a union query with a user defined search string parameter which defines the search string used to search all the [Test Names]. Search will not start until 3 characters have been entered into the combobox" So user starts typing text in the combo once the string gets to 3 characters a search of all the test names is initiated based on the UNION query which looks for test names that contain the search string. The drop down list is populated with the results of the query which is set to be the rowsource for the combo. If the user types a 4th character a new search string is created and the results of that query appear in the drop down list and so on. As more characters are typed the list gets shorter and shorter.

I then plan to use another piece of code to allow the user to click on the test they want I then want to populate the form with the test name and its corresponding ID
Why are there duplicates in my data? Well its a long story but basically its historical data I know there are a few duplicates there because if I try to index certain fields which should be unique (No Duplicates) access throws a wobbly and won't let me do it. Point is when I am done with this project it may become a tool for other people to look at and manage data with and it is they that will have to sanction the cleanup.

 

[jdraw]

When I asked WHAT you are trying to do (at 6 yr old level), I expect something like

...we have 1000's of historical records... we need to search for specific records based on ..some text.... you want to start reducing the search result once the 4th character has been entered.

You have told us HOW you have done what you have done which isn't working.

I suggest you look at an unbound text box and the OnChange event.

see http://bytes.com/topic/access/answers/944856-why-wont-my-onchange-event-work

In this set up with OnChange you will reset the filter every time you key in a character. So in effect it is a constant filtering of your recordsource by means of a changing filter. You could control the fact you need the change event to function after 3 characters by counting the length of the user input. If 3 or less just exit the routine; if >3 then do the change logic.

Good luck with your project.

 

[nanscombe]

Me.cboPTest.RowSource = "SELECT tbl_Requests.[Request NLMCID] AS NLMCID, tbl_Requests.[Request Display Name] AS [Test Name]FROM tbl_Requests WHERE tbl_Requests.[Request Display Name]  LIKE " * " & sNewStub & " * " UNION ALL SELECT tbl_ReqAltNames.[Request NLMCID] AS NLMCID, tbl_ReqAltNames.[Alternative name] AS [Test Name] FROM tbl_ReqAltNames WHERE tbl_ReqAltNames.[Alternative name] LIKE " * " & sNewStub & " * " ORDER BY NLMCID;"

Are you actually using double quotes inside your SQL string ( LIKE " * " & sNewStub & " * " )?

Looking at those extra spaces surrounding the *s, I wonder if it's messing up your SQL.

Try using single quotes instead string ( LIKE '*" & sNewStub & "*' ).

Also your first SQL statement is missing a space before the FROM satatement. ( Test Name]FROM )

Me.cboPTest.RowSource = "SELECT tbl_Requests.[Request NLMCID] AS NLMCID, tbl_Requests.[Request Display Name] AS [Test Name] FROM tbl_Requests WHERE tbl_Requests.[Request Display Name]  LIKE '*" & sNewStub & "*' UNION ALL SELECT tbl_ReqAltNames.[Request NLMCID] AS NLMCID, tbl_ReqAltNames.[Alternative name] AS [Test Name] FROM tbl_ReqAltNames WHERE tbl_ReqAltNames.[Alternative name] LIKE '*" & sNewStub & "*' ORDER BY NLMCID;"

 

[nanscombe]

I'm just trying a bit of coding trickery to get the relevant bit to be a bit more readable.

Dim strSQL as String
...

  strSQL = "SELECT T1.[Request NLMCID] AS NLMCID," _
  & " T1.[Request Display Name] AS [Test Name]" _
  & " FROM tbl_Requests AS T1 WHERE T1.[Request Display Name]" _
  & " LIKE '*" & sNewStub & "*'" _
  & " UNION ALL" _
  & " SELECT T2.[Request NLMCID] AS NLMCID," _
  & " T2.[Alternative name] AS [Test Name] FROM" _
  & " tbl_ReqAltNames AS T2 WHERE T2.[Alternative name]" _
  & " LIKE '*" & sNewStub & "*'" _
  & " ORDER BY NLMCID;"

Me.cboPTest.RowSource = strSQL

Using " _" at the end of one line followed by "&" on the next line allows a single line of code to be split onto multiple lines.

FROM tbl_Requests AS T1 and tbl_ReqAltNames AS T2 are examples of SQL aliases.

 

[HowardB]

jd thanks for your help and suggestion of an alternative approach but my gold star goes to nanscombe who took the time to look at the code in detail and nailed the problem. I corrected the SQL spacing and used the single quotes as you suggested and it works like a charm tyvm Nigel

Oh just seen your second post yes I was thinking about using strSQL to tidy up the code appearance will get onto that next, many thanks

 

[nanscombe]

You're not the first, and certainly won't be the last, to encounter a problem trying to put a " in the middle of a string.

Access sees individual " or ' as the start or end of a string.

strSQL = "... Like " * " & me.txtSearch & " * " ..."

would probably be seen as ...

strSQL = [COLOR="Red"]"... Like "[/COLOR] * [COLOR="DeepSkyBlue"]" & me.txtSearch & "[/COLOR] * [COLOR="Red"]" ..."[/COLOR]

I know of two ways of getting around it.

1) Use single quotes instead ..

strSQL = [COLOR="Red"]"... Like '*"[/COLOR] & me.txtSearch & [COLOR="DeepSkyBlue"]"*' ..."[/COLOR]

2) Double up the quotes, so Access interprets it as a literal character instead ...

strSQL = [COLOR="Red"]"... Like ""*"[/COLOR] & me.txtSearch & [COLOR="DeepSkyBlue"]"*"" ..."[/COLOR]

A bit more explantation can be found in the article Quotation marks within quotes.



Using ' inside the string would be fine until such time as there was a ' in the middle of me.txtSearch then you would be back to square one.

Imagine me.txtSearch was the word "Johnson's"

strSQL = [COLOR="DeepSkyBlue"]"... Like [/COLOR][COLOR="Red"]'*" & "Johnson'[/COLOR][COLOR="DeepSkyBlue"]s"[/COLOR] & [COLOR="Magenta"]"*' ..."[/COLOR]

Whoops, the string is messed up again.

What can you do about it? Use the Replace() function to replace any ' in the string with ''.

So to bulletproof your SQL

strSQL = "... Like '*" & me.txtSearch & "*' ..."

would become ...

strSQL = "... Like '*" & Replace(nz(me.txtSearch), "[COLOR="Red"]'[/COLOR]", "[COLOR="Red"]''[/COLOR]") & "*' ..."

That would ensure that any single quote (') would be replaced with a pair ('') which would be interpreted as a literal character instead.

strSQL = [COLOR="DeepSkyBlue"]"... Like [/COLOR][COLOR="Red"]'*" & "Johnson''s" & "*'[/COLOR][COLOR="DeepSkyBlue"] ..."[/COLOR]

I hope this is reasonably clear.

 

[HowardB]

Actually Nigel its worse than you think

In the VBA code using this syntax LIKE '*" & sNewStub & "*' as you suggested makes the query work within the combobox routine

If I translate that syntax approach to my standalone Access query which works but uses LIKE "*" & sNewStub & "*" It fails to return any hits replacing the double quotes works and returns the expected records.

It seems like a bit of an inconsistency crying out for standardisation within the Access product to make it more usable but I am just a beginner what do I know

Thanks again so much for your help it was an education