ListBox Rowsource

[DS]

I have a listbox question. If I fill a listbox with a SQL Select. Do I
have to include the Where fields actually in the listbox rowsource or can I
just list them as such?

With Me.List0
SELECT tblCheckDetails.CDItemID, tblItems.ItemName
FROM tblItems INNER JOIN tblCheckDetails ON tblItems.ItemID =
tblCheckDetails.CDItemID
WHERE (((tblCheckDetails.CDCheckID)=[Forms]![Form2]![Text2]));
..ColumnCount=2
'ColumnWidths = "0 in;1 in"
End With

Thanks
DS

 

[Marshall Barton]

I have a listbox question. If I fill a listbox with a SQL Select. Do I
have to include the Where fields actually in the listbox rowsource or can I
just list them as such?

With Me.List0
SELECT tblCheckDetails.CDItemID, tblItems.ItemName
FROM tblItems INNER JOIN tblCheckDetails ON tblItems.ItemID =
tblCheckDetails.CDItemID
WHERE (((tblCheckDetails.CDCheckID)=[Forms]![Form2]![Text2]));
.ColumnCount=2
'ColumnWidths = "0 in;1 in"
End With

You do not have to put a field in the Select clause just so
you can use it in the Where clause.

The code you posted is invalid. If that's cecause you
abbreviated it when posting it, don't do that. If you have
a question about code, whether it works or not, use
Copy/Paste so we can see **exactly** what you are asking
about. If your real code does work, we could have avoided
this long winded admonishment ;-)

If you your code doesn't work and you really did post the
actual code, then use something more like:

With Me.List0
.RowSource = "SELECT tblCheckDetails.CDItemID, " & _
"tblItems.ItemName " & _
"FROM tblItems INNER JOIN tblCheckDetails " & _
"ON tblItems.ItemID = tblCheckDetails.CDItemID " & _
"WHERE tblCheckDetails.CDCheckID=Forms!Form2!Text2"
.ColumnCount=2
'ColumnWidths = "0 in;1 in"
End With

I don't understand why you are setting the other properties
in the code. Can't you just set then in the form's design
view and be done with it?

Note that if you really do need to set the ColumnWidths
property in your code, you must use twips, not inches:
'ColumnWidths = "0;1440"

 

[DS]

Thanks Marshall, The code was actully copied from the queries SQL statement,
sorry I didn't repunctuate. Yes it does work, I had fears though that if it
wasn't in the select clause that one day it would blow up on me. The reason
I ask all of this is that one of my listbox now has 18 columns, most of
which are hide, and most of which are in the WHERE clause. So I was trying
to cut down on the columns that had to be dealt with, so now it looks like I
can. So I'm assuming an inch is 1440 twips and .5 inch is 720 twips! I've
been using inches all along but I guess I better go back and turn to twips!
Thank you for your advice, it is appreciated.
DS

I have a listbox question. If I fill a listbox with a SQL Select. Do I
have to include the Where fields actually in the listbox rowsource or can
I
just list them as such?

With Me.List0
SELECT tblCheckDetails.CDItemID, tblItems.ItemName
FROM tblItems INNER JOIN tblCheckDetails ON tblItems.ItemID =
tblCheckDetails.CDItemID
WHERE (((tblCheckDetails.CDCheckID)=[Forms]![Form2]![Text2]));
.ColumnCount=2
'ColumnWidths = "0 in;1 in"
End With

You do not have to put a field in the Select clause just so
you can use it in the Where clause.

The code you posted is invalid. If that's cecause you
abbreviated it when posting it, don't do that. If you have
a question about code, whether it works or not, use
Copy/Paste so we can see **exactly** what you are asking
about. If your real code does work, we could have avoided
this long winded admonishment ;-)

If you your code doesn't work and you really did post the
actual code, then use something more like:

With Me.List0
.RowSource = "SELECT tblCheckDetails.CDItemID, " & _
"tblItems.ItemName " & _
"FROM tblItems INNER JOIN tblCheckDetails " & _
"ON tblItems.ItemID = tblCheckDetails.CDItemID " & _
"WHERE tblCheckDetails.CDCheckID=Forms!Form2!Text2"
.ColumnCount=2
'ColumnWidths = "0 in;1 in"
End With

I don't understand why you are setting the other properties
in the code. Can't you just set then in the form's design
view and be done with it?

Note that if you really do need to set the ColumnWidths
property in your code, you must use twips, not inches:
'ColumnWidths = "0;1440"