i have an unbound form with 2 multiselect list boxes. grp & io.
the IO listbox is unbound and depends on the grp listbox but after finishing from the grp listbox i'm getting a null IO listbox.
the below code is tested and ok. - Dim V As Variant
-
Dim GRPS As String
-
-
If Me.grp.ItemsSelected.Count > 0 Then
-
For Each V In Me.grp.ItemsSelected
-
GRPS = GRPS & Me.grp.ItemData(V) & ","
-
Next V
-
GRPS = Left$(GRPS, Len(GRPS) - 1)
-
End If
-
-
If Len(GRPS) = 0 Then
-
GRPS = "like '*'"
-
Else
-
GRPS = "IN(" & GRPS & ")"
-
End If
-
-
SQLSTR = "SELECT internal_orders.ior_no, internal_orders.ior_description,internal_orders.ior_date, "
-
SQLSTR = SQLSTR & " internal_orders.io_grp FROM internal_orders "
-
SQLSTR = SQLSTR & " WHERE internal_orders.io_grp " & GRPS
-
-
Me.io.RowSource = SQLSTR
12 2847
Hi. You appear to be missing a comparison operator in your WHERE clause. I do not know whether it should be '=' or whatever, but it might be IN (as you appear to have looped through the listbox to build variable GRPS). I have listed IN below, but you will need to substitute what it really should be. The current WHERE clause will not return any rows without a suitable operator in place. - SQLSTR = SQLSTR & " WHERE internal_orders.io_grp IN " & GRPS
-Stewart
As Stewart's edit note says, please use code tags when posting code. You also need to post the entire sub or at least tell us what event you have the code in. Stewart and I assume that you've placed it in the AfterUpdate event of the grp combobox, where it belongs, but people frequently place it alsewhere, which can cause problems.
Welcome to bytes!
Linq ;0)>
Hi. You appear to be missing a comparison operator in your WHERE clause. I do not know whether it should be '=' or whatever, but it might be IN (as you appear to have looped through the listbox to build variable GRPS). I have listed IN below, but you will need to substitute what it really should be. The current WHERE clause will not return any rows without a suitable operator in place. - SQLSTR = SQLSTR & " WHERE internal_orders.io_grp IN " & GRPS
-Stewart
hi stewart
i'm not quite sure i understood your remarks but what i did is tested the sqlstr in the immediate window and pasted it in the sql query and it gave correct data.
if there is something wronge with the code would i get correct data? could my problem be in the IO listbox?
hi stewart
i'm not quite sure i understood your remarks but what i did is tested the sqlstr in the immediate window and pasted it in the sql query and it gave correct data.
if there is something wronge with the code would i get correct data? could my problem be in the IO listbox?
Hi. The WHERE clause has to say what kind of comparison you are doing, and it is not saying that at present. I don't think the listbox itself is what is the problem.
At present, the code in your WHERE clause is along the lines of - WHERE iogrp {list of values}
which makes no sense in stand-alone SQL without an operator in between - WHERE iogrp = {a value}
-
WHERE iogrp LIKE {a value}
-
WHERE iogrp IN {list of values}
-
WHERE iogrp > {a value}
-
WHERE iogrp <> {a value}
and so on.
I can't comment on what happens with your immediate window/query editor version, but I simply cannot see how the WHERE clause as currently written will work. As the SQL query is the rowsource for your IO listbox it follows that the listbox is null because no rows have been selected by its rowsource query. The most likely reason for that is that the WHERE clause is incorrect, as the rest of the query is just the SELECT component.
You should also check in the immediate window what the value of the GRPS variable is when the query is run, by setting a break point, stepping through your code and using debug.print on that variable. It would be of interest to see what the final value of your SQL string is and post that back in your next reply - it would be straightforward to check whether the string as finally created is valid SQL or not.
-Stewart
Hi. The WHERE clause has to say what kind of comparison you are doing, and it is not saying that at present. I don't think the listbox itself is what is the problem.
At present, the code in your WHERE clause is along the lines of - WHERE iogrp {list of values}
which makes no sense in stand-alone SQL without an operator in between - WHERE iogrp = {a value}
-
WHERE iogrp LIKE {a value}
-
WHERE iogrp IN {list of values}
-
WHERE iogrp > {a value}
-
WHERE iogrp <> {a value}
and so on.
I can't comment on what happens with your immediate window/query editor version, but I simply cannot see how the WHERE clause as currently written will work. As the SQL query is the rowsource for your IO listbox it follows that the listbox is null because no rows have been selected by its rowsource query. The most likely reason for that is that the WHERE clause is incorrect, as the rest of the query is just the SELECT component.
You should also check in the immediate window what the value of the GRPS variable is when the query is run, by setting a break point, stepping through your code and using debug.print on that variable. It would be of interest to see what the final value of your SQL string is and post that back in your next reply - it would be straightforward to check whether the string as finally created is valid SQL or not.
-Stewart
stewart
here is the immediate window test
?SQLSTR
SELECT internal_orders.ior_no, internal_orders.ior_description,internal_orders.io r_date, internal_orders.io_grp FROM internal_orders WHERE internal_orders.io_grp IN(4040,4050)
Many thanks - this helps pinpoint an error. There is a space missing after the IN operator (see post 2 and note that there is a space shown after IN, immediately before the closing double quote). You will need to include the space to separate the list from the IN operator.
-Stewart
Many thanks - this helps pinpoint an error. There is a space missing after the IN operator (see post 2 and note that there is a space shown after IN, immediately before the closing double quote). You will need to include the space to separate the list from the IN operator.
-Stewart
stewart here is the last test in the immediate window
?SQLSTR
SELECT internal_orders.ior_no, internal_orders.ior_description,internal_orders.io r_date, internal_orders.io_grp FROM internal_orders WHERE internal_orders.io_grp IN (4040,4050)
it also didn't work
stewart here is the last test in the immediate window
?SQLSTR
SELECT internal_orders.ior_no, internal_orders.ior_description,internal_orders.io r_date, internal_orders.io_grp FROM internal_orders WHERE internal_orders.io_grp IN (4040,4050)
it also didn't work
OK. What is the current value for internal_orders.io_grp (the item being compared to the list in the WHERE clause)? Is there a specific reason you are comparing it to a list of values, and not a single value?
-Stewart
OK. What is the current value for internal_orders.io_grp (the item being compared to the list in the WHERE clause)? Is there a specific reason you are comparing it to a list of values, and not a single value?
-Stewart
the current value is correct in the table groups 4040& 4050.
there are 2 tables groups & IO . 1 to many relation from grp to IO.
the purpose of filtering the IOs is that i want to get only the GRPs in the IO to get a filtered list of IO. sometimes i need more than one io_grp to get the list of IO .
Hi, all. - I don't see any problem with code as well as with SQL expression
- Though SQL expression was proved to run in query builder it would be useful anyway to know [internal_orders] metadata. Here is an example of how to post table MetaData :
Table Name=tblStudent - Field; Type; IndexInfo
-
StudentID; AutoNumber; PK
-
Family; String; FK
-
Name; String
-
University; String; FK
-
Mark; Numeric
-
LastAttendance; Date/Time
- Check the ListBox properties. At least the following:
- RowSourceType
- ColumnCount
- ColumnWidths
- Does the ListBox behave normally with RowSource set manually in design view?
- Does Requery method help?
- If you still have the problem, then I would recommend you to post here a sanitized version of your db.
Regards,
Fish.
thanks for your useful hints
the rowsource type was empty. it worked.
You are quite welcome.
Best regards,
Fish
Sign in to post your reply or Sign up for a free account.
Similar topics
by: bj |
last post by:
I run into this problem regularly and can't seem to figure out how to
do it. Suppose I have an unbound combobox (the list could be
pre-typed or populated in some other way). I want to place that...
|
by: Earthling |
last post by:
Dear Access Programmers,
I can't seem to figure this out.
The text boxes that are used to input data via my form are
unbound, I want to restrict the user to only put input that
is one line.
...
|
by: Trevor Hughes |
last post by:
I am trying to resolve a problem I'm experiencing in Access 2000.
I have an unbound control which is set be code on the open event of a
form. However when I try to subsequently run some code...
|
by: Robert |
last post by:
Have main form with an unbound subform1 which is used for data entry.
Subform1 has a nested continuous unbound subform (subform2) which is used to
enter multiple records related to the record being...
|
by: TORQUE |
last post by:
Hi,
Im wondering if anyone can help me with a problem.
I have a form with more than 50 unbound fields.
Some of the fields will be blank from time to time.
This seems to be where im having...
|
by: Robert |
last post by:
I have a form (Worksheet) that works fine by itself. I have now created a
seperate form (MainForm) that has a command button in the header and an
unbound subform (FormFrame) in the Detail section....
|
by: gcoaster |
last post by:
Hello Group,
I was wondering if anyone out there can help me
I have a form
Unbound List Box Control on the form
The row source is a built-in query.
I was wondering if there is anyway to...
|
by: Volker Neurath |
last post by:
Hi all,
I have a Problem with combobox-property "NotInList" and an unbound Form.
The situation:
On my main form i have three comboboxes for data-exchange (here: Names of
distributor,...
|
by: AdamOnAccess |
last post by:
Hi,
I need to store the last value of an unbound list box. For example, if the list box is currently set to X, when a user clicks Y, I need to save X in a variable.
I've tried to store the old...
|
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...
|
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...
|
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...
|
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...
|
by: jfyes |
last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
|
by: ArrayDB |
last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
|
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)...
|
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...
|
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
| |