Error inserting into linked table from VBA

[Chris Burnette]

I am having a problem inserting data into a table from a bound form in
Access. I am trying to insert the Primary Key of the Book table (BookID)
into the same field in the Author table, in which it is a Foreign Key. I do
this whenever a new record is created and data is entered for that record,
hence the AfterInsert event.

I have no problem accessing either the datasheet or the form for any of my
linked tables, however for some reason my code seems to be generating an ODBC
error.

My code looks like this:

Private Sub Form_AfterInsert()

Dim rst As DAO.Recordset

Set rst = CurrentDb().OpenRecordset("Author", dbOpenDynaset, dbSeeChanges)

rst.AddNew

rst!BookID = Me.BookID.Value
rst.Update
rst.Close
Set rst = Nothing

End Sub

The error looks like this:

ODBC--insert on a linked table 'Book' failed.

[Microsoft][ODBC SQL Server Driver][SQL Server]Explicit value must be
specified for identity column in table 'Book' either when IDENTITY_INSERT is
set to ON or when a replication user is inserting into a NOT FOR REPLICATION
identity column. (#545)


For some reason it seems to be trying to open the Book table even though I
specify Author as the record source in VBA. I'm not sure why it's doing
this, although I would speculate that it's because the form is bound to the
Book table.

So, the question is, is there a way to make it so that the PK of one table
is inserted into another as an FK? I have the relationships defined in
Access, so I don't think that's a problem.

If anyone could offer any suggestions about how best to do this, I would
appreciate it.

Thanks,

Chris

 

[Douglas J Steele]

From the error message, it would appear that your tables are actually in SQL
Server, not Jet. That means that relationships don't enter into the
equation.

I also question your tables. If BookID is a foreign key in the Author table,
that means that an author can only write one book. Even the other way around
(Author being a foreign key in Book) isn't correct, since a book can be
written by more than one author. Realistically, it should be a many-to-many
relationship between the two tables, which means implementing an
intersection entity (consisting of AuthorID and BookID) to allow you to
resolve the many-to-many.

Even if your tables are correct, you can't simply create a new record in
Author because you've got a new book. You need to retrieve the correct
Author from the table and update the FK in that row, or insert a new author
and provide the FK when you create the row.

 

[Larry Daugherty]

If Referential Integrity is on and the tables are in a relationship on
BookID then its value is already in the record.

As you say, your data is getting there alright. Just look in the
Author table after you've entered a new author and you'll see the PK
of the parent.


HTH

 

[Chris Burnette]

From the error message, it would appear that your tables are actually in SQL

Server, not Jet. That means that relationships don't enter into the
equation.

You are correct, the tables are in SQL Server.

I also question your tables. If BookID is a foreign key in the Author table,
that means that an author can only write one book. Even the other way around
(Author being a foreign key in Book) isn't correct, since a book can be
written by more than one author. Realistically, it should be a many-to-many
relationship between the two tables, which means implementing an
intersection entity (consisting of AuthorID and BookID) to allow you to
resolve the many-to-many.

There are actually 4 tables, which are Book, Author, CoAuthor, and
ThirdAuthor. I suppose I could do what you are suggesting and use a junction
table for the many-to-many relationship, although the way it's currently set
up it's a one-to-many relationship between Author and Book.

You are also correct that BookID is not the FK. Actually, AuthorID is the
FK in the Book table. The reason I said that BookID was the PK is because I
am using that field to navigate between forms in Access using the FindRecord
method, which is necessary because the tables all have a different # of
records.

Even if your tables are correct, you can't simply create a new record in
Author because you've got a new book. You need to retrieve the correct
Author from the table and update the FK in that row, or insert a new author
and provide the FK when you create the row.

Doug, could you please explain how to do this? I know how to update records
via Update query, but how do I retrieve the correct Author?

As for inserting a new author and providing the FK, that's what I was trying
to accomplish with my code. Perhaps I'm going about it incorrectly, but if
so could you provide a suggestion as to how to accomplish this?

Previously I was trying to do this using the Current event rather than the
AfterInsert event, and my code would run only when Me.NewRecord = True.
However I created an event cascade doing this, as every time a new record was
created in Book the code tried to create a new record in Author, which caused
the procedure to run again ad infinitum...

In any case, if you could please provide either a suggestion or a link to
another site or post where I might find this information, I would greatly
appreciate it.

Thanks,

Chris

 

[Douglas J. Steele]

There are actually 4 tables, which are Book, Author, CoAuthor, and
ThirdAuthor. I suppose I could do what you are suggesting and use a
junction
table for the many-to-many relationship, although the way it's currently
set
up it's a one-to-many relationship between Author and Book.

You are also correct that BookID is not the FK. Actually, AuthorID is the
FK in the Book table. The reason I said that BookID was the PK is because
I
am using that field to navigate between forms in Access using the
FindRecord
method, which is necessary because the tables all have a different # of
records.


Doug, could you please explain how to do this? I know how to update
records
via Update query, but how do I retrieve the correct Author?

One approach (probably not the fastest) would be to do a lookup on the
Author table using DLookup.

lngAuthorID = DLookup("AuthorID", "Author", "AuthorNM ='Christ Burnette'")

Once you have the ID, you can then use that value in your Update statement.

As for inserting a new author and providing the FK, that's what I was
trying
to accomplish with my code. Perhaps I'm going about it incorrectly, but
if
so could you provide a suggestion as to how to accomplish this?

As I suggested, you only want to add a new author if the author doesn't
already exist in the table. However, based on what you're saying above, does
BookID exist in the Author table? I don't see any logical reason for it to
be there.

Previously I was trying to do this using the Current event rather than the
AfterInsert event, and my code would run only when Me.NewRecord = True.
However I created an event cascade doing this, as every time a new record
was
created in Book the code tried to create a new record in Author, which
caused
the procedure to run again ad infinitum...

The Current event occurs when you move from record to record. That
definitely wouldn't sound like the correct event to use. Since the author
must exist before the book can be inserted (since AuthorID is required in
the Book record), you probably want to put code in the BeforeInsert, not the
AfterInsert.

 

[Chris Burnette]

Thanks for the information guys. I figured out how to get the data into the
FK table by using the OpenArgs property of the form. However, I still have a
minor issue with my DLookup function not quite working correctly.

The problem is that DLookup always returns the first record in the domain.
I want it to return the record in the domain where Book.AuthorID =
Author.AuthorID. I have tried changing AuthorID to "AuthorID = " &
Forms!AuthorForm!AuthorID, but when I run the procedure DLookup now seems to
return nothing, not even Null or an empty string.

My code is below:

Dim MyRecord As Variant
Dim AuthorID As Variant

AuthorID = Forms!AuthorForm!AuthorID

MyRecord = DLookup("AuthorID", "Book", AuthorID)

If MyRecord <> Null And MyRecord <> "" Then
MsgBox "MyRecord is " & MyRecord
End If

If someone could please help me get my code correct so that DLookup
actually returns the correct value, I would very much appreciate it.

Thanks,

Chris

 

[Chris Burnette]

Let's try this again:

I have tried changing AuthorID to "AuthorID = " & Forms!AuthorForm!AuthorID,
but this seems to return nothing, not even Null or an empty string.

Dim MyRecord As Variant
Dim AuthorID As Variant

AuthorID = Forms!AuthorForm!AuthorID

MyRecord = DLookup("AuthorID ", "Book", Forms!AuthorForm!AuthorID)

If MyRecord <> Null And MyRecord <> "" Then
MsgBox "MyRecord is " & MyRecord
End If