Problem with data entry on subform

[Mike Webb]

Using Access 2K2.

I have 3 tables: tblArticles (news articles and web pages of interest to our
organization), tblKeywords (locally assigned keywords that best describe the
content of the 1st table which I can use to query), tblJoin (joins the first
2 tables in 1-to-many relationships from them to this one.

Tables work fine.

Built data entry form (my first try) with tblArticles as main form and
tblKeywords as the subform. The record source for the subform is:
SELECT tblKeywords.Keyword_ID AS tblKeywords_Keyword_ID,
tblKeywords.Keyword, tblJoin.Join_ID, tblJoin.Article_ID, tblJoin.Keyword_ID
AS tblJoin_Keyword_ID FROM tblKeywords INNER JOIN tblJoin ON
tblKeywords.Keyword_ID=tblJoin.Keyword_ID;

The subform has a combo box to choose either an existing keyword or the user
can type one in.

The Problem: Whether I choose one or type one in, I get a new record in
tblKeywords (and it is linked up to the article in tblArticles). For
example, I now have several Keyword entries of "water rights" and "Platte".
It should have just a single record for each.
The row source for the combo box is: SELECT tblKeywords.Keyword
FROM tblKeywords;

What did I miss??

TIA,
Mike
Platte River Whooping Crane Maintenance Trust, Inc.
a non-profit 501(c)(3) organization

 

[Allen Browne]

The subform should be based on the junction table, not the keyword table.
You want to add new entries to the junction table, not new keywords each
time you make an entry.

 

[Mike Webb]

Oh. Didn't know that. I had used the wizard to set up the form and subform
and must have done something wrong in there.

I'll change the SQL in the record source and give it a try. Thanks.

 

[Mike Webb]

Okay, got an error when I tried to change the subform basis from the keyword
table to the join table.

I have a novice's grasp of SQL coding and tried to just reverse the table
and field references from what I posted below in the original post. My
record source now reads:

SELECT tblJoin.Join_ID AS tblJoin_Join_ID, tblKeywords.Keyword,
tblKeywords.Keyword_ID, tblJoin.Article_ID, tblKeywords.Keyword_ID AS
tblKeywords_Keyword_ID FROM tblJoin RIGHT JOIN tblKeywords ON
tblJoin.Keyword_ID=tblKeywords.Keyword_ID;

However, I got an error on my first test of the form. I tabbed through the
main form and tried to select a keyword from the combo box -- got the
following error: "Cannot add record(s); join key of table 'tblJoin' not in
record set."

I don't know how to proceed. The join key is in the SQL statement (Join_ID
for tblJoin and Keyword_ID for tblKeywords). I'm sure the answer is obvious
to many of the readers of this newsgroup. Would someone please let me know
what it is?

Thanks!
Mike

 

[Allen Browne]

Mike, just base the subform directly on your join table. The
LinkMasterFields and LinkChildFields properties of the subform control will
take care of showing the right keywords for the article automatically.

In the subform, use a combo box for the keywords. This will help the user by
auto-completing words. If you set the combo's LimitToList to Yes, you can
use its NotInList event to add the keywords to tblKeywords (after
confirmation if desired) as explained in this link:
http://allenbrowne.com/ser-27.html

 

[Mike Webb]

Thanks! I'll give it a try.

Mike, just base the subform directly on your join table. The
LinkMasterFields and LinkChildFields properties of the subform control will
take care of showing the right keywords for the article automatically.

In the subform, use a combo box for the keywords. This will help the user by
auto-completing words. If you set the combo's LimitToList to Yes, you can
use its NotInList event to add the keywords to tblKeywords (after
confirmation if desired) as explained in this link:
http://allenbrowne.com/ser-27.html

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

not interest record

 

[Mike Webb]

I'm sorry for my ignorance, but I got lost trying to make the changes you
suggested.

I re-wrote the SQL for the subform's record source to: SELECT
tblJoin.Join_ID, tblJoin.Keyword_ID FROM tblJoin; This changes the text
box for the subform Keyword_ID from and autonumber to a blank field. I also
cannot select or type in a keyword; just hear a little "beep".

I next tried to change the Master and Child fields. I put the Master field
as Article_ID, but Access wouldn't let me make the Child field be Keyword_ID
because the data type was binary. I rechecked the tables and verified that
it is an autonumber and Long Integer in all tables. Have no idea why this
dialog box lists it as binary.

I then stepped back, took a breath and then started over with a new
form-subform using the Wizard. This time I used just the Articles table and
the Join table. Set up just fine, but .... no keywords, just the
Keyword_ID. I se the Master field as Article_ID and the Child field as
Keyword_ID. The problem is the subform only shows the 3 fields from
tblJoin - Join_ID (PK), Article_ID (FK), and Keyword_ID (FK).

I didn't try to do anything with the combo box as you suggested as I didn't
want to compound the problem.

Can you, or someone, lead me by the hand to a solution -- or to a place I
can figure it out?

TIA!
Mike

 

[Allen Browne]

tblArticle:
Article_ID Autonumber primary key
...

tblKeyword:
Keyword Text (20) primary key

tblArticleKeyword:
ArticleKeyword AutoNumber primary key
Article_ID Number (long) foreign key to
tblArticle.Article_ID
Keyword Text (20) foreign key to
tblKeyword.Keyword

Main form:
RecordSource: tblArticle

Subform:
RecordSource: tblArticleKeyword.
LinkMasterFields: Article_ID
LinkChildFields: Article_ID

Subform contains just a combo box:
ControlSource: Keyword
RowSource tblKeyword

When you open the main form and move to an article, the subform contains
only those keywords associated with that article in tblArticleKeyword. User
can associate more keywords with that article by selecting them in the
combo, one per row.

 

[Mike Webb]

Thanks! I'll give it a try.

Mike

tblArticle:
Article_ID Autonumber primary key
...

tblKeyword:
Keyword Text (20) primary key

tblArticleKeyword:
ArticleKeyword AutoNumber primary key
Article_ID Number (long) foreign key to
tblArticle.Article_ID
Keyword Text (20) foreign key to
tblKeyword.Keyword

Main form:
RecordSource: tblArticle

Subform:
RecordSource: tblArticleKeyword.
LinkMasterFields: Article_ID
LinkChildFields: Article_ID

Subform contains just a combo box:
ControlSource: Keyword
RowSource tblKeyword

When you open the main form and move to an article, the subform contains
only those keywords associated with that article in tblArticleKeyword. User
can associate more keywords with that article by selecting them in the
combo, one per row.

 

[Mike Webb]

This is frustrating! The suggestions below work, but not the way they
should.

I removed the relationships, made all the changes you suggested (the only
differences are "Text(20)" is "Text(35)" in my tables, and
"tblArticlesKeyword" is "tblJoin" in my tables).

I created a one-to-many relationship from tblArticles to tblJoin, but could
not from tblKeywords to tblJoin. It is a one-to-one in the dialog box and I
can't change it. For both relationships I chose "Only include rows where
the joined fields from both tables are equal." I am also unable to check
the referential integrity boxes. I get an error: "...Microsoft Access can't
create this relationship and enforce referential integrity..... Data in the
table 'tblJoin' violates referential integrity rules. ...."
I printed the Documenter pages for tblJoin and TblKeywords and compared the
properties for "Keyword" line by line - they are the same. I don't know
where the error is coming from.

I left the referential integrity boxes for the relationship from tblKeywords
to tblJoin unchecked. I then opened my data entry form and entered some
dummy data. Data entry works fine - from the form and subform side. I went
to check the tables and found a surprise: the new keywords show up in
tblJoin - but NOT tbl Keywords. That table is blank. In tblArticles I
click the '+' and see Join_ID and Keyword.

I've tried changing little things here and there and can't seem to change it
in a manner that 'fixes' the actions.

What am I missing????

Cordially,
Mike

 

[Allen Browne]

Hi Mike.

The one-to-one is because you have a unique index on teh Keyword field in
tblJoin.

The subform is - quite correctly - adding records to the join table. If you
want them added to the lookup table as well, set the combo's LimitToList to
Yes, and use its NotInList event to write them to tblKeyword. There is an
example of how to code that at:
http://allenbrowne.com/ser-27.html

Because you already have keywords in tblJoin that are not in tblKeyword:
1. Create a query into tblJoin.
2. Drag the Keyword field into the grid.
3. In the Properties of the query (View menu), set Unique Values to Yes.
4. Change it to an Append query (Append on Query menu).
5. Run the query.
For the keywords that already exist in tblKeyword, you will get a message
about "key violations", but go ahead and answer Yes to append the others
anyway.

Text(35) is fine, as long as it is the same in both tblKeyword and tblJoin.

After that you should be able to create the one-to-many relation with
referential integrity.