473,320 Members | 1,876 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

Query from Listbox value using Access 2002 and VB (Visual Studio 2005)

Hi. First post here, so I apologize ahead of time if it isn't the best-formatted post. I am working on a form that has a listbox (named LstCustID) that is bound to an access table (named CustomerInfo). I also have a datagrid (named DgvWOList) in which I want to display the results of my query into the table it is bound to (named WorkOrder).

My problem is this: I want to be able to select a customer from the listbox and have a query run that pulls the value of the CustID (primary) column of the table bound to the listbox, but still show the customer name column. The value is an integer, and at this point i have it set to be a public variable named CustC. So...I tried the following code in my query, but to no avail...and I am lost at this point on how to set it.

SQL:

SELECT [Date Work Completed], [Customer ID], [Services Offered], [Work Order ID]
FROM WorkOrder
WHERE ([Customer ID] = Forms ! FrmWOList ! CustC)
ORDER BY [Date Work Completed]

It breaks in the WHERE portion, and I think it is due to CustC not being a control, but rather a variable. I'm just not sure how to make the query work from a variable. I am thinking I will need to pull the CustID from the table bound to the listbox in my query rather than a variable, but I am also unsure as to how to go about doing that. Any help would be greatly appreciated!
Sep 13 '07 #1
18 3525
Afterudate set a text box on your form = your variable and then point your query to that instead little confuzzled as to what your doing let me know if that helps oh and of course if you get it working just set that ugly TextBox to visible No should work as long as your seting the variable afterupdate of the list box but a quick question are you just trying to limit the query to the record/records where the ID = the ID selected in the listbox because inthat case forget the variable and just set your query to look at the listbox or Null
Sep 13 '07 #2
I am trying to get the query to only do what is selected in the listbox. I am unsure how to get it to do that, though. If I can skip the variable, so much the better.

Please note that the listbox displays the "CustomerName" column of the table it is bound to, but I want the query to run off of the "CustomerID" column. I this possible?
Sep 13 '07 #3
Okay..so I set the value parameters of my listbox control to work on the column I wanted it to. My variable pulls the value properly (tested with a msg box). So..if I set the text box to have the text of that value after update, I can set the query to use the text box's text, right?

If so, how would I adjust the SQL? I'm still getting a parsing error.
Sep 13 '07 #4
Ok, so I have it set that after each change in the listbox the value is carried over to my insivisble text box, and I have the SQL query looking like this:

SELECT [Date Work Completed], [Customer ID], [Services Offered], [Work Order ID]
FROM WorkOrder
WHERE ([Customer ID] = (Form ! frmWOList ! txtCustID)
ORDER BY [Date Work Completed]

The error I get is:
Error in WHERE clause near '!'.
Error in WHERE clause near 'ORDER'.
Unable to parse query text.

I have also tried using txtCustID.Text instead of txtCustID.
Sep 13 '07 #5
According to my "try...catch" The ! is not recognized...
Sep 13 '07 #6
Okay..so I might be doing something else wrong, maybe? I am using the query designer to create the query, and using the code:

Me.WorkOrderTableAdapter.FillByID(Me.WOProject_FDa taSet.WorkOrder)

to do the actual filling of the information.

Now, when I change the ! to ., I no longer get a query parsing error, but my try...catch tells me one or more parameters no longer has any value.

So I'm still stuck.
Sep 13 '07 #7
Go into your query in design view and under Customer ID you will see where you can enter your criteria put = [Form] ! [frmWOList] ! [the name of your listbox] this will bypass the variable
Basicly you want it to be [name of your form]![name of the subform if there is one]![name of the object you are referring to] this way access will fix the syntax for you as for your SQL code it is more than likely looking for this ([Customer ID] = ([Form]![frmWOList]![txtCustID]) however I would just do it in the design view because it is easier. Sorry for the wait I just got swamped on of our developers is out at the moment if you need any more help I’ll be in the office till 5:00 pm
Sep 13 '07 #8
Thanks for the information. I'll try changing it when I get home tonight (at work now myself). It seems just odd that VB refers to the exclamation point as a syntax error. Ah, well. This might fix it. If it does, I'll post the code that ended up working so future questioners might benefit.
Sep 13 '07 #9
Well..using that code no longer causes a parsing error, but it now returns no values in the Query.
Sep 14 '07 #10
Ok..so the way I have the code set up now is for a variable to pull the .SelectedValue from the listbox (which is set to the Customer ID field in the listbox's binding). It is then moved to a textbox control. Then, the query runs using the textbox control as the WHERE parameter. Here is the code I use to pull it and run the query, then the query code itself. Maybe I'm just missing something simple?

Variable & Query VB Code:
Sub TestQuery()
CustC = lstCustId.SelectedValue 'Uses a variable to pull the value
txtCustID.Text = CustC 'Causes the textbox to update with the value
Try 'Error handling
Me.WorkOrderTableAdapter.FillByID(Me.WOProject_FDa taSet.WorkOrder)
Catch ex As Exception 'Catches errors
MsgBox(ex.Message) 'displays a message box with said error in it.
End Try 'end error handling
End Sub

SQL (As created by the wizard when I did as requested):
SELECT [Date Work Completed], [Customer ID], [Services Offered], [Work Order ID]
FROM WorkOrder
WHERE ([Customer ID] = '[Form] ! [frmWOList] ! [txtCustID]')
ORDER BY [Date Work Completed]
Sep 14 '07 #11
Well, I know why it isn't returning a value. The SQL Code listed above is actually just looking for the text inside the single quotes, rather than pulling anything from the form. So now I'm back to square 1 on this. Any more ideas?
Sep 14 '07 #12
As a side note, if I remove those single quotes the SQL still seems not to recognize the exclamation point usage as a valid syntax. Is there something I need to include or some definitions I need to have in order for it to recognize that code? I know in VB6 I sometimes needed to use include statements to allow certain predefined commands to work. I'm now wondering if there is something similar for SQL?
Sep 14 '07 #13
Are you using Access or visual studio?
Sep 14 '07 #14
I've actually tried both. I get the same error either way.
"The expression you entered has an invalid .(dot) or ! operator or invalid parentheses." "You may have entered an invalid identifier or typed parentheses following the Null constant."
Sep 14 '07 #15
Please note that my forms were built using VS. (It's why I named the topic the way I did)
Sep 14 '07 #16
Not to sure about VS but if your querys are in Access you can just put the code in the critiria of the query by going to design view and placeing =Forms!FormName!NameofObject in the critira for the field you are trying to restrict by

Is there a disign View for your Query or is it in Access?
Sep 14 '07 #17
There is a design view in VS similar to that in Access, but VS always puts spaces in between the ! and whatever is typed around it. I ended up getting frustrated with using SQL I knew should work and decided to try another method: self-created run-time queries. However, since that is a function of VS and not of Access, I should probably move any questions I have on it to a different forum.

As a side note, if I create everything using only Access, the query works as intended. My conclusion is thus: VS for some reason does not support that particular type of query without using an include that I am not familiar with.

I have found a way around the query portion using only VB code that calls a string that is basically the same query. Ah, well. On to the other forum if I can't find the solution to getting my datagrid to fill properly (something I am sure does not belong in the Access forum).

Thank you for all your help. You were correct in your assessment of the code and how (and where) it was being placed. Without that, I would not have even been able to troubleshoot my program fully and would -still- be locked in on the query and not the other issues.
Sep 15 '07 #18
Sorry I could not help out more wish you luck and hopfully i'll be working in vs soon i have it just no time to learn lol but soon.
Sep 17 '07 #19

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

Similar topics

3
by: JC Mugs | last post by:
Help needed for project-Access 2002(office xp) PROBLEM: Figuring out how to lookup a record and DDate field in Table1 - Take that DDate-field data from record looked up and assign it to Date...
2
by: Justin Koivisto | last post by:
Firstly, I want to thank all that have helped me out with getting grips on Access each time I've had questions. This has got to be one of the most helpful groups that I've posted to over the years....
21
by: Bilal Abbasi | last post by:
I realize that you can add items to a list box as objects so you can have access to more than just one property like the itemindex in vb6. Question I have is how do I cause the listbox to show a...
1
by: The Confessor | last post by:
I currently have a listbox in my program which I populate with data from a random access file as follows: For T = 1 To HighestPointID FileGet(1, Point(T), T) ListBox_Point.Items.Add(T & " Lat:...
4
by: Peter Gibbs | last post by:
I need some help with this problem. I'm using Access 2002 with XP. My problem is with a 2-column listbox. My VBA code puts text data into the listbox. The problem is that the text data...
1
by: lance2001 | last post by:
Hi, After viewing the entire Visual Basic 2005 Express Edition for Beginners video series, I have begun building a database driven application that will make use of listbox1 (multi-select)...
53
by: Hexman | last post by:
Hello All, I'd like your comments on the code below. The sub does exactly what I want it to do but I don't feel that it is solid as all. It seems like I'm using some VB6 code, .Net2003 code,...
1
by: johnlim20088 | last post by:
Hi, Currently I have 6 web projects located in Visual Source Safe 6.0, as usual, everytime I will open solution file located in my local computer, connected to source safe, then check out/check in...
7
by: =?Utf-8?B?UHJhamFrdGE=?= | last post by:
We have an application build on Microsoft Visual Studio 2002 .Net 1.0 ie on 32 bit. Can we port the same application for .NET 2.0. Does .NET 2.0 is supported on Microsoft Visual studio 2002 ie for...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
0
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
0
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
0
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.