Solved: How to put data in two tables from one form

[daffelito]

Hi, I have this code on a button so that i in a single push of a button enters data into two tables that are in relation to eachother.
But i dont get it to work.
The tables looks like this:

tblPumpsort
id - counter (primary key and parent to table 2)
<more stuff>

tblPumpsortEl
id - counter (primary key)
ProduktID (child to id in tblPumpsort)
El

then i have some code to the addreccord button that looks like this:


Private Sub laggtillpump_Click()
On Error GoTo Err_laggtillpump_Click

DoCmd.GoToRecord , , acNewRec
Exit_laggtillpump_Click:
Exit Sub
Err_laggtillpump_Click:
MsgBox Err.Description
Resume Exit_laggtillpump_Click
Dim db As DAO.Database
Dim rst As DAO.Recordset
Dim varItem As Variant
Dim strSQL As String

'--- open the table
Set rst = CurrentDb.OpenRecordset("tblPumpsortEl")
'--- loop through all selected items in the list box
' adding data to the table
For Each varItem In Listruta43.ItemsSelected
rst.AddNew
rst!ProduktID = Me.id
rst!El = Listruta43(0, varItem)
rst.Update
Next varItem
'--- close the table
rst.Close
Set rst = Nothing

End Sub


But still it only puts data into tblPumpsort, nothing in to tblPumpsortEl:eek:

[OBP]

daffelito, as you are only opening one recordset for one table it won't pit any data in a second table.
You need to open a second Recordset for it to do that.
But why are you using VBA to do what Access does for you?

[daffelito]

I dont know exactly how you mean. How is Access able to make it for me with one single push of a button?

[OBP]

Access doesn't use a button, it saves the data to the table automatically when you use Bound Forms.

[daffelito]

Ok, but now i have this button that have an addrecord function to it, and i tried with a subform because you can not bound two tables to one form, but it doesent matter, i dont get the results i want anyway.
So i need some kind of vba code maby with some sql statements to do what i want.

[OBP]

Why didn't a MainForm and SubForm do what you want?
It is the normal way to add data to 2 tables.

[daffelito]

Yes i know that thats the normal way, but in my case i also need to add the id (autonumber) from table1 into the column ProductID in table2.

The products i enter data about can be run on different voltage, therefor i have to normalize my database.
If i use a subform it wont take the id from table 1 and put that in table 2 without some sort of code.

[daffelito]

I solved it. All i needed was to read the code again and change a few things so they got in the right line.
THe right code looks like this.


Private Sub Kommandoknapp55_Click()
'---On Error GoTo Err_Kommandoknapp55_Click
Dim db As DAO.Database
Dim rst As DAO.Recordset
Dim varItem As Variant
Dim strSQL As String

'--- open the table
Set rst = CurrentDb.OpenRecordset("tblPumpsortEl")
'--- loop through all selected items in the list box
' adding data to the table
For Each varItem In Listruta53.ItemsSelected
rst.AddNew
rst!ProduktID = Me.id
rst!El = CStr(Listruta53.Column(0, varItem))
rst.Update
Next varItem
'--- close the table
rst.Close
Set rst = Nothing
DoCmd.GoToRecord , , acNewRec
Exit_Kommandoknapp55_Click:
Exit Sub
Err_Kommandoknapp55_Click:
MsgBox Err.Description
Resume Exit_Kommandoknapp55_Click

End Sub