unbound list box

[dmcp]

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.

[code=vb]Dim V As Variant
Dim GRPS As String

If Me.grp.ItemsSel ected.Count > 0 Then
For Each V In Me.grp.ItemsSel ected
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_descriptio n,internal_orde rs.ior_date, "
SQLSTR = SQLSTR & " internal_orders .io_grp FROM internal_orders "
SQLSTR = SQLSTR & " WHERE internal_orders .io_grp " & GRPS

Me.io.RowSource = SQLSTR[/code]

[Stewart Ross]

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.

[code=vb]SQLSTR = SQLSTR & " WHERE internal_orders .io_grp IN " & GRPS[/code]

-Stewart

[missinglinq]

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)>

[dmcp]

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.

[code=vb]SQLSTR = SQLSTR & " WHERE internal_orders .io_grp IN " & GRPS[/code]

-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?

[Stewart Ross]

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

[dmcp]

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_descriptio n,internal_orde rs.ior_date, internal_orders .io_grp FROM internal_orders WHERE internal_orders .io_grp IN(4040,4050)

[Stewart Ross]

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

[dmcp]

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_descriptio n,internal_orde rs.ior_date, internal_orders .io_grp FROM internal_orders WHERE internal_orders .io_grp IN (4040,4050)
it also didn't work

[Stewart Ross]

stewart here is the last test in the immediate window
?SQLSTR
SELECT internal_orders .ior_no, internal_orders .ior_descriptio n,internal_orde rs.ior_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

[dmcp]

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 .

[FishVal]

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
  

  [i]Field; Type; IndexInfo[/i]
  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.

[dmcp]

thanks for your useful hints
the rowsource type was empty. it worked.

[FishVal]

You are quite welcome.

Best regards,
Fish