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 don´t get it to work. It just enters the data to one of my tables

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

 

[MarkK]

Execution of the code you posted ends here...

Private Sub laggtillpump_Click()
On Error GoTo Err_laggtillpump_Click
 
    DoCmd.GoToRecord , , acNewRec
Exit_laggtillpump_Click:
    Exit Sub

Exit Sub does exactly that.
But also be advised that 'DoCmd.GoToRecord , , acNewRec' also doesn't actually add a record. After you issue that command your user can navigate away from the 'NewRec' and the table will not have been affected.

 

[nIGHTmAYOR]

daffeleto,
The code you posted is as old as office 97 , please post an example of what you are trying to do and do note that it wont take more than 2 lines to achieve through DoCmd.Runsql

 

[daffelito]

I know, have corrected it now. I dont know how i was thinking realy.

 

[daffelito]

Ok, i will explain what i'm trying to do (i´m using access 2000)
I have a form that i use to enter data about a product. That form is bound to a table that looks like this:

id (primarykey and master to ProductID in table2)
productinfo1
productinfo2
..and so on

But i also got a listbox in that form that are unbound but showing the content of an query. What i want is to select multiple values from that listbox and put them into an other table, table2.

table2 looks like this:

id(primarykey)
ProductID (child to id in table1)
voltage

Why i need to do that? Because the products i enter info about can be run on different voltage, and i have to normalize my data.
So when i enter info in my form and then push my add button i need to:
1. add a record to table 1.
2. Take all the values from the listbox + the current id from table 1 and put them in table 2. (Voltage, ProductID)

Then it should look like this, for example in table 2:
columns are (id, ProductID, voltage)
1 2 230
2 2 380
3 2 500

Hopefully this info would help you to help me.

 

[daffelito]

I have solved it.
I rearanged a bit in the code so it 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

That did the trick

 

[nIGHTmAYOR]

Here is how MS Access Nowadays operate , also this is how to minimize code :

Dim varItem As Variant
Dim strSQL As String

For Each varItem In Listruta53.ItemsSelected
stSQL = "Insert into tblPumpsortEl ([ProduktID] , [voltage]) Values (" & Me![id] & '", " & CStr(Listruta53.Column(0, varItem)) & "')"
DoCmd.RunSql stSQL
Next varItem