Initial Value for Combo Box Text field

Hi,
Can anyone tell me how the initial value displayed in Combo Box is
determined when a form is opened?

I am loading the dropdown from one field ("CategoryName") of a table, with
"ORDER BY [CategoryName]". The values in the dropdown are loaded in the
correct order, but the initial value is not the first row of the dropdown as
I expected.

The field "CategoryName" is not the primary key for the table -- the PK is
an Autonum field. I don't know if it is coincidence, but the CategoryName
value loaded corresponds to the record with "1" in the autonum field
(currently I only have about three records in the table anyway). So is the
initial value determined by the PK record? (I am not using ColumnHeads --
if I do, the initial value seems to be random.)

And if I can ask a secondary question, I would like the initial value in the
Combo Box Text field to be an instruction rather than a valid value, to
force the user to make a selection from the dropdown (eg "<Select from
Dropdown>"). The only solution I can see is include the instruction as a
valid record in the table, arrange for it to be displayed as the initial
value (hence my original question above), and then programmatically deal
with it if the user selects this value (ie, error message).

I am sure that there must be a simpler way to set this up. Any help
appreciated.

--
Cheers,
Lyn.

re: Initial Value for Combo Box Text field

The value in the combo box when the form opens or moves to a particular
record will depend on the value stored in the field that the combo box is
bound to. If it is a new record and you haven't defined a default value then
the combo box's textbox should be blank.

To order the items in the drop down, base the Row Source of the combo box on
a query instead of directly on the table and set the query to sort the
desired field(s).

Remember that the displayed column in the combo box may not be the bound
column (the value stored in the field that the combo box is bound to). The
first visible column will be displayed in the textbox portion of the combo
box.

To set a particular row, such as the first row, to be displayed in the combo
box automatically, you could use code to set the value of the combo box. To
do this for a new record, in the form's Current event you could try
something like:

If Me.NewRecord Then
Me.cboMyCombo = Me.cboMyCombo.Column(0,0)
End If

The Column indexes are zero based, so zero would be the first column or row.
The above assumes that the bound column is the first column. If not, then
change the first zero to the Bound Column value minus 1 (the Bound Column
value is 1 based, the Column value is 0 based).

--
Wayne Morgan
MS Access MVP


"Lyn" <lhancock@ihug.com.au> wrote in message
news:ctvs8l$p2e$1@lust.ihug.co.nz...[color=blue]
> Hi,
> Can anyone tell me how the initial value displayed in Combo Box is
> determined when a form is opened?
>
> I am loading the dropdown from one field ("CategoryName") of a table, with
> "ORDER BY [CategoryName]". The values in the dropdown are loaded in the
> correct order, but the initial value is not the first row of the dropdown
> as I expected.
>
> The field "CategoryName" is not the primary key for the table -- the PK is
> an Autonum field. I don't know if it is coincidence, but the CategoryName
> value loaded corresponds to the record with "1" in the autonum field
> (currently I only have about three records in the table anyway). So is
> the initial value determined by the PK record? (I am not using
> ColumnHeads -- if I do, the initial value seems to be random.)
>
> And if I can ask a secondary question, I would like the initial value in
> the Combo Box Text field to be an instruction rather than a valid value,
> to force the user to make a selection from the dropdown (eg "<Select from
> Dropdown>"). The only solution I can see is include the instruction as a
> valid record in the table, arrange for it to be displayed as the initial
> value (hence my original question above), and then programmatically deal
> with it if the user selects this value (ie, error message).
>
> I am sure that there must be a simpler way to set this up. Any help
> appreciated.
>
> --
> Cheers,
> Lyn.
>
>[/color]

re: Initial Value for Combo Box Text field

Wayne,
Thanks for your help. I have reduced the combo box dropdown to a single
column, as follows.

ColumnCount = 1
BoundColumn = 1
ControlSource = [CategoryName]
RowSourceType = Table/Query
RowSource = "SELECT MiscInfoCats.CategoryName FROM MiscInfoCats ORDER BY
[CategoryName]; "

I am dealing with existing records, not trying to create new ones at this
point. This is my table:

IDCategory CategoryName
1 ZSelect from Dropdown> [Changed "<" to "Z" as
experiment]
2 Test1
14 Test2
16 1234567890
17 <Select from Dropdown>

IDCategory is the Autonum field which is the PK, but it takes no part in the
query used to establish the dropdown.

This is what I get in the text field and dropdown:

ZSelect from Dropdown> [Text field]

<Select from Dropdown>
1234567890
Test1
Test2
ZSelect from Dropdown>

Two observations here:

1) The initial value in the Text field seems to be based on the PK (lowest
value "1").

2) The dropdown is based on the ORDER specified by the query -- except I
would have expected value "1234567890" to appear above "<Select..." ("1" is
Chr(49) vs "<" which is Chr(60) ). I am using Option Compare Database which
may have this order?

Could you please comment on this?

Thanks again.

--
Cheers,
Lyn.

"Wayne Morgan" <comprev_gothroughthenewsgroup@hotmail.com> wrote in message
news:8ISMd.32213$iC4.10032@newssvr30.news.prodigy. com...
[--snip--][color=blue]
> The Column indexes are zero based, so zero would be the first column or
> row. The above assumes that the bound column is the first column. If not,
> then change the first zero to the Bound Column value minus 1 (the Bound
> Column value is 1 based, the Column value is 0 based).
>
> --
> Wayne Morgan
> MS Access MVP[/color]

re: Initial Value for Combo Box Text field

You say "initial value in the text field". Unless you've set a default
value, the control is bound to a field in the form's record source and that
field has a value for that record, or there is code setting the value, then
there should be no "initial value". The textbox portion of the combo box
should be blank. I can think of two exceptions to this. 1) The control is
unbound, you've selected a value, and you move to another record. Since the
control is unbound there is nothing in the moving to another record that
will change the data in the control unless you've coded something. 2) If you
are going between form Design view and Form view without closing the form,
then the form may remember the last value displayed.

If the control is behaving in a way other than described above, I would
suspect a corrupted control. Try deleting the combo box and creating a new
one.

--
Wayne Morgan
MS Access MVP


"Lyn" <lhancock@ihug.com.au> wrote in message
news:cu16hi$crb$1@lust.ihug.co.nz...[color=blue]
> Wayne,
> Thanks for your help. I have reduced the combo box dropdown to a single
> column, as follows.
>
> ColumnCount = 1
> BoundColumn = 1
> ControlSource = [CategoryName]
> RowSourceType = Table/Query
> RowSource = "SELECT MiscInfoCats.CategoryName FROM MiscInfoCats ORDER BY
> [CategoryName]; "
>
> I am dealing with existing records, not trying to create new ones at this
> point. This is my table:
>
> IDCategory CategoryName
> 1 ZSelect from Dropdown> [Changed "<" to "Z" as
> experiment]
> 2 Test1
> 14 Test2
> 16 1234567890
> 17 <Select from Dropdown>
>
> IDCategory is the Autonum field which is the PK, but it takes no part in
> the query used to establish the dropdown.
>
> This is what I get in the text field and dropdown:
>
> ZSelect from Dropdown> [Text field]
>
> <Select from Dropdown>
> 1234567890
> Test1
> Test2
> ZSelect from Dropdown>
>
> Two observations here:
>
> 1) The initial value in the Text field seems to be based on the PK (lowest
> value "1").
>
> 2) The dropdown is based on the ORDER specified by the query -- except I
> would have expected value "1234567890" to appear above "<Select..." ("1"
> is Chr(49) vs "<" which is Chr(60) ). I am using Option Compare Database
> which may have this order?
>
> Could you please comment on this?
>
> Thanks again.
>
> --
> Cheers,
> Lyn.
>
> "Wayne Morgan" <comprev_gothroughthenewsgroup@hotmail.com> wrote in
> message news:8ISMd.32213$iC4.10032@newssvr30.news.prodigy. com...
> [--snip--][color=green]
>> The Column indexes are zero based, so zero would be the first column or
>> row. The above assumes that the bound column is the first column. If not,
>> then change the first zero to the Bound Column value minus 1 (the Bound
>> Column value is 1 based, the Column value is 0 based).
>>
>> --
>> Wayne Morgan
>> MS Access MVP[/color]
>
>[/color]

re: Initial Value for Combo Box Text field

Wayne, thanks again.

Since your earlier response I have done some more investigation and learned
some more about Combo Boxes that I didn't know. Originally the combo box
was bound and it appears that by default (when the form is opened) it
displays the value of the first record according to the primary key. I made
the cbo unbound (it was only intended to be used to select a field on which
to search for the associated record and so shouldn't have been bound anyway)
and this resulted in the cbo Text field initializing as BLANK which is one
of the two acceptable outcomes I was looking for, and I have moved on from
there.

The only outstanding question I still have (which is largely academic now,
but I am curious to know the answer) is why the dropdown list which was
ordered in ascending order has the value beginning with Chr(60) or "<"
before the value beginning with Chr(49) which is "1". I can only assume
that it is the result of using "Option Compare Database". The only
information I can find on this option is that it uses the sorting method of
the Jet database and that it depends on the PC's locale and language. In my
case this is "English (Australia)" which, as far as text ordering is
concerned, should be the same as "English (United States)". If anyone knows
a good web reference that explains how this works for different locales I
would appreciate hearing of it.

--
Cheers,
Lyn.

"Wayne Morgan" <comprev_gothroughthenewsgroup@hotmail.com> wrote in message
news:JhiNd.20020$2e7.6702@newssvr12.news.prodigy.c om...

[--snip--][color=blue]
> --
> Wayne Morgan
> MS Access MVP
>
>
> "Lyn" <lhancock@ihug.com.au> wrote in message
> news:cu16hi$crb$1@lust.ihug.co.nz...[/color]

[--snip--][color=blue][color=green]
>> This is what I get in the [...] dropdown:
>>
>> <Select from Dropdown>
>> 1234567890
>> Test1
>> Test2
>> ZSelect from Dropdown>[/color][/color]

re: Initial Value for Combo Box Text field

I just copied and pasted your examples into a table, created a query based
on the table and sorted the field with your examples. I am set for Option
Compare Database also and didn't have the problem, it sorted as you indicate
you expect. One thing that could cause this would be a space in front of the
number. Access will trim trailing spaces automatically, but will leave
preceding spaces in the data.

--
Wayne Morgan
MS Access MVP


"Lyn" <lhancock@ihug.com.au> wrote in message
news:cu4vt6$pn0$1@lust.ihug.co.nz...[color=blue]
> Wayne, thanks again.
>
> Since your earlier response I have done some more investigation and
> learned some more about Combo Boxes that I didn't know. Originally the
> combo box was bound and it appears that by default (when the form is
> opened) it displays the value of the first record according to the primary
> key. I made the cbo unbound (it was only intended to be used to select a
> field on which to search for the associated record and so shouldn't have
> been bound anyway) and this resulted in the cbo Text field initializing as
> BLANK which is one of the two acceptable outcomes I was looking for, and I
> have moved on from there.
>
> The only outstanding question I still have (which is largely academic now,
> but I am curious to know the answer) is why the dropdown list which was
> ordered in ascending order has the value beginning with Chr(60) or "<"
> before the value beginning with Chr(49) which is "1". I can only assume
> that it is the result of using "Option Compare Database". The only
> information I can find on this option is that it uses the sorting method
> of the Jet database and that it depends on the PC's locale and language.
> In my case this is "English (Australia)" which, as far as text ordering is
> concerned, should be the same as "English (United States)". If anyone
> knows a good web reference that explains how this works for different
> locales I would appreciate hearing of it.[/color]

re: Initial Value for Combo Box Text field

Interesting...

I had deleted the "<" entry with further development, but I have just added
it back in to the table. The combo box dropdown still shows the order:

<Select>
1234567890
Test1
Test2

I also sorted the table directly by selecting the CategoryName column, then
Records -> Sort -> Sort Ascending. I still get the above result. The same
of course using the AZ sort button.

I am not sure what your locale is, but I just changed mine from "English
(Australia)" to "English (United States)". This made no difference (as
expected).

There are no leading spaces in any of the values. I am running Access 2003
under Win XP. As I said before, this is mainly of academic interest now,
although it could become an issue with some future development -- ie, why is
my instance of Access not behaving as expected and as other instances behave
!?

--
Cheers,
Lyn.

"Wayne Morgan" <comprev_gothroughthenewsgroup@hotmail.com> wrote in message
news:RGCNd.248$D34.62@newssvr12.news.prodigy.com.. .[color=blue]
>I just copied and pasted your examples into a table, created a query based
>on the table and sorted the field with your examples. I am set for Option
>Compare Database also and didn't have the problem, it sorted as you
>indicate you expect. One thing that could cause this would be a space in
>front of the number. Access will trim trailing spaces automatically, but
>will leave preceding spaces in the data.
>
> --
> Wayne Morgan
> MS Access MVP
>[/color]

re: Initial Value for Combo Box Text field

I went back and looked again. You're right, it does the same thing on mine.
I just wasn't thinking. Normally when an item is marked with the < it is
because the user wants it first and it will come before letters, so seeing
it first just appeared to be normal.

--
Wayne Morgan
MS Access MVP


"Lyn" <lhancock@ihug.com.au> wrote in message
news:cu721r$kl3$1@lust.ihug.co.nz...[color=blue]
> Interesting...
>
> I had deleted the "<" entry with further development, but I have just
> added it back in to the table. The combo box dropdown still shows the
> order:
>
> <Select>
> 1234567890
> Test1
> Test2
>
> I also sorted the table directly by selecting the CategoryName column,
> then Records -> Sort -> Sort Ascending. I still get the above result.
> The same of course using the AZ sort button.
>
> I am not sure what your locale is, but I just changed mine from "English
> (Australia)" to "English (United States)". This made no difference (as
> expected).
>
> There are no leading spaces in any of the values. I am running Access
> 2003 under Win XP. As I said before, this is mainly of academic interest
> now, although it could become an issue with some future development -- ie,
> why is my instance of Access not behaving as expected and as other
> instances behave !?[/color]

re: Initial Value for Combo Box Text field

Thanks for confirming that I am not going mad!

--
Cheers,
Lyn.

"Wayne Morgan" <comprev_gothroughthenewsgroup@hotmail.com> wrote in message
news:gj1Od.6654$D34.1652@newssvr12.news.prodigy.co m...[color=blue]
>I went back and looked again. You're right, it does the same thing on mine.
>I just wasn't thinking. Normally when an item is marked with the < it is
>because the user wants it first and it will come before letters, so seeing
>it first just appeared to be normal.
>
> --
> Wayne Morgan
> MS Access MVP[/color]