-----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:[color=blue]
> 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?[/color]
