DAO.recordset and insert

[meme'1992]

Hello,
I have two table. A person (table1) make more exame (table2). This is a relation 1:n (table1:table2).

Table1: ID_1, name
Table2: ID_2, exame , ID_1

I create a mask in which i want to insert the data of table1 and table2. With a button I save them.

Table1: ID_1, NOME ---> id insert automatily, txt_nome
Table2: ID_2, dati, ID_1 ---> id insert automatily, txt_dati, i want the id of the table1

[FONT=Courier New]Private Sub btn_Salva()
  CurrentDb.Execute "INSERT INTO Tabella1 (NOME) VALUES (' " & Me.txt_nome & " ')    
  If Me.txt_dati <> "" Then  
       Dim id_r As DAO.Recordset
       Set id_r = CurrentDb.OpenRecordset(" SELECT  ID_1 FROM Tabella1 WHERE NOME = ' " & Me.txt_nome & " ' ")
        CurrentDb.Execute "INSERT INTO Tabella2 (dati) VALUES (id_r.Fields(0), ' " & Me.txt_dati & " ')
End if
End sub[/FONT]

The first 'insert' insert the data right, but the second doesn't work.
The error is THE function id_r.Fields doen't defined in the expression.
What's the problem?
Thanks

 

[Mark_]

You insert a record into Tabella1.
Where do you load the value for Tabella1 ID_1?

If you had this set up as a normal form/subform and you had your tables linked you would not need to deal with filling in the ID on the child. As you are NOT doing it this way you need to reload the record you just added to get the current value for ID_1.

 

[Pat Hartman]

Access is a RAD tool. If you are not going to use its RAD features such as bound forms, you end up with none of the benefits and all of the garbage.

If the BE is Jet or ACE, you are going to have to switch to using DAO to insert the record. When you do that, you can reference the autonumber as soon as the .AddNew runs so put the code to save the ID somewhere between .AddNew and .Update

If the table is SQL Server, then either a query or DAO/ADO works because you need to use @@Identity or Scope_Identity to retrieve the last inserted identity column.

 

[Cronk]

The second insert fails because 2 values are trying to be inserted into one field.