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
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)> 5 1484
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.
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 32,556
Expert Mod 16PB
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.
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 32,556
Expert Mod 16PB
Right back at you Ston.
You can be assured we find helping people to learn, rather than providing boilerplate answers, so much more gratifying.
Sign in to post your reply or Sign up for a free account.
Similar topics
by: Testor |
last post by:
I am writing a simple perl script to learn in the process. The script
is supposed to grab a receipt number from the query-string and write
it into a file.
To do that, here's what I did:
...
|
by: Peter Jenkins |
last post by:
http://www.safenz.org.nz/New/index.htm
Only link working off this currently is the sitemap.
I have all the access keys going except the skip navigation
Please advise if any accessibility issues...
|
by: Kevin P |
last post by:
Hi News Group,
In an application written in C# and VS.Net using Dotnet Framework 1.1.4322
on XP and Win 2000 machines, we randomly get the error "Failed to load
resources from resource file....
|
by: Preference |
last post by:
Hello,
I am developing a .NET application in managed C++. Until now we have
executed without problems in a lot of different computers. But a few days ago
we detect a problem in some laptops. In...
|
by: Tee |
last post by:
Hi,
my question is as the topic, can we make a long statement become short by
pre-declare it.
sorry if you can't understand what I asked, as I am not too sure how to ask
it, but below is the...
|
by: Vijaya P Krishna |
last post by:
Hi,
I have a .NET Windows Forms application, written in VB.NET and C#.
I am opening a URL from the application using Process.Start(). The URL
points to a java servlet running on apache-tomcat....
|
by: raghu |
last post by:
I have written code for single linked list...I get an error as
Expression syntax in the line marked with ////////. Please correct me
where am I going wrong. I compiled this in TURBO Compiler
...
|
by: icetalks |
last post by:
have a look at this code , its for logging the user in after checking his UserName and Password.
dim check as boolean = false
...
...
If txtUserName.Text.Length = 0 And txtPass.Text.Length =...
|
by: Logan Lee |
last post by:
It is at http://211.30.198.135/pointer_operations.html. I want to get this reviewed to make sure that they are correct.
I'm not sure whether copy/paste will show up correctly but the content is:
...
|
by: shapper |
last post by:
Hello,
I have two tables: Polls and Options:
Poll PollID, Question
Options OptionID, PollID, Answer
I want to select a Poll given its ID and all Options associated to
it.
Options should...
|
by: Charles Arthur |
last post by:
How do i turn on java script on a villaon, callus and itel keypad mobile phone
|
by: emmanuelkatto |
last post by:
Hi All, I am Emmanuel katto from Uganda. I want to ask what challenges you've faced while migrating a website to cloud.
Please let me know.
Thanks!
Emmanuel
|
by: BarryA |
last post by:
What are the essential steps and strategies outlined in the Data Structures and Algorithms (DSA) roadmap for aspiring data scientists? How can individuals effectively utilize this roadmap to progress...
|
by: nemocccc |
last post by:
hello, everyone, I want to develop a software for my android phone for daily needs, any suggestions?
|
by: Hystou |
last post by:
There are some requirements for setting up RAID:
1. The motherboard and BIOS support RAID configuration.
2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...
|
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can...
|
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven...
|
by: Hystou |
last post by:
Overview:
Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome a new...
| |