Combo Box Row Source

[Bob Darlington]

I am using the following SQL as a RowSource for a combo box on a form.

"SELECT LAN, TenantName as [Tenant Name], ShopNum as [Shop] FROM
qTenantSelect "

The field 'ShopNum' is a text field and has a caption property set to 'Shop
Number' in the table design.
In the combo box though, I want to use 'Shop' as the heading (to save width)
as shown in the SQL above.
But the combo shows 'Shop Number' as a heading in form view.

I thought that the 'As Shop' would have done what I wanted, but looks like
I'm wrong .... yet again.

Can anyone help?

--
Bob Darlington
Brisbane

[Mark]

Re: Combo Box Row Source

Bob,
As far as I know, the heading is just a label and can be renamed as you
please. It displays 'Shop Number' because whatever you call it AS, the
source is still 'Shop Number'.
The data displayed as the combo box detail should be the raw data with no
headings at all. If you are not happy with the column widths, you can change
them in the combo box properties. The values in "Column widths" on the
"Format" tab are the values you need to change. See "Columnwidt h" property
in Access help for full details.

Rgds,

Mark

"Bob Darlington" <bob@dpcmanAX.c om.au> wrote in message
news:41330057$0 $10824$afc38c87 @news.optusnet. com.au...[color=blue]
> I am using the following SQL as a RowSource for a combo box on a form.
>
> "SELECT LAN, TenantName as [Tenant Name], ShopNum as [Shop] FROM
> qTenantSelect "
>
> The field 'ShopNum' is a text field and has a caption property set to[/color]
'Shop[color=blue]
> Number' in the table design.
> In the combo box though, I want to use 'Shop' as the heading (to save[/color]
width)[color=blue]
> as shown in the SQL above.
> But the combo shows 'Shop Number' as a heading in form view.
>
> I thought that the 'As Shop' would have done what I wanted, but looks[/color]
like[color=blue]
> I'm wrong .... yet again.
>
> Can anyone help?
>
> --
> Bob Darlington
> Brisbane
>
>[/color]

[Ed Sturges]

Re: Combo Box Row Source

Simply change the "Caption" property for the label to whatever you wish it
to be . . .


"Bob Darlington" <bob@dpcmanAX.c om.au> wrote in message
news:41330057$0 $10824$afc38c87 @news.optusnet. com.au...[color=blue]
> I am using the following SQL as a RowSource for a combo box on a form.
>
> "SELECT LAN, TenantName as [Tenant Name], ShopNum as [Shop] FROM
> qTenantSelect "
>
> The field 'ShopNum' is a text field and has a caption property set to[/color]
'Shop[color=blue]
> Number' in the table design.
> In the combo box though, I want to use 'Shop' as the heading (to save[/color]
width)[color=blue]
> as shown in the SQL above.
> But the combo shows 'Shop Number' as a heading in form view.
>
> I thought that the 'As Shop' would have done what I wanted, but looks[/color]
like[color=blue]
> I'm wrong .... yet again.
>
> Can anyone help?
>
> --
> Bob Darlington
> Brisbane
>
>[/color]

[Bob Darlington]

Re: Combo Box Row Source

It is not a label. The caption property is in the table design, and is used
throughtout the app as intended.
But for this particular combo, I want to change it to something shorter -
hence 'As'.

--
Bob Darlington
Brisbane
"Ed Sturges" <esturgesjr@sne t.net> wrote in message
news:dwGYc.8764 $FV3.6922@newss vr17.news.prodi gy.com...[color=blue]
> Simply change the "Caption" property for the label to whatever you wish it
> to be . . .
>
>
> "Bob Darlington" <bob@dpcmanAX.c om.au> wrote in message
> news:41330057$0 $10824$afc38c87 @news.optusnet. com.au...[color=green]
> > I am using the following SQL as a RowSource for a combo box on a form.
> >
> > "SELECT LAN, TenantName as [Tenant Name], ShopNum as [Shop] FROM
> > qTenantSelect "
> >
> > The field 'ShopNum' is a text field and has a caption property set to[/color]
> 'Shop[color=green]
> > Number' in the table design.
> > In the combo box though, I want to use 'Shop' as the heading (to save[/color]
> width)[color=green]
> > as shown in the SQL above.
> > But the combo shows 'Shop Number' as a heading in form view.
> >
> > I thought that the 'As Shop' would have done what I wanted, but looks[/color]
> like[color=green]
> > I'm wrong .... yet again.
> >
> > Can anyone help?
> >
> > --
> > Bob Darlington
> > Brisbane
> >
> >[/color]
>
>[/color]

[Bob Darlington]

Re: Combo Box Row Source

Mark,
Thanks for the reply.
The headings in a combo will pickup the caption property from the table
design, just as a query will.
What I want to know, is how to rename the heading to what I want for this
particular combo.
I am assuming that a different formatting is required as a result of using
the caption property in the table.
But what is the correct formatting? Or is it impossible?
As I said in my post, I want to save column width, hence my question. To
have a column width of 3cm to hold a 2 digit number looks ridiculous, hence
my post.
The caption used in table design is used throughout my app, and suits 95% of
the time, so I don't want to change it.

--
Bob Darlington
Brisbane
"Mark" <mark.reed75@nt lworld.com> wrote in message
news:ZdEYc.277$ 9d6.209@newsfe2-gui.ntli.net...[color=blue]
> Bob,
> As far as I know, the heading is just a label and can be renamed as[/color]
you[color=blue]
> please. It displays 'Shop Number' because whatever you call it AS, the
> source is still 'Shop Number'.
> The data displayed as the combo box detail should be the raw data with no
> headings at all. If you are not happy with the column widths, you can[/color]
change[color=blue]
> them in the combo box properties. The values in "Column widths" on the
> "Format" tab are the values you need to change. See "Columnwidt h" property
> in Access help for full details.
>
> Rgds,
>
> Mark
>
> "Bob Darlington" <bob@dpcmanAX.c om.au> wrote in message
> news:41330057$0 $10824$afc38c87 @news.optusnet. com.au...[color=green]
> > I am using the following SQL as a RowSource for a combo box on a form.
> >
> > "SELECT LAN, TenantName as [Tenant Name], ShopNum as [Shop] FROM
> > qTenantSelect "
> >
> > The field 'ShopNum' is a text field and has a caption property set to[/color]
> 'Shop[color=green]
> > Number' in the table design.
> > In the combo box though, I want to use 'Shop' as the heading (to save[/color]
> width)[color=green]
> > as shown in the SQL above.
> > But the combo shows 'Shop Number' as a heading in form view.
> >
> > I thought that the 'As Shop' would have done what I wanted, but looks[/color]
> like[color=green]
> > I'm wrong .... yet again.
> >
> > Can anyone help?
> >
> > --
> > Bob Darlington
> > Brisbane
> >
> >[/color]
>
>[/color]

[Yamafopa]

Re: Combo Box Row Source

If I understand this correctly, you have a table with a field called
ShopNum. That field has a caption = "Shop Number". You are including the
data from this field in a Combo Box. You would like to have the Combo box to
display a different caption than the one that is specified in the table
properties.
OK so far?

As I remember you can not override the table property value using a SQL
alias. If you need to display different Captions in certain circumstances,
you need to remove the 'Caption' property from the table.

I haven't tested this but, you might be able to override it by selecting the
values that will supply the combo box from a stored Query. That query would
have the field alias that you want to use.

HTH

yamafopa!



"Bob Darlington" <bob@dpcmanAX.c om.au> wrote in message
news:41330057$0 $10824$afc38c87 @news.optusnet. com.au...[color=blue]
> I am using the following SQL as a RowSource for a combo box on a form.
>
> "SELECT LAN, TenantName as [Tenant Name], ShopNum as [Shop] FROM
> qTenantSelect "
>
> The field 'ShopNum' is a text field and has a caption property set to[/color]
'Shop[color=blue]
> Number' in the table design.
> In the combo box though, I want to use 'Shop' as the heading (to save[/color]
width)[color=blue]
> as shown in the SQL above.
> But the combo shows 'Shop Number' as a heading in form view.
>
> I thought that the 'As Shop' would have done what I wanted, but looks[/color]
like[color=blue]
> I'm wrong .... yet again.
>
> Can anyone help?
>
> --
> Bob Darlington
> Brisbane
>
>[/color]

[Bob Darlington]

Re: Combo Box Row Source

Thanks for the reply.
You seem to be right. I can't do it without removing the Caption from the
table.
Tried your suggestion using a stored query, but gives the same result.

--
Bob Darlington
Brisbane
"Yamafopa" <yamafopa@yahoo .com> wrote in message
news:wwQYc.1181 04$UTP.47418@tw ister01.bloor.i s.net.cable.rog ers.com...[color=blue]
> If I understand this correctly, you have a table with a field called
> ShopNum. That field has a caption = "Shop Number". You are including the
> data from this field in a Combo Box. You would like to have the Combo box[/color]
to[color=blue]
> display a different caption than the one that is specified in the table
> properties.
> OK so far?
>
> As I remember you can not override the table property value using a SQL
> alias. If you need to display different Captions in certain circumstances,
> you need to remove the 'Caption' property from the table.
>
> I haven't tested this but, you might be able to override it by selecting[/color]
the[color=blue]
> values that will supply the combo box from a stored Query. That query[/color]
would[color=blue]
> have the field alias that you want to use.
>
> HTH
>
> yamafopa!
>
>
>
> "Bob Darlington" <bob@dpcmanAX.c om.au> wrote in message
> news:41330057$0 $10824$afc38c87 @news.optusnet. com.au...[color=green]
> > I am using the following SQL as a RowSource for a combo box on a form.
> >
> > "SELECT LAN, TenantName as [Tenant Name], ShopNum as [Shop] FROM
> > qTenantSelect "
> >
> > The field 'ShopNum' is a text field and has a caption property set to[/color]
> 'Shop[color=green]
> > Number' in the table design.
> > In the combo box though, I want to use 'Shop' as the heading (to save[/color]
> width)[color=green]
> > as shown in the SQL above.
> > But the combo shows 'Shop Number' as a heading in form view.
> >
> > I thought that the 'As Shop' would have done what I wanted, but looks[/color]
> like[color=green]
> > I'm wrong .... yet again.
> >
> > Can anyone help?
> >
> > --
> > Bob Darlington
> > Brisbane
> >
> >[/color]
>
>[/color]

[Mike Preston]

Re: Combo Box Row Source

On Mon, 30 Aug 2004 20:24:16 +1000, "Bob Darlington"
<bob@dpcmanAX.c om.au> wrote:
[color=blue]
>I am using the following SQL as a RowSource for a combo box on a form.
>
>"SELECT LAN, TenantName as [Tenant Name], ShopNum as [Shop] FROM
>qTenantSelec t "
>
>The field 'ShopNum' is a text field and has a caption property set to 'Shop
>Number' in the table design.
>In the combo box though, I want to use 'Shop' as the heading (to save width)
>as shown in the SQL above.
>But the combo shows 'Shop Number' as a heading in form view.
>
>I thought that the 'As Shop' would have done what I wanted, but looks like
>I'm wrong .... yet again.
>
>Can anyone help?[/color]

Horrible, obnoxious, please don't do this suggestion to follow:

Add another field to the query which has the same source as Shop
Number, but either leave its caption blank or set it to Shop. Then
use that one, rather than Shop Number for the source of your combo
box.

I told you it was a bad idea. ;-)

mike

[Bob Darlington]

Re: Combo Box Row Source

Mike,
In fact it was so horible that even I had tried it. And it doesn't work.
I think I'm stuck with the fact that once I've used the caption property in
the table, I can't then call it something else.
Thanks for replying though.

--
Bob Darlington
Brisbane
"Mike Preston" <mbpatpas.inval id@pacbell.net> wrote in message
news:41341221.1 38493102@news.I NDIVIDUAL.NET.. .[color=blue]
> On Mon, 30 Aug 2004 20:24:16 +1000, "Bob Darlington"
> <bob@dpcmanAX.c om.au> wrote:
>[color=green]
> >I am using the following SQL as a RowSource for a combo box on a form.
> >
> >"SELECT LAN, TenantName as [Tenant Name], ShopNum as [Shop] FROM
> >qTenantSelec t "
> >
> >The field 'ShopNum' is a text field and has a caption property set to[/color][/color]
'Shop[color=blue][color=green]
> >Number' in the table design.
> >In the combo box though, I want to use 'Shop' as the heading (to save[/color][/color]
width)[color=blue][color=green]
> >as shown in the SQL above.
> >But the combo shows 'Shop Number' as a heading in form view.
> >
> >I thought that the 'As Shop' would have done what I wanted, but looks[/color][/color]
like[color=blue][color=green]
> >I'm wrong .... yet again.
> >
> >Can anyone help?[/color]
>
> Horrible, obnoxious, please don't do this suggestion to follow:
>
> Add another field to the query which has the same source as Shop
> Number, but either leave its caption blank or set it to Shop. Then
> use that one, rather than Shop Number for the source of your combo
> box.
>
> I told you it was a bad idea. ;-)
>
> mike
>[/color]

[Mike Preston]

Re: Combo Box Row Source

On Tue, 31 Aug 2004 17:16:47 +1000, "Bob Darlington"
<bob@dpcmanAX.c om.au> wrote:
[color=blue]
>Mike,
>In fact it was so horible that even I had tried it. And it doesn't work.
>I think I'm stuck with the fact that once I've used the caption property in
>the table, I can't then call it something else.
>Thanks for replying though.[/color]

Let me rephrase. Add another field to the table, not the query. Then
use that field as the source of your field in the query.

I told you it was terrible. ;-)

mike
[color=blue]
>
>--
>Bob Darlington
>Brisbane
>"Mike Preston" <mbpatpas.inval id@pacbell.net> wrote in message
>news:41341221. 138493102@news. INDIVIDUAL.NET. ..[color=green]
>> On Mon, 30 Aug 2004 20:24:16 +1000, "Bob Darlington"
>> <bob@dpcmanAX.c om.au> wrote:
>>[color=darkred]
>> >I am using the following SQL as a RowSource for a combo box on a form.
>> >
>> >"SELECT LAN, TenantName as [Tenant Name], ShopNum as [Shop] FROM
>> >qTenantSelec t "
>> >
>> >The field 'ShopNum' is a text field and has a caption property set to[/color][/color]
>'Shop[color=green][color=darkred]
>> >Number' in the table design.
>> >In the combo box though, I want to use 'Shop' as the heading (to save[/color][/color]
>width)[color=green][color=darkred]
>> >as shown in the SQL above.
>> >But the combo shows 'Shop Number' as a heading in form view.
>> >
>> >I thought that the 'As Shop' would have done what I wanted, but looks[/color][/color]
>like[color=green][color=darkred]
>> >I'm wrong .... yet again.
>> >
>> >Can anyone help?[/color]
>>
>> Horrible, obnoxious, please don't do this suggestion to follow:
>>
>> Add another field to the query which has the same source as Shop
>> Number, but either leave its caption blank or set it to Shop. Then
>> use that one, rather than Shop Number for the source of your combo
>> box.
>>
>> I told you it was a bad idea. ;-)
>>
>> mike
>>[/color]
>
>[/color]

[Bob Darlington]

Re: Combo Box Row Source

Thanks Mike,
That makes more sense. I've bitten the bullet now and removed the caption
from the table and edited my queries which referred to it.
I suppose it wasn't such a big deal, but I hate these things getting the
better of me.

--
Bob Darlington
Brisbane
"Mike Preston" <mbpatpas.inval id@pacbell.net> wrote in message
news:4134cf72.1 86958271@news.I NDIVIDUAL.NET.. .[color=blue]
> On Tue, 31 Aug 2004 17:16:47 +1000, "Bob Darlington"
> <bob@dpcmanAX.c om.au> wrote:
>[color=green]
> >Mike,
> >In fact it was so horible that even I had tried it. And it doesn't work.
> >I think I'm stuck with the fact that once I've used the caption property[/color][/color]
in[color=blue][color=green]
> >the table, I can't then call it something else.
> >Thanks for replying though.[/color]
>
> Let me rephrase. Add another field to the table, not the query. Then
> use that field as the source of your field in the query.
>
> I told you it was terrible. ;-)
>
> mike
>[color=green]
> >
> >--
> >Bob Darlington
> >Brisbane
> >"Mike Preston" <mbpatpas.inval id@pacbell.net> wrote in message
> >news:41341221. 138493102@news. INDIVIDUAL.NET. ..[color=darkred]
> >> On Mon, 30 Aug 2004 20:24:16 +1000, "Bob Darlington"
> >> <bob@dpcmanAX.c om.au> wrote:
> >>
> >> >I am using the following SQL as a RowSource for a combo box on a form.
> >> >
> >> >"SELECT LAN, TenantName as [Tenant Name], ShopNum as [Shop] FROM
> >> >qTenantSelec t "
> >> >
> >> >The field 'ShopNum' is a text field and has a caption property set to[/color]
> >'Shop[color=darkred]
> >> >Number' in the table design.
> >> >In the combo box though, I want to use 'Shop' as the heading (to save[/color]
> >width)[color=darkred]
> >> >as shown in the SQL above.
> >> >But the combo shows 'Shop Number' as a heading in form view.
> >> >
> >> >I thought that the 'As Shop' would have done what I wanted, but looks[/color]
> >like[color=darkred]
> >> >I'm wrong .... yet again.
> >> >
> >> >Can anyone help?
> >>
> >> Horrible, obnoxious, please don't do this suggestion to follow:
> >>
> >> Add another field to the query which has the same source as Shop
> >> Number, but either leave its caption blank or set it to Shop. Then
> >> use that one, rather than Shop Number for the source of your combo
> >> box.
> >>
> >> I told you it was a bad idea. ;-)
> >>
> >> mike
> >>[/color]
> >
> >[/color]
>[/color]