List box storing multiple values in field

[Guest]

I have two tables, one has one field called Counties. On my form i have based
my list box upon this field so it displays all the counties. My other table
with a field called counties served. What i want to be able to do is, on my
form, i can select multiple values from my list box, which will then populate
my Counties Served field with all these values, with each value seperated by
a comma (,).

List box name - List2
Where data for list box come from - Counties (table Counties)
Where i want the data values to be stored - Test (table Table1)

If this can be done using a combo box, then i dont mind doing that either. I
just need this feature or some way of the user being able to select multiple
counties for one record. I have no knowledge of access, so can someone please
provide the code and information for it. Thankyou

 

[Albert D. Kallal]

You don't need to write one line of code.

You have your main form. (you not mentioned what table this is based on).

You then have a child table related to the main record, and this is your
countries served.

make the counties served a sub-form. The column in the sub-form will be a
combo box that lets the user select a country.

If you use the above then zero lines of code need be written....

my Counties Served field with all these values, with each value separated
by
a comma (,).

NO NO NO! do not try and stuff multiple values into a field. You can't sort
that field, you can not correctly search on that field. You can not do
counts of many countries etc are in use. This list of things you can't do
goes on for may many pages. You effect destroy all ability to report on
those values.

List box name - List2
Where data for list box come from - Counties (table Counties)
Where i want the data values to be stored - Test (table Table1)

Hum, you lost me.

What form/table will have the main record, and then you attached child
records (countries served I belie). The countries table is really only a
table of convene for the user so they don't actually have to type in he
country name into he countries served table.

So, what table are you going to attached the countries served table to?

The way this works is you going to find, or display, or bring up a main
record. Lets assume it is the customer record. Then, attached to this
customer is going to be a list of countries they serve (that going to be our
child table, and it currently called countries served). We then of course
have our "pick list" of countries (note that the countries table is not
really a relational table, but only a pick list table to save us the pain of
having to type in the country name). So, this system could in fact function
without the countires talbe (so, don't confues a pick list of values with
that of a related child table with "many" reocords that belong to one
reocrd.

 

[Guest]

ok, let me try and explain what i want to do, and then you can recommend what
is the best option. I have a cab service, and certain cab companies serve
different counties around the country. So one cab service could serve 4
counties and another serve 8. Now i have a list of these counties and they
are stored in a one field table called Counties.

I have another table that stores all the cab companys details, and in this
table i have a field called 'Counties Served' where it will list all the
counties that they serve within the country.

Now what i want to be able to do, is display the counties that are availble
using my list on the one field table, and allow the user to select the
counties they serve and it be stored somehow on the database. Do you
understand what im trying to do?

 

[Albert D. Kallal]

I have another table that stores all the cab companys details, and in this

table i have a field called 'Counties Served' where it will list all the
counties that they serve within the country.

Well, we just agreed that were not going to "stuff" a bunch of data into one
field, as that don't work well. Also, if you planned to do the above, then
why do you have a table called counties served then? for what purpose would
it be?

Now what i want to be able to do, is display the counties that are
availble
using my list on the one field table, and allow the user to select the
counties they serve and it be stored somehow on the database. Do you
understand what im trying to do?

Yes, and as I said, not one line of code need be written here. I also
pointed out that stuffing multiple values into one field don't work.

So, we have the main record is Cab Companies. and, we have a table called
countries Served. The structure of countries served will be:


id (autonumber) CabCo_id country


The id is a autonumber field that we likely not to use at this time, but it
good practice for each table to have primary key. If you wish, you can use
Country field as the primary key. So, you might use:

Country CabCo_id

As I said, you don't actually need the autonumber ID, but if you have one,
not a big deal.

The MOST important field in this countries served table is the field used to
RELATE the record to our cabCo table, and that field is the Cabco_id. Thus,
CabCo_id is a standard long number field. And our last field is the country.

So, we simply have a table called countries served, and this is where we
will store the "list" of counties that a particular CabCo has.

As mentioned, we are NOT going to store the list in a single field, as that
can't be searched correctly, or sorted, or anything. (and, further, the code
to "edit" such data in a field is VERY difficult. Not only would our code
have to pull values out of a lsitbox, but what happens when you go back to
edit the record, you have to pull the values from that one field with commas
and somehow re-populate the listbox. this type of code is VERY difficult to
write, and further as mentioned, stuffing a bunch of values into one field
is hard to work with (why not then use one record for the while database,
and just type in each cab co separated by a comma into one record? You can
begin to see how incredible silly this type of design would be. So, DO NOT
store multiple values into that one field, as it nearly impossible to work
with.

Ok, lets get back to our problem:

You have the main form called CabCo, based on our table of CabCompany. You
then build the above table called CounteriesServed. You will then draw a
relational join from the CabCo primary key (hopefully you been using "id"
for all tables). to the above field called CabCo_id.

You then use the wizard to build a sub-form based on the above table. This
sub-form is to then be inserted into the CabCo form. At this point then you
can add a new record to the sub-form for each country served. That means we
will not be able to use a listbox. However, each line of the sub-form where
we add an additional country served we can use the combo box.

All of the above can be done with wizards, and no code.

It would be nice to use a listbox, but unfortunately that takes advanced
coding to make run. So, the approach will use a sub-form,a nd you simply
enter the country for each new row (but, you make that control a combo box
for ease of data entry).

So, we have

tblCabCo - our "main" table of cab informaton.

tblCountriesServed - our "list" of countiers served for each tblCabCo
record

tblCountires - our nice list of countires that we use for the combo
box in tblCountiresServed to make data entery easy. As mentoend, there is
not really a related tabe, but only a table used to make data entery more
easy when editing a reocrd in tblCountiresServed.