Lookup Value List Keeps Growing (1 Viewer)

MatthewB

Member
Local time
Yesterday, 19:08
Joined
Mar 30, 2022
Messages
78
I have a lookup on a foreign key that associates a strata to a task. So I use the lookup to provide a strata plan nr. It seems with each new record I create and assign a strata plan nr my value list grows by one value of that nr. Is there a known cause for this?
 

theDBguy

I’m here to help
Staff member
Local time
Yesterday, 19:08
Joined
Oct 29, 2018
Messages
19,200
Not sure I follow. Can you post your table structure?
 

June7

AWF VIP
Local time
Yesterday, 18:08
Joined
Mar 9, 2014
Messages
4,461
By 'lookup' do you mean a combobox? What is combobox RowSource? If you want to provide db for analysis, follow instructions at bottom of my post.
 

MatthewB

Member
Local time
Yesterday, 19:08
Joined
Mar 30, 2022
Messages
78
T
 

Attachments

  • Screenshot (12).png
    Screenshot (12).png
    25.3 KB · Views: 70

MatthewB

Member
Local time
Yesterday, 19:08
Joined
Mar 30, 2022
Messages
78
ELECT StrataPlan_T.kp_StrataPlan_ID, StrataPlan_T.StrataPlanNr FROM StrataPlan_T INNER JOIN Quoting_T ON StrataPlan_T.kp_StrataPlan_ID = Quoting_T.kf_StrataPlan_ID ORDER BY StrataPlan_T.StrataPlanNr;
 

June7

AWF VIP
Local time
Yesterday, 18:08
Joined
Mar 9, 2014
Messages
4,461
Neither of those posts help me understand your database structure nor behavior.
 

Mike Krailo

Active member
Local time
Yesterday, 22:08
Joined
Mar 28, 2020
Messages
635
Why is there duplicate data in your combo box and what do those codes represent? What exactly is a strata plan? Something doesn't look right but it is made even more difficult to decipher when only shown a small portion of the form. Show the table structure and the whole form for context. Usually duplicate data is OK if you are grouping items together based on a classification of some type as long as the unique values are in another table but in your case the data in the dropdown shows duplicates which suggests that there are unique ID's that are stored but the displayed value could be the same. Not sure what to make of it yet.

It seems with each new record I create and assign a strata plan nr my value list grows by one value of that nr. Is there a known cause for this?
My guess is a problem with table structure so need to see what you have to be of any help.
 

moke123

AWF VIP
Local time
Yesterday, 22:08
Joined
Jan 11, 2013
Messages
2,910
have you tried "Select Distinct ..."
 

MatthewB

Member
Local time
Yesterday, 19:08
Joined
Mar 30, 2022
Messages
78
I will strip out my db and upload. Thanks. Tonight. PST
 

Pat Hartman

Super Moderator
Staff member
Local time
Yesterday, 22:08
Joined
Feb 19, 2002
Messages
36,961
Just fix the RowSource query as moke suggested.
 

MatthewB

Member
Local time
Yesterday, 19:08
Joined
Mar 30, 2022
Messages
78
I solve the problem.
I need to delete the relationship between kp and kf StrataPlan, delete the row soure for the look up. recreate the look up and then creat a one to many between the Kp & kf StrataPLan. Now my lookup does not show duplicates of my strata plan nrs. stratas are condominium buildings. Does one need to make lookups before creating one to many relationships? Maybe deleting the lookup and recreating it might have sufficed.
 

Pat Hartman

Super Moderator
Staff member
Local time
Yesterday, 22:08
Joined
Feb 19, 2002
Messages
36,961
One should NEVER, EVER use lookups at the table level. They are a crutch for people who don't know how to create a query or write code. Once you get to the point of doing these two things, the evils of table level lookups will come to haunt you. Combo boxes on forms solve the problem of data entry. The user gets to see a meaningful value but behind the scenes, the numeric foreign key is stored and that is what you need for joins in your queries.

FYI, although there is no prohibition against a RowSource query containing a join, it would be extremely rare for this query to contain anything other than a single table.
 

Users who are viewing this thread

Top Bottom