how to store listbox (multiple) values?

[AniG]

Hi.
I have 3 tables.
1- tbl-customer contains(customer-id, customer-name)
2- tbl-serials contains(serial-id, serial-description)
3- tbl-customerSerial contains(Customer-id,serial-id)

I have created a form that contains the customer info, and
I would like to add a listbox that outputs all the
available (predefined values) serial descriptions for that
particular customer. now, a customer can have multiple
serial numbers. I would like to store the values chosen in
the listbox in the tbl-customerSerial table.

I have searched online for references about this but
unfortunatly couldn't find anything .

Does anyone know how is this possible?
I appreciate all the help.
Thank you in advance.

 

[Gerald Stanley]

Instead of placing a multi-select listbox on the Customer
Form, create a new form CustomerSerial whose controlSource
is tbl-customerSerial with a ComboBox whose RowSource is
the serial-description, serial-id and whose BoundCoulmn is 2.
Then incorporate the CustomerSerial form on the Customer
Form as a subForm with the customerId as the link between
the two.

Hope This Helps
Gerald Stanley MCSD

 

[AniG]

Yes, but a client may have MULTIPLE serial numbers. And I
want to be able to view those serial numbers in the
customer form and be able to select and de-select the
serial numbers for each customer.

I am having difficulties in inserting the multiple values
into the tbl-customerSerial table.

Is there another way to do it ?
Thanks again.

 

[Rick Brandt]

Yes, but a client may have MULTIPLE serial numbers. And I
want to be able to view those serial numbers in the
customer form and be able to select and de-select the
serial numbers for each customer.

I am having difficulties in inserting the multiple values
into the tbl-customerSerial table.

Is there another way to do it ?

Yes, use a relational database as it's intended to be used. If (1) Client
can have (many) serial numbers then that is a 1-to-many relationship that
should be implemented as TWO tables "related" to each other on the Client's
Primary Key field(s).

Then you use a Client main form with an embedded Serial Number subform
which would allow you to view/enter/edit as many serial number entries as
required per Client without stuffing multiple values into a single field.

 

[Gerald Stanley]

It is considered rude to type in CAPITAL letters as it is
taken to be shouting. The solution I have outlined allows
for multiple serial numbers per customer and is the classic
solution to the maintenance of one-to-many relationship data.

Gerald Stanley MCSD

 

[Guest]

Sorry about the capital letters. just wanted to emphasise.
Ok...I'll try the combo boxes and see what gives.

Thanks.

 

[AniG]

Hi again,

I have implemented the combo box method as you guys have
described but the values are not stored in the tbl-
customerSerial table.

I do see the serial-description values in the combo box
but when I save the customer info the values are not
stored in the tbl-customerserial table.

What am I missing ????


thanks

 

[AniG]

Rather it gives me an error message indicating the
following:

"you can not add or change a record because a related
record is required in tbl-customer"

Have I linked something wrongly?

thanks

 

[tina]

ani, i don't think a combo box is the way to go here. go back and read Rick
Brandt's post again - he doesn't advocate a combo box or list box. instead
he describes the correct table setup and consequently the easiest solution
to your problem.
good luck.

 

[AniG]

OK!
Got it working with combo boxes.
but I would of liked to use listboxes instead.
For now, i guess combo boxes will do.

Thank you guys for all your help.
Appreciate it.