Appending Records to Table with Composite Key

[mforema]

Hi Everyone,

I am trying to write some VBA code that will append records to a table, but this table has a composite key. My initial code looks like this:

Dim mySQL1 as String

DoCmd.RunSQL "INSERT INTO tblKeywords (Keyword) Values ('" & txtKeyword1.Value & "')"

mySQL1 = "INSERT INTO tblArticleKeyword (ArticleID, Keyword)"
mySQL1 = mySQL + " Values ('" & [Form_frmLiteratureArticles - edit].ArticleID.Value & "', "
mySQL1 = mySQL + "'" & txtKeyword1.Value & "')"

DoCmd.RunSQL mySQL1

The first RunSQL works great! However, the second produces an error message, saying that the action query won't append the record(s) due to a problem with keys. Can anyone see if there's something wrong with my code, or am I attempting to do something that is impossible to do in the first place?

Thanks!
~mforema

[Rabbit]

You haven't told us what your keys are, how you populate the keys, and how the table is related to other tables.

[mforema]

You haven't told us what your keys are, how you populate the keys, and how the table is related to other tables.

I have three tables set up as follows:

tblLiteratureAr ticles
ArticleID (PK)
Title
Author
etc...

tblKeywords
Keyword (PK)

tblArticleKeywo rd (Join table with composite key)
ArticleID (FK)
Keyword (FK)

The primary keys in tblLiteratureAr ticles and tblKeywords are populated via VBA code, which works fine. My problem occurs when I try to populate the Join table.

To add more context...
I have two forms that are designed to allow users to add new articles to the database.

One form, called [frmLiteratureAr ticles - edit], is bound to the table tblLiteratureAr ticles. This form has code built into the AfterUpdate event of one of the controls; the code automatically creates a new Article ID, which is automatically appended to the table tblLiteratureAr ticles.

In the AfterUpdate event, after the ArticleID field of tblLiteratureAr ticles is populated, the code gives the command to open the second form, without closing the first one.

The second form (frmKeywords) is unbound, and it has 10 text boxes (txtKeyword1, txtKeyword2, txtKeyword3, etc.) and a command button (cmdSaveKeyword s). The text boxes are for users to input a maximum of 10 keywords, then they can click the command button to save the keywords. The code I posted originally was from the OnClick event code for the command button, cmdSaveKeywords . The first RunSQL populates the keywords table (of course, it only works for the first text box, but I was only testing the code at the time; later, I will add more code so that keywords from each text box will be added to tblKeywords).

My problem is occuring when I try to write code that will populate the Join Table. I want that code to also be in the OnClick event for cmdKeywords.

I hope this makes things clearer.

Thanks!
~mforema

p.s. the error message that pops up says, "Invalid SQL statement; expected 'DELETE', 'INSERT', 'PROCEDURE', 'SELECT', or 'UPDATE'.

[Rabbit]

I don't see anything wrong with the SQL statement. Have you tried it in a query?

The way your tables are set up right now there's no need for tblKeywords.

[mforema]

I don't see anything wrong with the SQL statement. Have you tried it in a query?

The way your tables are set up right now there's no need for tblKeywords.

I have tried the SQL in a query. I keep getting the same error message that says it will not append the table due to a problem with the keys. I think I may be running into problems with the AfterUpdate event. I want the code to get the current value from two text boxes - 1. txtKeyword1 from frmKeywords and 2. ArticleID from [frmLiteratureAr ticles - edit]. If the Article ID does not exist in tblLiteratureAr ticles, where it's part of the primary key, then it can not be added to tblArticleKeywo rd, where it's part of the foreign key.

I need the tables set up in a Many-to-Many relationship. Each article could have multiple keywords and each keyword could have multiple articles.

[mforema]

I have tried the SQL in a query. I keep getting the same error message that says it will not append the table due to a problem with the keys. I think I may be running into problems with the AfterUpdate event. I want the code to get the current value from two text boxes - 1. txtKeyword1 from frmKeywords and 2. ArticleID from [frmLiteratureAr ticles - edit]. If the Article ID does not exist in tblLiteratureAr ticles, where it's part of the primary key, then it can not be added to tblArticleKeywo rd, where it's part of the foreign key.

I need the tables set up in a Many-to-Many relationship. Each article could have multiple keywords and each keyword could have multiple articles.

Yep, that was it. The problem was the current value in the ArticleID textbox wasn't updated, which means the new Article ID didn't even exist in tblLiteratureAr ticles. So, I inserted the following code into the AfterUpdate event in order to save the new record:

DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70

Now, the code works perfectly!

Thanks!
~mforema

[Rabbit]

Yep, that was it. The problem was the current value in the ArticleID textbox wasn't updated, which means the new Article ID didn't even exist in tblLiteratureAr ticles. So, I inserted the following code into the AfterUpdate event in order to save the new record:

DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70

Now, the code works perfectly!

Thanks!
~mforema

I didn't do anything but you're welcome lol.

[mforema]

I didn't do anything but you're welcome lol.

LOL :) Sometimes I think better when I talk about problems with someone else; I guess it forces me to slow down and think through them more critically.