Full Version: How to add data in 2 tables with one button.
UtterAccess Forums > Microsoft® Access > Access Forms
daffelito
Hi! I need some help with an issue i have.
I have a form that i use to store data about a product. By some help from the friendly people here at utteracces i have normalized my tables and made a relationship between two of my tables.
The relationship works as it should when i write directly in the tables but i don't get my form to work the way i want.
The tables look like this:
Table 1
Id - counter (primary key and Parent to ProductID in table 2)
productname
etc
etc
Table 2
ID - counter (primary key)
ProductID (Child to Id in table 1)
Voltage
In the form i have a listbox where i can choose multiple values.
What i want when i press the add reccord button that i have made, is for the code to add the reccord into table 1 and all the values from the multi selection listbox into table 2.Voltage. Also i need it to add the current reccord ID from table one to the ProductID in table 2.
Table 2 should look like this (example) when its done.
ID ProductID Voltage
1 1 230
2 1 380
3 1 1000
Hopefully it makes sense, otherwise i have to explain a bit more. But what i need is not to enter the voltage selection in a different form, i need it to be in the same with just one push of a button that does everything in one go. I have tried to do it self but with no success.
Larry Larsen
Hi
If the form is based on table1 then those controls should feed back to the bound fields of the table..
So to add the selected items and record id use some thing like:
CODE
Dim db As Database
Dim rst As Recordset
Dim varItem As Variant
Dim strSQL As String
  
        
    '--- open the table
    Set rst = CurrentDb.OpenRecordset("Tabl2")
    
    '--- loop through all selected items in the list box
    '    adding data to the table
nbsp;   For Each varItem In ListBoxName.ItemsSelected
        rst.AddNew
        rst!ProductID = Me.ID
        rst!Voltage = ListBoxName(0, varItem)
        rst.Update
    Next varItem
    
    '--- close the table
    rst.Close
    Set rst = Nothing

HTH's
thumbup.gif
daffelito
thanks i will try it. I´ll get back to tell if it did the trick or not.
This is a "lo-fi" version of UA. To view the full version with more information, formatting and images, please click here.