Bonkers over a combo box`

[Guest]

I've designed a form for data entry for my contacts. It draws all of its
bound fields from a table. One of the fields is called "contact type". In my
table, I've got a combo box for this info which sorts the possible contact
types alphabetically from the following value list:
Clinic;Clinicians/Practitioners;Computer Support;Employee
Services;Lab;Monitors;Patients;Personal;Pharmacy;Provider;Regulatory;Scheduling;ServiceProvider;Sponsor;System Support.

I seem to have one of 2 choices in the form I've created when it comes to
the way I can use this bound field for data entry. One requires that I
manually enter the type of contact (which presupposes that I can REMEMBER the
above list). The second, a combo box, isn't working properly. For one thing,
the list of options doesn't sort alphabetically (which I want). For a second
thing, items are duplicated. It seems that there are multiples of each
possible type of contact. I find this annoying, and I find that all of my
attempts at correcting these problems are unsuccessful.

Help?

 

[Dirk Goldgar]

I've designed a form for data entry for my contacts. It draws all of
its
bound fields from a table. One of the fields is called "contact
type". In my
table, I've got a combo box for this info which sorts the possible
contact
types alphabetically from the following value list:
Clinic;Clinicians/Practitioners;Computer Support;Employee
Services;Lab;Monitors;Patients;Personal;Pharmacy;Provider;Regulatory;Scheduling;ServiceProvider;Sponsor;System
Support.

I seem to have one of 2 choices in the form I've created when it
comes to
the way I can use this bound field for data entry. One requires that I
manually enter the type of contact (which presupposes that I can
REMEMBER the
above list). The second, a combo box, isn't working properly. For one
thing,
the list of options doesn't sort alphabetically (which I want). For a
second
thing, items are duplicated. It seems that there are multiples of each
possible type of contact. I find this annoying, and I find that all
of my
attempts at correcting these problems are unsuccessful.

Help?

Open the form in design view, select the combo box, and bring up its
property sheet. Go to the Data tab, find out what it has in each of the
properties Row Source Type and Row Source, and post those values into a
reply to this message. It sounds like the combo box isn't set up the
same way as the one you defined in the table.

All that said, I'd suggest you not use a value list for this, but rather
store the list of contact types in a table and have your combo box(es)
draw from that. That would give you more flexibility to add other
contact types later. But first, let's figure out what's going on with
the combo box you have now.

 

[GeoffG]

Sue:

There are several ways to do this. If I were you, I'd restructure your
database as follows.

(Incidentally, I've not rehearsed the following steps, but they should give
you the general idea.)

1. Turn your ContactType field into a foreign-key field that stores
Numbers (Long Integers). For the sake of clarity, rename the field
"fkContactTypeID" to indicate that it is a foreign-key field. (It's best I
think not to use spaces in field names.)

2. Create a separate table for the possible contacts. Create two fields
in this table:

ContactTypeID (AutoNumber, Primary Key).
ContactType (Text)

3. Create a one-to-many relationship between the "ContactTypeID" field in
this new table and the "fkContactTypeID" field in your main table. Select
Enforce Referential Integrity.

4. To simplify creation of ComboBox, open the main table in design view.
Click the "fkContactTypeID" field. In the Properties of the Field at the
bottom of the window, click the LookUp tab and select ComboBox. Set number
of columns to two. Set the column widths to 0;1 - zero semicolon one (inch).
(Zero hides the first column.) Save the new table design.

5. Open the form in design view. Open the field list. Drag the
"fkContactType" field to the form. This should create the ComboBox
automatically.

6. Select the ComboBox and open its properties sheet. Click in its
RowSource property and click its build button (the square tablet on the
right with the elipsis ...). This enables you to create a query on the table
you created in paragraph 2 above. Ensure the two fields are in the design
grid and set sort ascending on your ContactType field. Close the query and
choose to update the SQL statement for the ComboBox.

In future you can add/remove contacts from the new table.

That should just about do it.
Geoff

 

[Guest]

"Row source type" and "Row source" do not appear in the list under the Data
tab.

 

[Guest]

DUH -
I lied.
Row source type: Table/Query
Here's the row source: SELECT [tblContactInformation].[ID],
[tblContactInformation].[ContactType] FROM [tblContactInformation]

 

[Dirk Goldgar]

DUH -
I lied.
Row source type: Table/Query
Here's the row source: SELECT [tblContactInformation].[ID],
[tblContactInformation].[ContactType] FROM [tblContactInformation]

See, this rowsource query is just extracting a list of all the contact
types currently in your tblContactInformation. It's not sorting them or
removing duplicates, either.

I really think you should approach this along the lines that GeoffG
suggested in his post; that is, you should have a table of contact
types, and either do what GeoffG says and have an autonumber primary key
for this table, storing that as a foreign key in your
tblContactInformation, or else just have the ContactType text field be
the sole field in the table (and its primary key) and store that text
value in tblContactInformation.

If you don't feel like doing that, you can just change the properties of
the combo box on the form to match those of the combo box you defined in
the table. Then you'd be using a Value List rowsourcetype, and the same
value list as you specifed in the table design. That's easy enough to
do, but harder to maintain than using a table to store the permissible
contact types and a query to supply them to the combo box.

 

[tina]

Sue, the first thing i'd recommend is that you get rid of the "combo box" in
your table, which you described in your first post in this thread. it's
called a Lookup field, and the majority of experienced Access developers in
these newsgroups advise *against* using a Lookup field in any table, at any
time. for more information, see http://www.mvps.org/access/lookupfields.htm.

to change the Lookup field to a "regular" field:
open the table in Design view, and click on the field.
in the Field Properties section at the bottom, click the Lookup tab and
change the DisplayControl setting to Text Box.
while still in Design view, open the Indexes dialog and remove any
*duplicate* indexes on this field (one index on this field is fine, just
remove any additional indexes).
save and close the table.
open the Relationships window, and remove any *duplicate* links between this
field and the ID field of tblContactInformation. (a single link is
necessary, just remove any additional links and additional copies of
tblContactInformation, which will show an underscore and number at the end
of the table name, such as "tblContactInformation_1").
save and close the Relationships window.

note that you can, and should, use a combo box control in the *form*, with
the RowSourceType and RowSource that you posted previously. just add a sort
to the SQL statement, as

SELECT [tblContactInformation].[ID],
[tblContactInformation].[ContactType] FROM [tblContactInformation] ORDER BY
[tblContactInformation].[ContactType];

hth

DUH -
I lied.
Row source type: Table/Query
Here's the row source: SELECT [tblContactInformation].[ID],
[tblContactInformation].[ContactType] FROM [tblContactInformation]
--
Thanks for your time!


Services;Lab;Monitors;Patients;Personal;Pharmacy;Provider;Regulatory;Schedul
ing;ServiceProvider;Sponsor;System

Open the form in design view, select the combo box, and bring up its
property sheet. Go to the Data tab, find out what it has in each of the
properties Row Source Type and Row Source, and post those values into a
reply to this message. It sounds like the combo box isn't set up the
same way as the one you defined in the table.

All that said, I'd suggest you not use a value list for this, but rather
store the list of contact types in a table and have your combo box(es)
draw from that. That would give you more flexibility to add other
contact types later. But first, let's figure out what's going on with
the combo box you have now.

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)

 

[Dirk Goldgar]

Sue, the first thing i'd recommend is that you get rid of the "combo
box" in your table, which you described in your first post in this
thread. it's called a Lookup field, and the majority of experienced
Access developers in these newsgroups advise *against* using a Lookup
field in any table, at any time. for more information, see
http://www.mvps.org/access/lookupfields.htm.

to change the Lookup field to a "regular" field:
open the table in Design view, and click on the field.
in the Field Properties section at the bottom, click the Lookup tab
and change the DisplayControl setting to Text Box.
while still in Design view, open the Indexes dialog and remove any
*duplicate* indexes on this field (one index on this field is fine,
just remove any additional indexes).
save and close the table.
open the Relationships window, and remove any *duplicate* links
between this field and the ID field of tblContactInformation. (a
single link is necessary, just remove any additional links and
additional copies of tblContactInformation, which will show an
underscore and number at the end of the table name, such as
"tblContactInformation_1").
save and close the Relationships window.

note that you can, and should, use a combo box control in the *form*,
with the RowSourceType and RowSource that you posted previously. just
add a sort to the SQL statement, as

SELECT [tblContactInformation].[ID],
[tblContactInformation].[ContactType] FROM [tblContactInformation]
ORDER BY [tblContactInformation].[ContactType];

I could be wrong, Tina, but I think tblContactInformation is the base
table for Sue's form, not a lookup table. I don't think she currently
has a table that just lists the contact types. That being the case,
what Sue has in her table isn't really a true lookup field; it's just a
field whose default display control is a combo box with a value list
rowsource. That's no big deal, except for the issue of maintainability
that I mentioned in my earlier post. If, on the other hand, she creates
a table for the contact types, gives it a primary key that is not the
text itself, and sets the display control in the table design to show
the text but store the (hidden) key, then she'll have a true lookup
field.

 

[Guest]

Sue
To eliminate the duplicates that you complained about add to the query for
the Row Source that tina suggests the DISTINCT (or DISTINCTROW) operator as
such:

SELECT DISTINCT [tblContactInformation].[ID],
[tblContactInformation].[ContactType] FROM [tblContactInformation] ORDER BY
[tblContactInformation].[ContactType];

Sue, the first thing i'd recommend is that you get rid of the "combo box" in
your table, which you described in your first post in this thread. it's
called a Lookup field, and the majority of experienced Access developers in
these newsgroups advise *against* using a Lookup field in any table, at any
time. for more information, see http://www.mvps.org/access/lookupfields.htm.

to change the Lookup field to a "regular" field:
open the table in Design view, and click on the field.
in the Field Properties section at the bottom, click the Lookup tab and
change the DisplayControl setting to Text Box.
while still in Design view, open the Indexes dialog and remove any
*duplicate* indexes on this field (one index on this field is fine, just
remove any additional indexes).
save and close the table.
open the Relationships window, and remove any *duplicate* links between this
field and the ID field of tblContactInformation. (a single link is
necessary, just remove any additional links and additional copies of
tblContactInformation, which will show an underscore and number at the end
of the table name, such as "tblContactInformation_1").
save and close the Relationships window.

note that you can, and should, use a combo box control in the *form*, with
the RowSourceType and RowSource that you posted previously. just add a sort
to the SQL statement, as

SELECT [tblContactInformation].[ID],
[tblContactInformation].[ContactType] FROM [tblContactInformation] ORDER BY
[tblContactInformation].[ContactType];

hth

DUH -
I lied.
Row source type: Table/Query
Here's the row source: SELECT [tblContactInformation].[ID],
[tblContactInformation].[ContactType] FROM [tblContactInformation]
--
Thanks for your time!

I've designed a form for data entry for my contacts. It draws all of
its
bound fields from a table. One of the fields is called "contact
type". In my
table, I've got a combo box for this info which sorts the possible
contact
types alphabetically from the following value list:
Clinic;Clinicians/Practitioners;Computer Support;Employee

Services;Lab;Monitors;Patients;Personal;Pharmacy;Provider;Regulatory;Schedul
ing;ServiceProvider;Sponsor;System

Support.

I seem to have one of 2 choices in the form I've created when it
comes to
the way I can use this bound field for data entry. One requires that I
manually enter the type of contact (which presupposes that I can
REMEMBER the
above list). The second, a combo box, isn't working properly. For one
thing,
the list of options doesn't sort alphabetically (which I want). For a
second
thing, items are duplicated. It seems that there are multiples of each
possible type of contact. I find this annoying, and I find that all
of my
attempts at correcting these problems are unsuccessful.

Help?

Open the form in design view, select the combo box, and bring up its
property sheet. Go to the Data tab, find out what it has in each of the
properties Row Source Type and Row Source, and post those values into a
reply to this message. It sounds like the combo box isn't set up the
same way as the one you defined in the table.

All that said, I'd suggest you not use a value list for this, but rather
store the list of contact types in a table and have your combo box(es)
draw from that. That would give you more flexibility to add other
contact types later. But first, let's figure out what's going on with
the combo box you have now.

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)

 

[Dirk Goldgar]

Sue
To eliminate the duplicates that you complained about add to the
query for the Row Source that tina suggests the DISTINCT (or
DISTINCTROW) operator as such:

SELECT DISTINCT [tblContactInformation].[ID],
[tblContactInformation].[ContactType] FROM [tblContactInformation]
ORDER BY [tblContactInformation].[ContactType];

But if I am right about what tblContactInformation is, that will only
show those contact types that are currently in the table, which will not
include any that are permissible but haven't been used yet. She either
needs a table of just the contact types, or else (using the above
approach) needs to set the combo's Limit to List property to No and
accept possible misspellings of existing contact types.

 

[Guest]

The query which includes the DISTINCT operator will prevent any data input.
It creates a none-updateable recordset. Setting the property LIMIT TO LIST is
not necessary although will not hurt.

JD

Sue
To eliminate the duplicates that you complained about add to the
query for the Row Source that tina suggests the DISTINCT (or
DISTINCTROW) operator as such:

SELECT DISTINCT [tblContactInformation].[ID],
[tblContactInformation].[ContactType] FROM [tblContactInformation]
ORDER BY [tblContactInformation].[ContactType];

But if I am right about what tblContactInformation is, that will only
show those contact types that are currently in the table, which will not
include any that are permissible but haven't been used yet. She either
needs a table of just the contact types, or else (using the above
approach) needs to set the combo's Limit to List property to No and
accept possible misspellings of existing contact types.

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)

 

[Douglas J. Steele]

The presence of the DISTINCT operator in the RowSource for a combobox will
have no impact on whether the form itself is updatable.

I do agree that DISTINCT in the RecordSource for the form would make the
form read-only.

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

The query which includes the DISTINCT operator will prevent any data
input.
It creates a none-updateable recordset. Setting the property LIMIT TO LIST
is
not necessary although will not hurt.

JD

Sue
To eliminate the duplicates that you complained about add to the
query for the Row Source that tina suggests the DISTINCT (or
DISTINCTROW) operator as such:

SELECT DISTINCT [tblContactInformation].[ID],
[tblContactInformation].[ContactType] FROM [tblContactInformation]
ORDER BY [tblContactInformation].[ContactType];

But if I am right about what tblContactInformation is, that will only
show those contact types that are currently in the table, which will not
include any that are permissible but haven't been used yet. She either
needs a table of just the contact types, or else (using the above
approach) needs to set the combo's Limit to List property to No and
accept possible misspellings of existing contact types.

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)

 

[Dirk Goldgar]

The query which includes the DISTINCT operator will prevent any data
input. It creates a none-updateable recordset. Setting the property
LIMIT TO LIST is not necessary although will not hurt.

I believe you are missing my point.

 

[tina]

yeah, i caught that when i read your post that hit half an hour before mine,
then went back and re-read Sue's first post. obviously my reading
comprehension ain't worth beans today! <g>

Sue, the first thing i'd recommend is that you get rid of the "combo
box" in your table, which you described in your first post in this
thread. it's called a Lookup field, and the majority of experienced
Access developers in these newsgroups advise *against* using a Lookup
field in any table, at any time. for more information, see
http://www.mvps.org/access/lookupfields.htm.

to change the Lookup field to a "regular" field:
open the table in Design view, and click on the field.
in the Field Properties section at the bottom, click the Lookup tab
and change the DisplayControl setting to Text Box.
while still in Design view, open the Indexes dialog and remove any
*duplicate* indexes on this field (one index on this field is fine,
just remove any additional indexes).
save and close the table.
open the Relationships window, and remove any *duplicate* links
between this field and the ID field of tblContactInformation. (a
single link is necessary, just remove any additional links and
additional copies of tblContactInformation, which will show an
underscore and number at the end of the table name, such as
"tblContactInformation_1").
save and close the Relationships window.

note that you can, and should, use a combo box control in the *form*,
with the RowSourceType and RowSource that you posted previously. just
add a sort to the SQL statement, as

SELECT [tblContactInformation].[ID],
[tblContactInformation].[ContactType] FROM [tblContactInformation]
ORDER BY [tblContactInformation].[ContactType];

I could be wrong, Tina, but I think tblContactInformation is the base
table for Sue's form, not a lookup table. I don't think she currently
has a table that just lists the contact types. That being the case,
what Sue has in her table isn't really a true lookup field; it's just a
field whose default display control is a combo box with a value list
rowsource. That's no big deal, except for the issue of maintainability
that I mentioned in my earlier post. If, on the other hand, she creates
a table for the contact types, gives it a primary key that is not the
text itself, and sets the display control in the table design to show
the text but store the (hidden) key, then she'll have a true lookup
field.

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)