How to set up a combo box & Should I use a query?

[BobC]

I keep getting confused on how to use a combo box and prevent the wrong
field from being changed ... i.e. I do not want the HA# to be edited in
the table.

I want to create a form to edit a table (tblHANames)
The table has only 2 fields (HA#, HAName)
I want to use a combo box with 2 columns to locate the record to be edited.
The table is in HA# sequence (key)
I do not want the user to be able to change the HA# field (containing
the numbers 1 to 150) ... only the names in the HAName fields.

The first question was whether or not it is best to use a query?

Next, how do I set up the combo box to be able to see both the HA# and
HAName and not be able to change the HA# in the table? ...(e.g. end up
with 2 or 3 # 15s in the table)?

 

[Jeanette Cunningham]

BobC,

use a query for the combo
In the query design, put HA# for the first column and HAName for the second
column.
For the combo, set its rowsource to the query.
Set its column count to 2
Set first column width to 1 cm or half inch, second column to 4 cm or 2
inches
Make the combo unbound - make its control source empty
Set the combos Limit to List property to Yes - this means users can't add a
new value to it.

Jeanette Cunningham

 

[BobC]

Thanks!
Bob

BobC,

use a query for the combo
In the query design, put HA# for the first column and HAName for the second
column.
For the combo, set its rowsource to the query.
Set its column count to 2
Set first column width to 1 cm or half inch, second column to 4 cm or 2
inches
Make the combo unbound - make its control source empty
Set the combos Limit to List property to Yes - this means users can't add a
new value to it.

Jeanette Cunningham

 

[John W. Vinson]

how do I set up the combo box to be able to see both the HA# and
HAName and not be able to change the HA# in the table? ...(e.g. end up
with 2 or 3 # 15s in the table)?

Just to add to Jeanette's good advice...

a Combo Box GETS data from its Rowsource.
It STORES data into its Control Source.

If you don't want it to store data, leave the control source blank; it will
still *get* the HA# if that's what's in its rowsource query, and you can use
the combo's value in code to find a record, but if there's nothing in the
Control Source, it won't be stored anywhere.

 

[BobC]

THANK YOU!!!!!
I have been reading and trying to understand combo boxes for the last 2
hours ... your explanation clarifies a lot.
I have one more question if you don't mind ...
Does it matter which position in a 2 column the row source the field is
that I want to change?
Bob

 

[Rick Brandt]

THANK YOU!!!!!
I have been reading and trying to understand combo boxes for the last
2 hours ... your explanation clarifies a lot.
I have one more question if you don't mind ...
Does it matter which position in a 2 column the row source the field
is that I want to change?
Bob

Sounds like you are still confused. A ComboBox is NOT used to change any of
the data you see within the rows of the ComboBox. It is a resource for the
user to enter a value on your form. In that regard it's the same as a
TextBox. However; it provides two things that a TextBox does not.

1) It can simplify or speed up entry by offering a list of choices rather
than forcing the user to type out all of the characters.

2) It can restrict entries to only those provided in the list when the
property LimitToList is enabled.

Now, there is a special case. You can have a form bound to your table with
the two fields and you can use that form to edit none, one, or both fields
simply by enabling the locked property on the control(s) you do not want the
user changing.

To make it easy to find the record in your table that the user wants to edit
you can add a ComboBox that is used to *navigate* to the appropriate record.
In this capacity the ComboBox will use the same table as its RowSource as
your form is using, but its ONLY purpose is as a navigation tool. It plays
no role in the editing of the record the user is taken to. That would be
done by using other *bound* controls that you have on the form. The
navigation ComboBox would be unbound (blank ControlSource).

If this is what you want the ComboBox for then first build your form that
allows you to edit your data. Once that is built then add the ComboBox
making sure that the toolbox Wizard is turned on. One of the choices for
the ComboBox wizard will build exactly the ComboBox you need.

 

[BobC]

Well ... I finally have a working form!!!!!!!!!!!!!

A couple of follow-up questions:

Why did I have to delete the existing combo box and rebuild it with a
wizard to get the text boxes to *navigate* to the same record number as
located by the combobox? It seems like I could have changed something
to get that to happen?

A Note: I WOULD HAVE BEEN WORKING ON THIS FOR POSSIBLY DAYS TO GET THE
UNDERSTANDING THAT YOU GUYS HAVE GIVEN ME .... THANKS!!!!!!!!!!!

 

[Rick Brandt]

Well ... I finally have a working form!!!!!!!!!!!!!

A couple of follow-up questions:

Why did I have to delete the existing combo box and rebuild it with a
wizard to get the text boxes to *navigate* to the same record number
as located by the combobox? It seems like I could have changed
something to get that to happen?

Well, you didn't actually. I just felt that was the quickest route from
point a to point b.

You could have just cleared the ControlSource and added the appropriate code
to the Combo's AfterUpdate event.

 

[BobC]

You are probably right about my route from a to b ... unless of course
the natural route is via point q ;-)
Thanks Again!

 

[John W. Vinson]

THANK YOU!!!!!
I have been reading and trying to understand combo boxes for the last 2
hours ... your explanation clarifies a lot.
I have one more question if you don't mind ...
Does it matter which position in a 2 column the row source the field is
that I want to change?
Bob

The combo has a number of interrelated properties. This one is the Bound
Column. See below...

RowSource: a Query which retrives from one to ten (I think more are allowed in
recent versions) fields from a table (or from joined tables); some fields can
be calculated fields if that's appropriate.

ColumnCount: How many of those columns are included in the combo box itself.

ColumnWidths: a string of numbers, as many as ColumnCount, separated by
semicolons; each number is the width in inches or centimeters of the
corresponding column. Zero width columns are invisible when the combo is
dropped down; only the first nonzero width column is visible when the combo is
not dropped down.

BoundColumn: Which column will contain the Value of the combo box.

Control Source: which field in the Form's Recordsource will receive the
selected value. May be blank.

For example, you might have a query

SELECT PersonID, LastName & ", " & FirstName, Position, DOB
FROM Personnel
ORDER BY LastName, FirstName;

Thiss query would return records like

312; "Aarons, Michael"; "Researcher"; 3/2/1955
506; "Able, Janet"; "VP Marketing"; 5/10/1950
229; "Acton, Bill"; "Custodian"; 11/20/1946

This query might be used as the Rowsource of a combo box cboPersonnel. Set its
ColumnCount to 4 and its ColumnWidths property to

0";1.25";0.75";0.5"

and you'll see only the (calculated field) full name displayed when the combo
is not in use, but (for identification purposes if you have duplicate names)
the person's position and date of birth when it's dropped down.

Set the Bound Column to 1 and it will store the PersonID into a PersonID
foreign key field in the form's recordource (if its Control Source is PersonID
or the corresponding field in that table). If the Control Source is blank, you
can put (or let the wizard put) VBA code in the combo's AfterUpdate event to
navigate to that record, or to do something else with that PersonID.