On-not-in-list and more

[Guest]

Hi
Got two questions, I hope someone can help me with
Got a main tabel with a "keyword"-field. Each record can have several
keywords and these keywords are stored in a related subtable.
This subtable is again related to a table containing a list of keywords. The
keyword table only has one column and the property limit-to-list is set to NO.
I have made a form containing fields from the main table and a subform where
the keywords can be chosen.
My questions are as follows:
1. I want the user to be able to enter a keyword not on the list AND at the
same time get the entry saved in the keyword-table. How do I do that?
2. How do I make a query giving me all the data from a record in the main
table plus all the related keywords from the subtable.

Thanks for you help!

 

[Allen Browne]

A1: Since you do not have a hidden bound column (i.e. the table of keywords
is just the keywords, not a hidden number field), you can set the
LimitToList property to Yes to the combo's NotInList event fires, and then
use that event to add the keyword to the lookup table.
Details on:
Adding values to lookup tables
at:
http://allenbrowne.com/ser-27.html

A2: Not sure I understood this Q.
Presumably you have 3 tables:
- your main table, with a MainID primary key;
- your Keyword table where the Keyword (text) is primary key);
- a junction table, with fields:
MainID relates to your main table's primary key
Keyword relates to an entry in your Keyword table.
If so, you just create a query based on the first and third tables.

If you wanted to generated a concatenated list of keywords, you need to
write a function to do that. Here's an example:
http://www.mvps.org/access/modules/mdl0004.htm