Multi-Select list box on a Data Entry Form

[Guest]

Hello,

I am wanting to place a multi-select list box on my data entry form that
allows users to select multiple options and those options will get added into
a record with comma delimited. Is this possible? If so, how should I go about
doing it.

I tried to create a multi-select box with the various options to choose
from, but when I choose the items it does not enter them in the table as a
record. Any assistance would be appreciated.



Thanks,
Brandon

 

[Douglas J. Steele]

It's possible (through the use of VBA code, since you cannot bind a field to
a multi-select list box), but it's a very bad idea, as it will be very
difficult to write queries to retrieve the information from that field.

You're far better off properly normalizing your tables, so that you've got a
parent table with the bulk of the information, linked to a child table with
one row for each selected option.

 

[Guest]

Thanks for your help. I am not for sure if that will work for what I am
wanting. The purpose of this multi-select box is so that they can have a
field that shows every state that is tied to that record. The only way that I
know how to do this would be to have it has a seperate field with the
abbreviated states listed as an individual record and commas seperating out
each abbreviation. This way, when I create a report for the data, all I have
to do is display that field and I don't have to have a query tied to it. Does
this better explain it?

Thanks,
Brandon

 

[Douglas J. Steele]

While it better explains what you're trying to do, it doesn't make it any
more acceptable from a database normalization perspective.

Realistically, you've got a classic many-to-many relationship between your
existing table and a State table, whether or not you actually have a State
table in your database. Each record can be associated with 1 or more states,
each state can be associated with 1 or more records.

Many-to-many relationships involve 3 tables: TableA, TableB and an
intersection table that contains foreign keys pointing to TableA and TableB.

Realistically, what you're trying to do is no different than the Orders
illustrated in the Northwind database that comes with Access. There, you
have an Orders table and a Products table. Since 1 order can contain
multiple products, and since 1 product can be contained on multiple orders,
it was necessary to introduce the Order Details table to handle the
relationship. The associated data is maintained through the Orders form,
which uses the form Orders Subform as a subform to populate the Order
Details table.

If you've got NY, MI, TX in a field on one record, and CA, WA, NJ on another
record, and ND, NY, NJ on a third record, write me a query that returns how
many records are linked to NY....

 

[Guest]

Ok,

I somewhat understand what you are saying, but if I do not have these
different states in a field then how can I get a report to show each record
that meets a certain criteria and when it displays that record then a box
with the record will show each state's abbreviation that it is tied to with a
comma seperating it?



Thanks,
Brandon

 

[Douglas J. Steele]

To concatenate all of the related states into a single field, you can look
at solutions like http://www.mvps.org/access/modules/mdl0004.htm or
http://www.mvps.org/access/modules/mdl0008.htm at "The Access Web"

 

[Guest]

Thanks Doug,

I believe I have it figured out. Now, do you think you could help me out
with a Write Conflict problem that I am having?

I have a data entry form that allows users to enter records into a table.
Additionally, I have multiple forms that the users can go to that displays
the data and they can also edit that data from those forms. However, I am
running into a problem where there are times that access gives me a write
conflict error message and asks me if I want to "save record", "copy to
clipboard", or "discard changes". Do you know how to fix this problem. There
are no other people edited the data besides one person.


Thanks,
Brandon

 

[Douglas J. Steele]

If you've got the same data displayed on more than 1 form, you're going to
run into that problem. No way around it that I'm aware of.

 

[Guest]

The same data is not on multiple forms. Each form that is used for edited has
different fields on each form. That is why I am confused. Do you know what
could be causing this problem?


Thanks

 

[Douglas J. Steele]

DIfferent fields of the same record or of a different record? Access works
on a row basis, so if you've got fields from the same record in two forms,
it doesn't matter if they're the same fields or not.

 

[Guest]

Ok,

I didn't know that access worked that way. I now know how to solve my
problem. I really appreciate all of your assistance.



Thanks,
Brandon