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

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

P: 14
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
Share this Question
Share on Google+
18 Replies


MGrowneyARSI
P: 90
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

P: 14
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

P: 14
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

P: 14
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

P: 14
According to my "try...catch" The ! is not recognized...
Sep 13 '07 #6

P: 14
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

MGrowneyARSI
P: 90
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

P: 14
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

P: 14
Well..using that code no longer causes a parsing error, but it now returns no values in the Query.
Sep 14 '07 #10

P: 14
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

P: 14
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

P: 14
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

MGrowneyARSI
P: 90
Are you using Access or visual studio?
Sep 14 '07 #14

P: 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

P: 14
Please note that my forms were built using VS. (It's why I named the topic the way I did)
Sep 14 '07 #16

MGrowneyARSI
P: 90
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

P: 14
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

MGrowneyARSI
P: 90
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

Post your reply

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