How do i make a live search function with a text box and query associated with a continuous form?

Question

Hello i'm trying to build a live search function in MS Access 2013 with a text box and continuous form.The text box and continuous form are in the same form, though the text box are in the header. This form is a subform of one other form, if it matters, let me know.

I'm trying to updated the continuous form with what the user is typing in the txtSearch control. The control have an On Change event, which will trigger the code behind when the controls value changes. With the query below I'm receiving run-time error 13. I'm not sure how to tackle it.

Below I've added additional information for those who find it helpful.

Control names:

txtSearch

Code Behind: I've made a query in the original query to make the search query smaller and more readable. Not sure if this affects the search ability. All fields are supposed to be searchable though I've broken it down to one here to test it.

Private Sub txtSearch_Change()
    SQL = "SELECT [qryPallställDetails].[SLA_ID], [qryPallställDetails].[ArtNr], [qryPallställDetails].[Benämning], [qryPallställDetails].[Saldo], [qryPallställDetails].[DtmReg], [qryPallställDetails].[InvDtm], [qryPallställDetails].[PV] " _
                & "FROM [qryPallställDetails]" _
                & "WHERE ((([qryPallställDetails].[Benämning])= " & Me.txtSearch.Text & "));"
    Me.RecordSource = SQL
    Me.Requery
End Sub

Original Query:(Not vb formatted.)

SELECT tblSLA.*, tblVerk.Verk, [tblVerkShelfs].[ShelfNumber] & [tblVerk_Place].[Place] AS VP, 
[tblPallställ].[PName] & [tblPallställSection].[Section] & [tblPallställ_Row].[pallställRow] & [tblPallställPlace].[Place] AS PV,
[tblVerk].[Des] & [tblVerkShelfs].[ShelfNumber] & [tblVerk_Place].[Place] AS VD

FROM tblVerk
RIGHT JOIN (tblVerkShelfs
RIGHT JOIN (tblVerk_Place
RIGHT JOIN ((tblSLA
LEFT JOIN (tblPallställ

RIGHT JOIN (tblPallställSection
RIGHT JOIN (tblPallställPlace
RIGHT JOIN (tblPallställ_Row
RIGHT JOIN tblPallställData

ON tblPallställ_Row.PallställRow_ID = tblPallställData.PallställRow_ID)
ON tblPallställPlace.PallsällPlace_ID = tblPallställData.PallsällPlace_ID)
ON tblPallställSection.PallställSection_ID = tblPallställData.PallställSection_ID)
ON tblPallställ.Pallställ_ID = tblPallställData.Pallställ_ID)
ON tblSLA.SLA_ID = tblPallställData.SLA_ID)

LEFT JOIN tblVerkData ON tblSLA.SLA_ID = tblVerkData.SLA_ID)
ON tblVerk_Place.VerkPlace_ID = tblVerkData.VerkPlace_ID)
ON tblVerkShelfs.Verk_Shelf_ID = tblVerkData.Verk_Shelf_ID)
ON tblVerk.Verk_ID = tblVerkData.Verk_ID;

I've been looking for an answer, though i have not found an answer on what i'm trying to do.

[How to debug dynamic SQL in VBA](http://stackoverflow.com/questions/418960/managing-and-debugging-sql-queries-in-ms-access/1099570#1099570). -- Use Gustav's [`CSql()` function](https://stackoverflow.com/a/36494189/3820271) when concatenating variables with SQL. — Andre, Jun 20 '17 at 08:55
@Nathan_Sav The formatting with symbols such the " ain't effected by spaces. I've have tried your suggestion in other projects, though i have not noticed a difference. It works fine with normal records regardless where the space is. Though i don't know if it affects the performance. — , Jun 20 '17 at 09:08
@Andre, That's mainly for error handling right? Interesting topic, i'll take a closer lock at it, thx for the share. :) — , Jun 20 '17 at 09:16
so `"FROM [qryPallställDetails]WHERE ((([qryPallställDetails].[Benämning])= ` is legal SQL? — Nathan_Sav, Jun 20 '17 at 09:17
@Nathan_Sav: in Access, it actually works, because of the [square brackets]. But IMHO it's rather ugly and should be avoided. — Andre, Jun 20 '17 at 09:18
@TobiasOlsson: Yes, it's mainly for error handling, but since you get an error, it's appropriate. :) If you'd run your final query in the Access query editor, it would give you better error information. — Andre, Jun 20 '17 at 09:24
Yes, in Access it works well. Mainly because the square brackets wrapping. How ugly it is, is negotiable, however if more spaces increases the performance or makes it easier for Access to recognize the formatting it changes the matter. — , Jun 20 '17 at 09:29
@Nathan_Sav, whenever i write a query and have to use in the vba, i tend to wrap all fields in brackets. Because it increases preference and makes it easier for access to format the fields, variables and local controls. It also prevents spaces for causing an error. — , Jun 20 '17 at 09:33
@TobiasOlsson : Me to, but was unaware that the braces negated the need for the spaces. Depends on what you like I suppose. :) — Nathan_Sav, Jun 20 '17 at 09:41

score 0 · Accepted Answer · answered Jun 20 '17 at 09:22

Runtime error 13 is Type mismatch.

You have (I assume) a text column [Benämning] and compare it with a string, but without using quotes.

The short-sighted solution would be to put single quotes around it:

& "WHERE ((([qryPallställDetails].[Benämning])= '" & Me.txtSearch.Text & "'));"

But this fails again when your search string itself contains a single quote.

So: Use Gustav's CSql() function when concatenating variables with SQL. It handles string and other variables.

& "WHERE ((([qryPallställDetails].[Benämning])= " & CSql(Me.txtSearch.Text) & "));"

Yes, the column "Benämning" text. The few columns that ain't text is DtmReg, InvDtm and the PK. Is formatted as date. — , Jun 20 '17 at 09:38

How do i make a live search function with a text box and query associated with a continuous form?

1 Answers1