Re: Bonkers over a combo box`

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

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


"NC_Sue" <NCSue@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:310ECCB3-664E-46CF-89F6-56160507C7CE@xxxxxxxxxxxxxxxx
DUH -
I lied.
Row source type: Table/Query
Here's the row source: SELECT [tblContactInformation].[ID],
[tblContactInformation].[ContactType] FROM [tblContactInformation]
--
Thanks for your time!


"Dirk Goldgar" wrote:

"NC_Sue" <NCSue@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:AAD3FA13-2522-49D8-9992-952775ABF4D3@xxxxxxxxxxxxx
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)





.

Relevant Pages

  • Re: Lookup Evils
    ... I don't think there is really a downside to adding a primary key field. ... here that my opinion is that if you are storing a single field it may as ... Row Source Type, ... A lookup field is created in table design view by selecting Insert> Lookup ...
    (microsoft.public.access.tablesdbdesign)
  • Re: Bonkers over a combo box`
    ... the Row Source that tina suggests the DISTINCT operator as ... these newsgroups advise *against* using a Lookup field in any table, ... and remove any *duplicate* links between this ... Row source type: Table/Query ...
    (microsoft.public.access.forms)
  • Re: Last question for awhile...
    ... define a "lookup field" in a table. ... On first glance, it might appear to be "more elegant," but you are very ... It just seems like a sort of loophole way of solving the problem rather ... that stores those values and then use a different row source type of ...
    (comp.databases.ms-access)
  • Re: poor design??
    ... it as a combo box that has the row source type based off of a "Table/Query" ... base the combo box row source on the Employee table, ... It is also identified as such in table design view. ... with a field showing who issued the quote that's a lookup field based on ...
    (microsoft.public.access.tablesdbdesign)
  • Re: About lookup fields..
    ... Then I enter statement: ADOQuery1.Sort:= 'Nameoflookupfield'. ... And what I get the error message: ... You can sort by ... >> Does ADOTable component allows to index or sort itself on lookup field? ...
    (borland.public.delphi.database.ado)