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

Appending Records to Table with Composite Key

P: 72
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:

Expand|Select|Wrap|Line Numbers
  1. Dim mySQL1 as String
  2.  
  3. DoCmd.RunSQL "INSERT INTO tblKeywords (Keyword) Values ('" & txtKeyword1.Value & "')"
  4.  
  5. mySQL1 = "INSERT INTO tblArticleKeyword (ArticleID, Keyword)"
  6. mySQL1 = mySQL + " Values ('" & [Form_frmLiteratureArticles - edit].ArticleID.Value & "', "
  7. mySQL1 = mySQL + "'" & txtKeyword1.Value & "')"
  8.  
  9. 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
Aug 10 '07 #1
Share this Question
Share on Google+
7 Replies


Rabbit
Expert Mod 10K+
P: 12,389
You haven't told us what your keys are, how you populate the keys, and how the table is related to other tables.
Aug 10 '07 #2

P: 72
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:

tblLiteratureArticles
ArticleID (PK)
Title
Author
etc...

tblKeywords
Keyword (PK)

tblArticleKeyword (Join table with composite key)
ArticleID (FK)
Keyword (FK)

The primary keys in tblLiteratureArticles 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 [frmLiteratureArticles - edit], is bound to the table tblLiteratureArticles. 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 tblLiteratureArticles.

In the AfterUpdate event, after the ArticleID field of tblLiteratureArticles 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 (cmdSaveKeywords). 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'.
Aug 10 '07 #3

Rabbit
Expert Mod 10K+
P: 12,389
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.
Aug 10 '07 #4

P: 72
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 [frmLiteratureArticles - edit]. If the Article ID does not exist in tblLiteratureArticles, where it's part of the primary key, then it can not be added to tblArticleKeyword, 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.
Aug 10 '07 #5

P: 72
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 [frmLiteratureArticles - edit]. If the Article ID does not exist in tblLiteratureArticles, where it's part of the primary key, then it can not be added to tblArticleKeyword, 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 tblLiteratureArticles. So, I inserted the following code into the AfterUpdate event in order to save the new record:

Expand|Select|Wrap|Line Numbers
  1. DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70
Now, the code works perfectly!

Thanks!
~mforema
Aug 10 '07 #6

Rabbit
Expert Mod 10K+
P: 12,389
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 tblLiteratureArticles. So, I inserted the following code into the AfterUpdate event in order to save the new record:

Expand|Select|Wrap|Line Numbers
  1. DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70
Now, the code works perfectly!

Thanks!
~mforema
I didn't do anything but you're welcome lol.
Aug 10 '07 #7

P: 72
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.
Aug 10 '07 #8

Post your reply

Sign in to post your reply or Sign up for a free account.