SQL and Columns

[DS]

I have 2 listboxes, When you click on the first one it populates the
second one. The problem I have is when that the second one is based on
the criteria from the first. Me.MenuID = Me.ListBox.column(0) This
doesn't seem to work, can you not refer to a column in SQL.
Thanks
DS

 

[Marshall Barton]

I have 2 listboxes, When you click on the first one it populates the
second one. The problem I have is when that the second one is based on
the criteria from the first. Me.MenuID = Me.ListBox.column(0) This
doesn't seem to work, can you not refer to a column in SQL.

No, you can't. I keep forgetting that myself and only
remember it when I get a message about an unknown function.

Note, you can not use Me in a query either. Me is only
valid inside a class module. Use the full form reference
syntax Forms!formname.controlname

A common way to do what you want is to add a hidden text box
with the expression =ListBox.column(0) and use this textbox
instead .

 

[DS]

DS wrote:





No, you can't. I keep forgetting that myself and only
remember it when I get a message about an unknown function.

Note, you can not use Me in a query either. Me is only
valid inside a class module. Use the full form reference
syntax Forms!formname.controlname

A common way to do what you want is to add a hidden text box
with the expression =ListBox.column(0) and use this textbox
instead .

I was afraid of that. For some reason I thought you could refer to the
listbox and it automatically refers to the first column value only.
maybe thats what I remembered.
Thanks
Marshall
DS

 

[Marshall Barton]

I was afraid of that. For some reason I thought you could refer to the
listbox and it automatically refers to the first column value only.
maybe thats what I remembered.

It's the BoundColumn that you get when you refer to a single
select list box's value property.

Maybe if you explained more about what you were trying to do
and added some details about the list box and the query, I
could recommend something?

 

[Dirk Goldgar]

I was afraid of that. For some reason I thought you could refer to the
listbox and it automatically refers to the first column value only.
maybe thats what I remembered.

Marshall's suggestion of using a hidden text box to pick up the
listbox's column, and referring to that text box in your query, should
work. An alternative would be to rewrite the second list box's
rowsource SQL whenever the value of the first list box changes.

 

[DS]

DS wrote:





It's the BoundColumn that you get when you refer to a single
select list box's value property.

Maybe if you explained more about what you were trying to do
and added some details about the list box and the query, I
could recommend something?

Thanks Marshall, The hidden textboxes work, or I guess I could use dim
as well.
DS

 

[Marshall Barton]

Thanks Marshall, The hidden textboxes work, or I guess I could use dim
as well.

No, Dim is used to declare VBA variables, which are not
available outside VBA modules. The only VBA things that can
be used outside a VBA module are Public Functions in
standard modules.

FWIW, I almost always use VBA code to construct the combo
box's RowSource SQL statement as Dirk mentioned, but that's
only because I generally try to avoid query parameters as a
matter of habit.

 

[DS]

DS wrote:





No, Dim is used to declare VBA variables, which are not
available outside VBA modules. The only VBA things that can
be used outside a VBA module are Public Functions in
standard modules.

FWIW, I almost always use VBA code to construct the combo
box's RowSource SQL statement as Dirk mentioned, but that's
only because I generally try to avoid query parameters as a
matter of habit.

Thanks Marshall, I'll stay with the hidden TextBoxes FWIW it sounds safer!
DS

 

[Marshall Barton]

Thanks Marshall, I'll stay with the hidden TextBoxes FWIW it sounds safer!

I doubt that it is any safer, but, since there's no VBA code
involved, it's more straightforward.