473,320 Members | 1,863 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.

unbound list box

12
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.

Expand|Select|Wrap|Line Numbers
  1. Dim V As Variant
  2. Dim GRPS As String
  3.  
  4. If Me.grp.ItemsSelected.Count > 0 Then
  5. For Each V In Me.grp.ItemsSelected
  6. GRPS = GRPS & Me.grp.ItemData(V) & ","
  7. Next V
  8. GRPS = Left$(GRPS, Len(GRPS) - 1)
  9. End If
  10.  
  11. If Len(GRPS) = 0 Then
  12. GRPS = "like '*'"
  13. Else
  14. GRPS = "IN(" & GRPS & ")"
  15. End If
  16.  
  17. SQLSTR = "SELECT internal_orders.ior_no, internal_orders.ior_description,internal_orders.ior_date, "
  18. SQLSTR = SQLSTR & " internal_orders.io_grp FROM internal_orders "
  19. SQLSTR = SQLSTR & " WHERE internal_orders.io_grp " & GRPS
  20.  
  21. Me.io.RowSource = SQLSTR
Apr 12 '08 #1
12 2847
Stewart Ross
2,545 Expert Mod 2GB
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.

Expand|Select|Wrap|Line Numbers
  1. SQLSTR = SQLSTR & " WHERE internal_orders.io_grp IN " & GRPS
-Stewart
Apr 12 '08 #2
missinglinq
3,532 Expert 2GB
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)>
Apr 12 '08 #3
dmcp
12
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.

Expand|Select|Wrap|Line Numbers
  1. 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?
Apr 14 '08 #4
Stewart Ross
2,545 Expert Mod 2GB
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
Expand|Select|Wrap|Line Numbers
  1. WHERE iogrp {list of values}
which makes no sense in stand-alone SQL without an operator in between
Expand|Select|Wrap|Line Numbers
  1. WHERE iogrp = {a value}
  2. WHERE iogrp LIKE {a value}
  3. WHERE iogrp IN {list of values}
  4. WHERE iogrp > {a value}
  5. 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
Apr 14 '08 #5
dmcp
12
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
Expand|Select|Wrap|Line Numbers
  1. WHERE iogrp {list of values}
which makes no sense in stand-alone SQL without an operator in between
Expand|Select|Wrap|Line Numbers
  1. WHERE iogrp = {a value}
  2. WHERE iogrp LIKE {a value}
  3. WHERE iogrp IN {list of values}
  4. WHERE iogrp > {a value}
  5. 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)
Apr 14 '08 #6
Stewart Ross
2,545 Expert Mod 2GB
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
Apr 14 '08 #7
dmcp
12
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
Apr 15 '08 #8
Stewart Ross
2,545 Expert Mod 2GB
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
Apr 15 '08 #9
dmcp
12
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 .
Apr 15 '08 #10
FishVal
2,653 Expert 2GB
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
    Expand|Select|Wrap|Line Numbers
    1. Field; Type; IndexInfo
    2. StudentID; AutoNumber; PK
    3. Family; String; FK
    4. Name; String
    5. University; String; FK
    6. Mark; Numeric
    7. 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.
Apr 15 '08 #11
dmcp
12
thanks for your useful hints
the rowsource type was empty. it worked.
Apr 18 '08 #12
FishVal
2,653 Expert 2GB
You are quite welcome.

Best regards,
Fish
Apr 18 '08 #13

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

Similar topics

2
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...
2
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. ...
3
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...
4
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...
18
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...
8
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....
1
gcoaster
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...
6
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,...
6
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...
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
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...
1
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...
0
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...
0
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...
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: 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.