Google Groups no longer supports new Usenet posts or subscriptions. Historical content remains viewable.
Dismiss
Access 2010: No Column Property In Queries???
162 views
Skip to first unread message
Neil
Apr 10, 2012, 11:02:48 PM4/10/12
to
When trying to reference a list box's 3rd column value using the Column
property in another control's row source criteria, that property is
unavailable for some reason! I've always used it before in forms and
queries. But now it seems to be gone. If I reference the list box's column
property in the format: Forms!MyForm!MyListBox.Column(2), I get an error:
"unknown function."
I can reference the list box's Column property from VBA, no problem. But if
I try to reference it in a query (either a standalone query or one that's
the rowsource of another control), I get an error. Also, the autocomplete
options that are there when you type don't show a column property. The only
ones they list are:
columncount, columnend, columnheads, columnhidden, columnorder, columnstart,
columnwidths.
Same in the builder.
Anyone know what's going on? Is this not available in 2010 (for some bizarre
reason), or is there something wrong with my setup?
Thanks!
Neil
property in another control's row source criteria, that property is
unavailable for some reason! I've always used it before in forms and
queries. But now it seems to be gone. If I reference the list box's column
property in the format: Forms!MyForm!MyListBox.Column(2), I get an error:
"unknown function."
I can reference the list box's Column property from VBA, no problem. But if
I try to reference it in a query (either a standalone query or one that's
the rowsource of another control), I get an error. Also, the autocomplete
options that are there when you type don't show a column property. The only
ones they list are:
columncount, columnend, columnheads, columnhidden, columnorder, columnstart,
columnwidths.
Same in the builder.
Anyone know what's going on? Is this not available in 2010 (for some bizarre
reason), or is there something wrong with my setup?
Thanks!
Neil
Patrick Finucane
Apr 11, 2012, 11:07:58 AM4/11/12
to
worked in the past is kind of moot since it doesn't work now. I'd
reference invisible fields instead the hold particular column
properties.else create a function that's called in the query.
Neil
Apr 12, 2012, 6:52:04 AM4/12/12
to
"Patrick Finucane" <patrickf...@gmail.com> wrote in message
news:d1d56734-6f05-48e6...@er9g2000vbb.googlegroups.com...
Yeah, I know for a fact it did, as I recall, whenever I would use the Column
property in a query, it would always put square brackets around it
automatically, so it would end up looking like this:
[Forms]![MyForm]![MyComboBox].[Column](2)
I always found that kinda quirky that Access would do that. But, hey, it
worked, and that's all that mattered. I used to use this all the time.
So now, yeah, I have to have a function, pass it a reference to the control
and the column number, and have it return the value, just to get a property
that a used to be able to get directly. Seems a major step backwards to me!
Oh well. In any case, thanks for responding. At least I know it's not
something corrupt in my installation of Access, since you can't do it
either. So, thanks for letting me know.
Neil
Albert D. Kallal
Apr 15, 2012, 6:16:07 PM4/15/12
to
"Neil" wrote in message news:jm6c5m$6ga$1...@dont-email.me...
>Oh well. In any case, thanks for responding. At least I know it's not
>something corrupt in my installation of Access, since you can't do it
>either. So, thanks for letting me know.
I don't recall being able to do this in a2003. You can place such
expressions on a form, but I don't recall this working in even in a2003.
What you can do however is simply drop in that other table, left join those
additional. columns in, and you thus be able to use the columns direct
without he column command.
--
Albert D. Kallal
Edmonton, Alberta Canada
PleaseNoS...@msn.com
>Oh well. In any case, thanks for responding. At least I know it's not
>something corrupt in my installation of Access, since you can't do it
>either. So, thanks for letting me know.
expressions on a form, but I don't recall this working in even in a2003.
What you can do however is simply drop in that other table, left join those
additional. columns in, and you thus be able to use the columns direct
without he column command.
--
Albert D. Kallal
Edmonton, Alberta Canada
PleaseNoS...@msn.com
Neil
Apr 16, 2012, 7:40:03 AM4/16/12
to
"Albert D. Kallal" <PleaseNOS...@msn.com> wrote in message
news:BMHir.2478$FQ1...@newsfe12.iad...
> What you can do however is simply drop in that other table, left join
> those additional. columns in, and you thus be able to use the columns
> direct without he column command.
No, that wouldn't work. What we're discussing here is using a combo box
> those additional. columns in, and you thus be able to use the columns
> direct without he column command.
column in the criteria row of a query. So what you propose wouldn't apply
there. The only way would be to use a function.
Neil
Apr 16, 2012, 8:20:11 AM4/16/12
to
"Neil" <neil.ginsbe...@gmail.com> wrote in message
news:jmh0gv$vq1$1...@dont-email.me...
box.
Albert D. Kallal
Apr 16, 2012, 3:49:44 PM4/16/12
to
"Neil" wrote in message news:jmh2sq$bm8$1...@dont-email.me...
>>
>> No, that wouldn't work. What we're discussing here is using a combo box
>> column in the criteria row of a query. So what you propose wouldn't apply
>> there. The only way would be to use a function.
>Correction - it's a list box (as stated in the original post), not a combo
box.
Actually, my suggestion will still work. What I am suggestion here is to
base the main form on a query (base form table), and then left join in the
additional columns you need from the table that drives the combo box. At
that point, when you selected a value in the combo box, the several other
columns you included from that table will now display (with out code) I
those additional text boxes that are bound to that combo box table.
The result is you can use
forms!myform!nameofthesevealAdditonalCollumnsYouPlacedOnTheFormGoesHere.
So my suggestion works for both combo box, listbox and in fact if you just
placed a bond text box and typed in the id value into this bound FK text
box, then all of the several other text boxes on the form will auto update
and display the information from those additional columns from the joined in
table.
As pointed out, it important to make this a left join so records that don't
have a value entered into the FK text box that holds the PK of the related
table selected will still show up.
In fact, I wondering if placing a text box on the form with the expression
=(cboBox.column(2)) would allow that text box to be used in the query
expression - if this works (not tested), then my suggestion to add the
additional columns by use of a left join query (and base the form on that
query) would not even be required.
>>
>> No, that wouldn't work. What we're discussing here is using a combo box
>> column in the criteria row of a query. So what you propose wouldn't apply
>> there. The only way would be to use a function.
>Correction - it's a list box (as stated in the original post), not a combo
box.
base the main form on a query (base form table), and then left join in the
additional columns you need from the table that drives the combo box. At
that point, when you selected a value in the combo box, the several other
columns you included from that table will now display (with out code) I
those additional text boxes that are bound to that combo box table.
The result is you can use
forms!myform!nameofthesevealAdditonalCollumnsYouPlacedOnTheFormGoesHere.
So my suggestion works for both combo box, listbox and in fact if you just
placed a bond text box and typed in the id value into this bound FK text
box, then all of the several other text boxes on the form will auto update
and display the information from those additional columns from the joined in
table.
As pointed out, it important to make this a left join so records that don't
have a value entered into the FK text box that holds the PK of the related
table selected will still show up.
In fact, I wondering if placing a text box on the form with the expression
=(cboBox.column(2)) would allow that text box to be used in the query
expression - if this works (not tested), then my suggestion to add the
additional columns by use of a left join query (and base the form on that
query) would not even be required.
Neil
Apr 16, 2012, 10:45:47 PM4/16/12
to
news:mJ_ir.9816$YM2....@newsfe05.iad...
box in my last message; I corrected that in a follow up). I thought it was
clear, since it was a list box, that it was unbound (at least for me, I
can't remember ever using a bound list box; list boxes are always used by me
just to make a selection). So I thought it was clear that we were dealing
with an unbound control that was used just for selection.
So, yes, I understand about using lookup tables in the main form query and
getting those values. I do that all the time. But this is an unbound list
box, so that wouldn't apply. Sorry if that wasn't clear.
> So my suggestion works for both combo box, listbox and in fact if you just
> placed a bond text box and typed in the id value into this bound FK text
> box, then all of the several other text boxes on the form will auto update
> and display the information from those additional columns from the joined
> in table.
Yes, it would work the same for a list box as for a combo box; they're both
the same, essentially. The point I made above, though, is that I've never
used a bound list box. Maybe that's just me. I just find that combo box
works much better with a bound field, and list box works well as an unbound
control for making a selection.
Anyway, regardless of whether or not people use bound list boxes, in this
case, anyway, it's unbound.
> In fact, I wondering if placing a text box on the form with the expression
> =(cboBox.column(2)) would allow that text box to be used in the query
> expression - if this works (not tested), then my suggestion to add the
> additional columns by use of a left join query (and base the form on that
> query) would not even be required.
Actually, that does work. So, thanks for the workaround. :-)
Neil
> "Neil" wrote in message news:jmh2sq$bm8$1...@dont-email.me...
>
>>>
>>> No, that wouldn't work. What we're discussing here is using a combo box
>>> column in the criteria row of a query. So what you propose wouldn't
>>> apply there. The only way would be to use a function.
>
>>Correction - it's a list box (as stated in the original post), not a combo
> box.
>
> Actually, my suggestion will still work. What I am suggestion here is to
> base the main form on a query (base form table), and then left join in the
> additional columns you need from the table that drives the combo box. At
> that point, when you selected a value in the combo box, the several other
> columns you included from that table will now display (with out code) I
> those additional text boxes that are bound to that combo box table.
Yes, I understand that. But this is an unbound listbox (sorry I said combo
>
>>>
>>> No, that wouldn't work. What we're discussing here is using a combo box
>>> column in the criteria row of a query. So what you propose wouldn't
>>> apply there. The only way would be to use a function.
>
>>Correction - it's a list box (as stated in the original post), not a combo
> box.
>
> Actually, my suggestion will still work. What I am suggestion here is to
> base the main form on a query (base form table), and then left join in the
> additional columns you need from the table that drives the combo box. At
> that point, when you selected a value in the combo box, the several other
> columns you included from that table will now display (with out code) I
> those additional text boxes that are bound to that combo box table.
box in my last message; I corrected that in a follow up). I thought it was
clear, since it was a list box, that it was unbound (at least for me, I
can't remember ever using a bound list box; list boxes are always used by me
just to make a selection). So I thought it was clear that we were dealing
with an unbound control that was used just for selection.
So, yes, I understand about using lookup tables in the main form query and
getting those values. I do that all the time. But this is an unbound list
box, so that wouldn't apply. Sorry if that wasn't clear.
> So my suggestion works for both combo box, listbox and in fact if you just
> placed a bond text box and typed in the id value into this bound FK text
> box, then all of the several other text boxes on the form will auto update
> and display the information from those additional columns from the joined
> in table.
the same, essentially. The point I made above, though, is that I've never
used a bound list box. Maybe that's just me. I just find that combo box
works much better with a bound field, and list box works well as an unbound
control for making a selection.
Anyway, regardless of whether or not people use bound list boxes, in this
case, anyway, it's unbound.
> In fact, I wondering if placing a text box on the form with the expression
> =(cboBox.column(2)) would allow that text box to be used in the query
> expression - if this works (not tested), then my suggestion to add the
> additional columns by use of a left join query (and base the form on that
> query) would not even be required.
Neil
Albert D. Kallal
Apr 17, 2012, 12:09:06 AM4/17/12
to
"Neil" wrote in message news:jmilir$b4r$1...@dont-email.me...
>> In fact, I wondering if placing a text box on the form with the
>> expression =(cboBox.column(2)) would allow that text box to be used in
>> the query expression
listbox) being un-bound means my left join idea will not work...
>> In fact, I wondering if placing a text box on the form with the
>> expression =(cboBox.column(2)) would allow that text box to be used in
>> the query expression
>Actually, that does work. So, thanks for the workaround. :-)
Ok, neat-o. And yes, you are correct - the detail of the combo box (or
listbox) being un-bound means my left join idea will not work...
0 new messages