*#$&@%^ combo box in a form

[NC_Sue]

I have a contacts database with 2 tables. (I know, I know - I should
normalize, but I designed this early on in the Access learning curve & am
just beginning the process of normalization - I have ALL info in a single
table until recently.)

Anyway, there are 2 tables - contacts & contact type. Contact type has 2
fields - the autonumber (primary key) and the type of contact. The table
contains a field in which I have a numeric foreign key which relates to the
primary key in the contact type table. I have a dropdown box in the table
which I can use to enter the contact type, but I want to be able to enter
this info using a form. I can't make the combo box on the form function,
though. It doesn't MAKE a combo bos, and it displays a numeric value (the
foreign key) rather than text (the verbal description of contact type.)

Waah.

What am I doing wrong?

 

[Ofer Cohen]

Open the form in design view, view the properties of the combo.

If the RowSource of the combo include two fields Id and type
Select TypeId, TypeDesc From TableName

The BoundColumn property of the combo should be 1, so it will store the Id
In the ColumnWidth property of the combo should specify that you don't want
the Id to be displayed, only the description

0 cm; 4 cm

That will display only the description but will store the Id

 

[NC_Sue]

There's nothing but the field name under the properties for contact type in
my form - no way to set properties for a combo box. When I try to set row
source, it only allows me to choose from a listing of fields in the original
table.

 

[Rick Brandt]

There's nothing but the field name under the properties for contact
type in my form - no way to set properties for a combo box. When I
try to set row source, it only allows me to choose from a listing of
fields in the original table.

That would be what you see as choices for the ControlSource. The RowSource is a
different property and that should use either a Value List, the Name of a Table
or Query, or a SQL Statement.

 

[NC_Sue]

I can't locate "Row source" under the Data or ALL tabs of the property box.

Again, Waah.

 

[Ofer Cohen]

Lets try this steps

1. Open the form in design view
2. Open the properties (From the menu select: View -> Properties)
3. Click on the combo, now the properties should be displayed
4. On the top of the properties box there are Tabs, select ALL
5. Now that you can see all the properties of the combo, look for the
properties I specified in the first post, what do you have?

 

[NC_Sue]

As I said in last post, "Row source" does not appear anywhere, even under the
ALL tab of the property box. It simply ain't there.

 

[Dirk Goldgar]

In

As I said in last post, "Row source" does not appear anywhere, even
under the ALL tab of the property box. It simply ain't there.

Can you verify that this control on the form is actually a combo box,
not a text box? What does it say in the caption bar of its property
sheet?

 

[Rick Brandt]

As I said in last post, "Row source" does not appear anywhere, even
under the ALL tab of the property box. It simply ain't there.

Then either...

You do not have the control in question selected.

or

The control in question is not a ComboBox.

 

[NC_Sue]

OK - now we're getting somewhere - it's a textbox but I don't want it to be.
How do I make it be a combo box?

 

[NC_Sue]

It wasn't a combo box.
Now I found the combo box control & inserted it but it will only display the
primary key, not the text associated with the primary key. How do I fix that?

You guys are so patient - thanks.

 

[Ofer Cohen]

Have you tried what I suggested in first post?

 

[LauriS]

I have a dropdown box in the table

Problem #1. Tables are for storing data - not selecting it. Dropdowns
belong only in the forms - not in the tables.

Before you click on the Dropdown button in the form design view make sure
the Wizard button is on (looks like a magic wand). This will walk you
through setting up the combo box.

Once the wizard is finished go back to the properties for that object and
look at them. See how the information you gave the wizard was applied. This
is how I learned to do them on my own - use the Wizard first, see what it did
then try it myself.

Lauri

 

[jeff]

Lauri

Problem #1. Tables are for storing data - not selecting it. Dropdowns
belong only in the forms - not in the tables.

Not quite True. Tables can be used to store data as lookup data for
inserting into other tables ie say for Postcodes for a persons
address..using a combo box here would list the postcodes one could select
and this can then be used to automatically set Suburb etc after selecting
the postcode....

Also a feature of Access is in setting a particular field in the table
design to a combo box and establishing its lookup query and columns in the
actual table. This will then mean that when a user selects a field from the
field list in the form design and drops it onto the form, the field will
automatically be a dropdown combo box with all its lookup and column details
set. This type of feature is what makes Access such a powerful rapid
development environment.

the reest is there....

Agree with using the wizard for novices...a great way to learn....

my 2 bobs worth!

cheers
jeff

 

[Rick Brandt]

Lauri

Not quite True. Tables can be used to store data as lookup data for
inserting into other tables ie say for Postcodes for a persons
address..using a combo box here would list the postcodes one could
select and this can then be used to automatically set Suburb etc
after selecting the postcode....

Lauri was not saying that tables *could not* be used this way, but rather that
they *should not* be. At least not in anything more serious than a CD
collection table.

 

[NC_Sue]

Care to give another 2 bobs or so?
I tried Lauri's suggestions & now when I enter a contact type for one
person, EVERYONE's contact type changes to match that particular person. Why
come???

 

[NC_Sue]

Can you tell me the best way to proceed, then? At this point I seem unable to
get the dropdown box in my form to function as I want it to. Either
EVERYONE's contact type gets changed to the same value when I change this
field for one individual or I can only get the numeric key to display, not
the text. Do I need to change my tables? My forms? My relationships?

 

[Rick Brandt]

Can you tell me the best way to proceed, then? At this point I seem
unable to get the dropdown box in my form to function as I want it
to. Either EVERYONE's contact type gets changed to the same value
when I change this field for one individual

That means you didn't bind the ComboBox to a field in your table.

or I can only get the
numeric key to display, not the text. Do I need to change my tables?
My forms? My relationships?

You need a two column ComboBox. The first column is hidden and is the bound
column holding the ID and hte second visible column show the text.

 

[Aussie Jeff]

Rick

Thanks for commenting on my use of tables to store lookup data. I am afraid
that I have been involved in a large commercial software package (EDRMS)
based on SQL Server and Oracle. Lookup tables were used extensively for
exactly that and also because lookup data (eg in my example Postcodes) can
be added to then tables are far better than hardcoding the choices. At some
sites, this system had GIGABYTES of data and well over 200 concurrent users.

My comments are absolutely valid and do not belong in the realms of a CD
collection table!

My other comment was to correct the statement that "Dropdowns belong only in
the forms - not in the tables." This is not true when it comes to Access as
you would well know...it is a powerful rapid application development feature
of Access to define dropdowns in the tables to facilitate form design.

As far as poor Sue is involved this is not helping her...obviously.

I am about to suggest that I send her a set of screen grabs to paint the
thousand words that only a picture can do!

This is obviously very long thread to solve what is a rather straight
forward issue for those who know..

cheers
Jeff