Please check rather long SQL statement

[stonward]

Hi,

Can someone who understands SQL syntax check the following for me?
TxtSearch2 is a textbox on a Form, Form1 which also contains a listbox, list0.
This is a test page to get the SQL statement working directly in the code window. I'm doing this because since moving a listbox into a Tab Control, the criteria for the underlying query no longer works - or rather, I don't know the syntax for addressing a listbox in a tab from the query design window.

I'm pretty sure the problem is with the third SQL line (WHERE clause), since the listbox should just show stock items, but it's showing them all - I guess it's an issue with the bracketing?

Dim strsql As String

strsql = "SELECT Products.ProductID, [categories.categoryname] & ' ' & [Widths] & [Series] & [Rating] & [Diameters] & ' ' & [Manufacturer] & ' ' & [Notes] AS Product,Products.Trade, Products.StockLevel "

strsql = strsql & "FROM Categories INNER JOIN Products ON Categories.CategoryID = Products.categoryid "

strsql = strsql & "WHERE (([categories.categoryname] & [Widths] & [Series] & [Rating] & [Diameters] & [Manufacturer] & [Notes]) Like '*') & [Forms]![form1]![txtSearch2] & '*') AND ((Products.StockLevel)>0) "

strsql = strsql & "ORDER BY Products.CategoryID, Products.Series DESC , Products.Diameters, Products.Widths, Products.Rating;"

Thanks,
S

[stonward]

Just a quick PS:

I know that objects in a tab control are considered to be on the main form, but I still can't get my 'usual' query criteria to work as it does when the listbox is actually on the main form: still working on that, but please check and advise with SQL statement? Thanks.

[missinglinq]

Where does this code appear? I it resides in one of the Listbox events, you need to "reconnect" the Listbox and its event code after moving the control to a tabbed page.

Courtesy of ADezii, this code will "reconnect" controls to some selected Events (OnClick and AfterUpdate in this example.) It can be modified for other Events, and has the advantage of updating a large number of controls without doing them one by one.

Private Sub Form_Load()
Dim ctl As Control

For Each ctl In Me.Controls
If (TypeOf ctl Is TextBox) Or (TypeOf ctl Is ComboBox)  Or (TypeOf ctl Is ListBox) Then
If ctl.OnClick = "" Then
ctl.OnClick = "[Event Procedure]"
End If
End If
Next

For Each ctl In Me.Controls
 If (TypeOf ctl Is TextBox) Or (TypeOf ctl Is ComboBox)  Or (TypeOf ctl Is ListBox) Then   If ctl.AfterUpdate = "" Then
     ctl.AfterUpdate = "[Event Procedure]"
   End If
 End If
Next
End Sub

Alternatively, in Design View, you can select a control, go into Properties - Events and click on the event in question, to take you to the code window, as if you were setting it up for the first time. Once in the code window, simply return to Design View. The control is now "connected" to its code and the hotkey will work. The disadvantage to this is that it's time consuming if it involves a lot of controls.

Linq ;0)>

[NeoPa]

Ston,

Do you want the code checked, or the SQL?

If it is the SQL then it would be easier for us (and make sense for you too) to print the strSQL string off first and post that.

If it is the code that you fear may not be working well for you, then I suggest you follow these guidelines (for this and similar situations) :

One of the most popular (frequently occurring rather than best liked) problems we get is with SQL strings being manipulated in VBA code.

The reason this is so difficult is that all the work is being done at a level of redirection. What I mean by this is that the coder is never working directly with the SQL itself, but rather with code which in turn, is relied on to produce the SQL that they are envisaging is required. It's rather similar to the problems coders have historically had dealing with pointers.

Anyway, a technique I often suggest to coders struggling with this (at any level. This happens to experienced coders too.) is to use either the MsgBox() function, or Debug.Print into the Immediate Pane of the debugger window, to display the value of the SQL in the string before using it (That's assuming you're not good with debugging generally. Personally I would trace to the line, then display the value prior to allowing execution of the string - See Debugging in VBA). It's really much easier to appreciate what a SQL string is meant to do, and where there may be problems, when you can see it in its entirety, and in its true form, rather than as the code is about to create it.

[stonward]

Wow, thanks Guys - two really cool answers that will keep me up tonight trying it all out: how can there be still so much to learn with Access afer working with it for so long?!
Two great answers - can't choose between them for 'best' reply. But choose I must....be assured I am forever grateful for this type of input.
Speak with you soon I hope,

Ston

[NeoPa]

Right back at you Ston.

You can be assured we find helping people to learn, rather than providing boilerplate answers, so much more gratifying.