By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,676 Members | 1,636 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,676 IT Pros & Developers. It's quick & easy.

Update record including data from a multi-select list box

P: n/a
Hi All

I have two tables: tblRecords and tblOptions.

tblRecords consists of RecordID (autonumber), Record (text) and Option
(text record of options chosen) fields

tbloptions consists of OptionID (autonumber) and Option (text) fields.

I want to use a form to add/update the records table.

The Record field is general text but the Option field should record
data from a Multi-Select listbox. the listbox obtains its' selections
from a table which is updated by the user using another form.

I don't know where to start?

Thanks in advance for any help
Suffrinmick
Nov 13 '05 #1
Share this Question
Share on Google+
1 Reply


P: n/a
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Your tblRecords is not Normalized. You can't have multiple data items
in one cell, that violates the First Normal Form (1NF). What you need
is another table that will hold the options selected for each "Record,"
the table RecordOptions, below.

JET SQL:

CREATE TABLE Options (
OptionID COUNTER ,
OptionName TEXT(255) PRIMARY KEY
)

CREATE UNIQUE INDEX udx_OptionID ON Options (OptionID)

CREATE TABLE Records (
RecordID COUNTER ,
Record TEXT (50)
)

CREATE UNIQUE INDEX udx_RecordID ON Records (RecordID)

CREATE TABLE RecordOptions (
RecordID integer FOREIGN KEY REFERENCES Records,
OptionID integer FOREIGN KEY REFERENCES Options ,
CONSTRAINT PK_RecordOptions PRIMARY KEY (RecordID, OptionID)
)

When the Records row is saved, the values in the ListBox will be read
and saved to the RecordOptions table.

It would be a better idea to use a subform for the Options. Use the
RecordOptions table as the subform's source. Link to the master form by
the RecordID. Use a ComboBox for selection of each OptionID.

Read the Access Help articles on using forms & subforms.

--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQdwanYechKqOuFEgEQKD/ACfUiAfub320u5hITQ/lSigrxT+mhQAoLgK
kWUGSXYvkwlA2uZT9TYgcbl/
=CANG
-----END PGP SIGNATURE-----
SuffrinMick wrote:
Hi All

I have two tables: tblRecords and tblOptions.

tblRecords consists of RecordID (autonumber), Record (text) and Option
(text record of options chosen) fields

tbloptions consists of OptionID (autonumber) and Option (text) fields.

I want to use a form to add/update the records table.

The Record field is general text but the Option field should record
data from a Multi-Select listbox. the listbox obtains its' selections
from a table which is updated by the user using another form.

I don't know where to start?

Nov 13 '05 #2

This discussion thread is closed

Replies have been disabled for this discussion.