Combo Box Data

[Srowe]

I have a combo box on a form That lists subjects last name. When I pick the
subject I want it autopopulates the entire subject record (This is a good
thing) The problem I am having is that everytime I entire the same subject by
way of the combo box into a new record it saves the subject again in the
database. So if I entire a subject into a record 100 times It will list his
name in the combo box a 100 times and continues to grow.

Is there anyway of limiting a entry to only being in the database once and
use that same record over and over?

Hope this is clear enough.

Thanks

Scott

 

[Wayne-I-M]

Hi

It sounds to be - may be wrong - like you have not set up the tables right.
In your subject table should be only the data that relates directly to that
subject. Nothing else except an ID field or some other unique identifier.

You need to create other tables that are linked to this table.

You combo box will only each subject once if they are only in the table once
- as they should be.

If your main form is based on a table with the subject ID as the related
table (child) hen each time you click the combo to go to the record you are
in effect going to the child table in a new record on the main form -
createing a new subject in the table with the same name but a different ID.

Base the combo directly on a query based on the subject table (use the
wizard for this if needed) to get round this problem

Good luck

 

[Dirk Goldgar]

I have a combo box on a form That lists subjects last name. When I pick the
subject I want it autopopulates the entire subject record (This is a good
thing) The problem I am having is that everytime I entire the same subject
by
way of the combo box into a new record it saves the subject again in the
database. So if I entire a subject into a record 100 times It will list
his
name in the combo box a 100 times and continues to grow.

Is there anyway of limiting a entry to only being in the database once and
use that same record over and over?

It sounds as though the combo box's row source (where it gets the data to
list) is the same table that is the form's record source (where the form
draws its data from). Is that right? As setup like that is simple, but
essentially conflates two things -- the subject, and whatever it is that the
form is recording about the subject. It's a flawed design, though it may be
good enough if your database is (and remains) extremely simple.

The simple answer to your question, without changing your table design, is
to set the combo box's row source query to use the DISTINCT keyword. If
looked at in SQL View, it would say "SELECT DISTINCT SubjectName ...", or
something along those lines. If you edit it in the visual query designer,
you'd set the query's Unique Values property to "Yes" -- that accomplished
the same thing.

A better overall design is to have a table for subjects -- one record per
subject -- and another table for ... whatever you are recording about
subjects, with multiple records per subject. Then the combo box's row
source would be the Subjects table, while the form would be based on the
other table. Those two tables would be in what's called a "one-to-many"
relationship.

That design, while better in the long run, does introduce some
complications, as then you need to provide a way to add a new subject, and
you need to ensure that you can't accidentally add a record to the "many"
table without adding a record to the "one" table.

 

[Srowe]

Dirk,

You are right on all accounts in regards to the form and the combo box being
tied to the same table. I have tried what you suggested but it still did not
correct my issue. My database is fairly simple but we are slowly outgrowing
it and I'm trying to update it before it becomes too obsolete.

Any ideas would be greatly appreciated.

Thanks,

Scott

 

[Dirk Goldgar]

Dirk,

You are right on all accounts in regards to the form and the combo box
being
tied to the same table. I have tried what you suggested but it still did
not
correct my issue. My database is fairly simple but we are slowly
outgrowing
it and I'm trying to update it before it becomes too obsolete.

Scott -

What exactly did you try? Could you post the SQL of the combo box's
RowSource, and the table or SQL of the form's RecordSource?

 

[Srowe]

Dirk,

Thanks. I played around a bit more with your idea and it worked. Seems as
though I didn't put the "DISTINCT" in all the right places.

Thanks for your help.

Scott

 

[Srowe]

Wayne,

You as well are right. I'm really new at this so changing the table design
would be quite the task for me. Unfortuneatly I wasn't the designer of the
database.

I was able to get it to work. Using the "SELECT DISTINCT" idea.

Thanks,

Scott