473,231 Members | 1,621 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,231 software developers and data experts.

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.
Nov 13 '05 #1
8 18254
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.

Nov 13 '05 #2
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

Nov 13 '05 #3
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


Nov 13 '05 #4
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>

Nov 13 '05 #5
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.

Nov 13 '05 #6
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

Nov 13 '05 #7
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 !?

Nov 13 '05 #8
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

Nov 13 '05 #9

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

3
by: TekWiz | last post by:
I've got a system that automatically generates a form. I have it set up so that the backend will return to the inital form page with an error object in sessions data (assuming the backend detected...
16
by: cwizard | last post by:
I'm calling on a function from within this form, and there are values set but every time it gets called I get slammed with a run time error... document.frmKitAmount.txtTotalKitValue is null or not...
2
by: Terry Bickle | last post by:
Please forgive me for using the wrong term here or there. I'm an old Excel 4 macro guy who didn't convert to VB and I'm tinkering with an Access 2000 DB. I'm sure there is a simple Access 101...
8
by: Zlatko Matić | last post by:
There is a form (single form) and a combobox. I want that current record of the form is adjusted according to selected value in the combobox. Cuurrent record should be the same as the value in the...
3
by: klawiter | last post by:
Greetings, I have a table with two columns. On my form, I have a combo field that displays the contents of the first column. Upon selecting an item in this combo field, I would like to then...
2
by: Pete | last post by:
Is there any way to add an initial value into a databound combo box on a windows form. I have a combo box set to dropdown list so users cannot add their own items and I fill the data from a...
1
by: sara | last post by:
I am learning how to use simple functions to make my apps more powerful and efficient. On one screen, I want to populate field B: ItemDescription by looking up the ItemDescription in the Items...
2
by: | last post by:
Hi everyone, I have a form with a combo box on it. When you select a value (a PO#) from the combo box, the bound field is the indexID of the selected PO. On the same form, I have a text box...
1
by: =?Utf-8?B?bWZt?= | last post by:
I have recently migrated to VS.Net 2005, and I am struggling to do something very basic with the combo box. I want to manually loop through a data table and add both the display text and the value...
0
by: VivesProcSPL | last post by:
Obviously, one of the original purposes of SQL is to make data query processing easy. The language uses many English-like terms and syntax in an effort to make it easy to learn, particularly for...
0
by: jianzs | last post by:
Introduction Cloud-native applications are conventionally identified as those designed and nurtured on cloud infrastructure. Such applications, rooted in cloud technologies, skillfully benefit from...
0
by: abbasky | last post by:
### Vandf component communication method one: data sharing ​ Vandf components can achieve data exchange through data sharing, state sharing, events, and other methods. Vandf's data exchange method...
2
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 7 Feb 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:30 (7.30PM). In this month's session, the creator of the excellent VBE...
0
by: fareedcanada | last post by:
Hello I am trying to split number on their count. suppose i have 121314151617 (12cnt) then number should be split like 12,13,14,15,16,17 and if 11314151617 (11cnt) then should be split like...
0
by: stefan129 | last post by:
Hey forum members, I'm exploring options for SSL certificates for multiple domains. Has anyone had experience with multi-domain SSL certificates? Any recommendations on reliable providers or specific...
0
Git
by: egorbl4 | last post by:
Скачал я git, хотел начать настройку, а там вылезло вот это Что это? Что мне с этим делать? ...
0
by: Aftab Ahmad | last post by:
Hello Experts! I have written a code in MS Access for a cmd called "WhatsApp Message" to open WhatsApp using that very code but the problem is that it gives a popup message everytime I clicked on...
0
by: Aftab Ahmad | last post by:
So, I have written a code for a cmd called "Send WhatsApp Message" to open and send WhatsApp messaage. The code is given below. Dim IE As Object Set IE =...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.