How do I do an Insert like this one....

[Rich25]

I have an input form with mostly text boxes. When the user presses submit, most of the fields are inserted into Table A, for example. Some fields are inserted need to be inserted into Table B. Fields cannot be inserted into Table B if Table A has no records (is at eof). I'm having a problem with this because when both tables are empty, I want to insert into table A, then immediately use table A's id field as a foreign key in Table B, but it isn't working. Hope this wasn't too hard to understand. Please help if you can.

Thanks

Rich

 

[Ovatvvon]

So if I understand it right, you want to append a record to a table (whether the table is empty or not) and take the new primary key (identifier) and use it as the foreign key (relator) in the other table, but do this in all one sweep? If this is the case, you can make an active connection to the database with a server-side cursor and use the addnew method to add a new record, then set the pk to a variable, then use it for the fk in the other table with you use the update method for it.

dim myPK

rs.Open "myTable", conn, keyset, optimistic, adCmdTable
rs.AddNew
rs("userID&quot = request.form("userid&quot
rs.Update
myPK = rs("userPK&quot
rs.Close

sql = "INSERT INTO * (userFK, id) VALUES (" & myPK & ", '" & userID & ""
conn.Execute
conn.Close
...


this isn't literal code, but you should recognize that. But should give you a good idea of how to do it. -Ovatvvon :-Q

 

[jfrost10]

It may be more of a programming issue than an Insert query issue. Tell me if I'm off here: the fields that go in table A are the parent informatin, and the fields in table B are the child information. So then in code, what you need is two objects: a parent and a child. The child will have the parents key as one of its properties (so it can be associated with it). What I would do is set up a save sub in both objects. Fill both object properties with the field values, save the parent object (table A), then save the chile (table B).

That way, you cover validation on the form, and you don't need a super complicated insert statement. If none of this made sense, let me know


Jack

 

[Rich25]

I figured out what I had to do. I probably just didn't explain the problem well enough. All I had to do was open a new recordset after I inserted or updated Table A, then base Table B on the id field of Table A, including the new id that was inserted. That still may not be understandable, but at least I can understand it, if not explain it too.

Rich