Difficulty with List Selection and New Records

[Guest]

I'm creating a database for work, and I have NO EXPERIENCE with Access, other
than the file I'm creating now. So please keep this in mind when answering
my simple question.

I need to create a list that allows multiple selections. I have
successfully created lists that allow multiple selections. However, when I
create a new record in Form View, the same selections from the previous
record are maintained. When you change one selection, it changes them
throughout the entire database. Also, when the database is saved and closed,
it does not keep the selections.

Help!

Thanks,
Whitney C.

 

[Douglas J. Steele]

That's the way multi-select list boxes work on continuous forms.

You cannot bind a multi-select list box to a field in the recordset. This is
because a field can only hold a single value, whereas a multi-select list
box represents multiple values. What that means is that while it may look as
though you have multiple list boxes on the form, in actual fact you have a
single list box, repeated multiple times, so that, as you're seeing,
changing one of the list boxes changes them all (since they're all the same
list box)

What are you hoping to do with the list box?

 

[Guest]

Thank you for the information. What you're saying makes sense.

I'm needing to select several different names from the list. Each record
will have several names associated with it. Not all records will have the
same combination of names. But in the end, I need to be able to search the
fields for the different names. Does this make sense?

Whitney

 

[Douglas J. Steele]

Sorry, I'm having problems understanding what you're doing.

What's the list? Why are you selecting multiple names from it? Are you
hoping that if you select multiple names, you'll be able to store multiple
names in the record? You can't: Access doesn't work that way.

 

[Guest]

This is what I'm trying to do:

I have a list of inventors who have created a specific patent. I need to
select each name that is associated with a patent. Then I will need to be
able to search a single field/list/whatever it's called for their name. Is
this possible?

If not, will it be possible to create an object that you enter a name into,
and then another object "pop up" for another name entry?

Thank you so much for your help!

 

[Douglas J. Steele]

What you've got is a classic many-to-many relationship. You've obviously got
Patents and you've got Inventors, but one Patent can be assigned to multiple
Inventors, and one Inventor can hold multiple Patents, so the correct way to
handle it is to create a third table to resolve the intersection of the
other two tables.

What you typically do to maintain the information is use a form with a
subform on it.

Take a look at the Northwind database that came with your version of Access.
You've got the same many-to-many relationship between Products and Orders
(one product can appear on multiple orders, one order can contain multiple
products), so the Order Details table was introduced to resolve the
many-to-many. The Orders form includes the Orders Subform form as a subform
to allow you to maintain the data.

 

[Guest]

Great! Thank you for the information! I will definately try what you've
recommended! If I have any other questions, I will let you know!

Thanks again!,
Whitney