By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,471 Members | 2,264 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,471 IT Pros & Developers. It's quick & easy.

Please check rather long SQL statement

stonward
100+
P: 145
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?

Expand|Select|Wrap|Line Numbers
  1. Dim strsql As String
  2.  
  3. strsql = "SELECT Products.ProductID, [categories.categoryname] & ' ' & [Widths] & [Series] & [Rating] & [Diameters] & ' ' & [Manufacturer] & ' ' & [Notes] AS Product,Products.Trade, Products.StockLevel "
  4.  
  5. strsql = strsql & "FROM Categories INNER JOIN Products ON Categories.CategoryID = Products.categoryid "
  6.  
  7. strsql = strsql & "WHERE (([categories.categoryname] & [Widths] & [Series] & [Rating] & [Diameters] & [Manufacturer] & [Notes]) Like '*') & [Forms]![form1]![txtSearch2] & '*') AND ((Products.StockLevel)>0) "
  8.  
  9. strsql = strsql & "ORDER BY Products.CategoryID, Products.Series DESC , Products.Diameters, Products.Widths, Products.Rating;"
  10.  
Thanks,
S
May 11 '10 #1

✓ answered by 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.

Expand|Select|Wrap|Line Numbers
  1. Private Sub Form_Load()
  2. Dim ctl As Control
  3.  
  4. For Each ctl In Me.Controls
  5. If (TypeOf ctl Is TextBox) Or (TypeOf ctl Is ComboBox)  Or (TypeOf ctl Is ListBox) Then
  6. If ctl.OnClick = "" Then
  7. ctl.OnClick = "[Event Procedure]"
  8. End If
  9. End If
  10. Next
  11.  
  12. For Each ctl In Me.Controls
  13.  If (TypeOf ctl Is TextBox) Or (TypeOf ctl Is ComboBox)  Or (TypeOf ctl Is ListBox) Then   If ctl.AfterUpdate = "" Then
  14.      ctl.AfterUpdate = "[Event Procedure]"
  15.    End If
  16.  End If
  17. Next
  18. 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)>

Share this Question
Share on Google+
5 Replies


stonward
100+
P: 145
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.
May 11 '10 #2

missinglinq
Expert 2.5K+
P: 3,532
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.

Expand|Select|Wrap|Line Numbers
  1. Private Sub Form_Load()
  2. Dim ctl As Control
  3.  
  4. For Each ctl In Me.Controls
  5. If (TypeOf ctl Is TextBox) Or (TypeOf ctl Is ComboBox)  Or (TypeOf ctl Is ListBox) Then
  6. If ctl.OnClick = "" Then
  7. ctl.OnClick = "[Event Procedure]"
  8. End If
  9. End If
  10. Next
  11.  
  12. For Each ctl In Me.Controls
  13.  If (TypeOf ctl Is TextBox) Or (TypeOf ctl Is ComboBox)  Or (TypeOf ctl Is ListBox) Then   If ctl.AfterUpdate = "" Then
  14.      ctl.AfterUpdate = "[Event Procedure]"
  15.    End If
  16.  End If
  17. Next
  18. 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)>
May 11 '10 #3

NeoPa
Expert Mod 15k+
P: 31,186
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.
May 12 '10 #4

stonward
100+
P: 145
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
May 12 '10 #5

NeoPa
Expert Mod 15k+
P: 31,186
Right back at you Ston.

You can be assured we find helping people to learn, rather than providing boilerplate answers, so much more gratifying.
May 13 '10 #6

Post your reply

Sign in to post your reply or Sign up for a free account.