How to solve this error?

[ai3rules]

I have 2 tables in my database. Machines and Users. My goal is to be able to update both tables, which are related, from one form.

I created 2 seperate INSERT INTO commands, one for each table. The machines table alone works, but when I try either the users table alone or along with the machines table I get this error:

Error Type:
Microsoft OLE DB Provider for ODBC Drivers (0x80040E14)
[Microsoft][ODBC Microsoft Access Driver] You cannot add or change a record because a related record is required in table 'Machines'.
/Testing/Add3.asp, line 36

If anyone can tell me what I need to do to make this work I would really appreciate it.

Thank you.

[russell]

The error means you have a Foreign Key defined in the database, and your attempted insert violates that key.

One of the fields in Users is mapped to a field in Machines. The value you insert in that field (in table users) must exist in the foreign key field in Machines.

If you don't know which fields are involved, in Access go to Tools, then click Relationships.

The purpose of Foreign Keys is to preserve data integrity. Example: lets say I have 2 tables Players and Scores defined like:

Create Table Players (
id int not null,
name varchar(32) not null
)
Create Table Scores (
playerId int not null,
week int not null
)
I would want to ensure that bad data can't get in my scores table by putting a foreign key from Scores.PlayerId to Players.Id. Once I did so, I cannot insert any playerId in Scores that doesn't exist in Players. You have this situation between MAchines and Users.

[ai3rules]

I understand now what the problem is. As you said the relationship is preventing the creation of an independent record in the Users table. It has to be created along with the record created in the machines table.

How can I do that using the INSERT INTO command? I want to take user info from a form and insert it into both the Machines and Users table as one record so that I don't run into that relationship error.

Again, any help is appreciated,

Thank you.

[russell]

run two seperate querries. 1st one into machines, 2nd into users

[ai3rules]

By queries you mean the Insert command twice, like I originally did?

[russell]

yep.

[ai3rules]

But that's what gave me the error??

I even tried inserting something into the Users table alone, and got the same erro

[russell]

insert into machines
then users
in seperate commands

ex:

sql = "INSERT Into MAchines..."
db.Execute(sql)

sql = "INSERT INTO Users..."
db.Execute(sql)

[ai3rules]

Ok, I tried something like that but I think I might have done it wrong? I will attach the page. I made it with dreamweaver and the connection was made using their wizard (dsn) so I might have confused the method you gave me.

I would appreciate if you could take a look at my page and see what I am missing.

Thank you.