Initial Value for Combo Box Text field

[Lyn]

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.

[Wayne Morgan]

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" <lh******@ihug.com.au> wrote in message
news:ct**********@lust.ihug.co.nz...

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.

[Lyn]

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" <co***************************@hotmail.com> wrote in message
news:8I*******************@newssvr30.news.prodigy. com...
[--snip--]

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

[Wayne Morgan]

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" <lh******@ihug.com.au> wrote in message
news:cu**********@lust.ihug.co.nz...

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" <co***************************@hotmail.com> wrote in
message news:8I*******************@newssvr30.news.prodigy. com...
[--snip--]

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]

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" <co***************************@hotmail.com> wrote in message
news:Jh******************@newssvr12.news.prodigy.c om...

[--snip--]

--
Wayne Morgan
MS Access MVP
"Lyn" <lh******@ihug.com.au> wrote in message
news:cu**********@lust.ihug.co.nz...

[--snip--]

This is what I get in the [...] dropdown:

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

[Wayne Morgan]

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" <lh******@ihug.com.au> wrote in message
news:cu**********@lust.ihug.co.nz...

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.

[Lyn]

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" <co***************************@hotmail.com> wrote in message
news:RG**************@newssvr12.news.prodigy.com.. .

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

[Wayne Morgan]

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" <lh******@ihug.com.au> wrote in message
news:cu**********@lust.ihug.co.nz...

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 !?

[Lyn]

Thanks for confirming that I am not going mad!

--
Cheers,
Lyn.

"Wayne Morgan" <co***************************@hotmail.com> wrote in message
news:gj*****************@newssvr12.news.prodigy.co m...

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