.adp combo box in form pulling repeated options

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have created a data entry form with a combo box field. The form is
designed to do data input for a subscription table. The field I want to fill
is called noticeid. It is an integer field. I fill the combo box by using a
query that links the subscription table (via the notcieid field) to a entryid
field in the notice table. Entryid is a keyed field (integer). The value we
want displayed for the drop down is the noticename field (a text field) in
the notice table. However we want to store the integer in the subscription
table.

All is fine except for the fact that the drop down displays mutliple (many)
values of the same option rather than simply one value for each entry in the
notice table.

I've run into many consistency problems with doing lookups at the table
level. When I created a lookup field in the table, my existing table data
displayed incorrectly (i.e. every value displayed was one number off).

Does anyone have any suggestions or know of references that are helpful in
working with Access projects (.adp files)?
 
Thank you for your suggestion. The DISTINCT keyword definitely does reduce
the number of my selections. However, it screws up the relationship between
the existing values in the main table and the value that it is pulling from
the "lookup" table.

For example, if a row in my main table has a 1 in the field and that links
correctly to the related keyed field in the lookup table. (This field is the
foreign key and contains integers.) When the lookup control in the form is
defined with this select statement - SELECT LookupTbl.ID, LookupTbl.Name FROM
LookupTbl INNER JOIN [maintable] ON LookupTbl.ID=[maintable].[id] ORDER BY
LookupTbl.Name - everything works correctly. New entries can be selected
from the lookup control in the form (even though there are multiple repeated
options) and the old values link correctly.

However, when you add the DISTINCT .... the original maintable values don't
link correctly.

I just can't figure out how to do this and I feel as though it is something
very simple that I'm forgetting.

Thanks for your help.
 
You must use the Distinct keyword only for the lookup control, not for the
main table or for any other queries that you want to be updatable.

--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC


Janet said:
Thank you for your suggestion. The DISTINCT keyword definitely does
reduce
the number of my selections. However, it screws up the relationship
between
the existing values in the main table and the value that it is pulling
from
the "lookup" table.

For example, if a row in my main table has a 1 in the field and that links
correctly to the related keyed field in the lookup table. (This field is
the
foreign key and contains integers.) When the lookup control in the form
is
defined with this select statement - SELECT LookupTbl.ID, LookupTbl.Name
FROM
LookupTbl INNER JOIN [maintable] ON LookupTbl.ID=[maintable].[id] ORDER BY
LookupTbl.Name - everything works correctly. New entries can be selected
from the lookup control in the form (even though there are multiple
repeated
options) and the old values link correctly.

However, when you add the DISTINCT .... the original maintable values
don't
link correctly.

I just can't figure out how to do this and I feel as though it is
something
very simple that I'm forgetting.

Thanks for your help.
--
Janet


Sylvain Lafontaine said:
Use the DISTINCT keyword to remove the multiple values.
Click to expand...
Click to expand...
 
Back
Top