Pulling data from an access tabel and inserting it in another tabel

0
votes

Im having some trouble with the design of my database. In microsoft Access I have two tables. One named table1 this table contains three fields (Name, Surname and Birthdate). The other table, named table2 contains two fields (Name and Surname). I want the following to happen. If I make a new record in table1 using a form the Name and Surname get passed/inserted to table2 automaticly. What is the best way of doing this? I was messing around with the primary key but that doenst seem to work. And since I'm a beginner I dont know where to go from here.

Thanks in advance for your time and efford!

2
databasems-access
Why would you want to do that? Use a query to show data from a table in different form (e.g. fewer columns), don't duplicate the data. - Andre
I dont want to duplicate the data. In my final project I want to seperate the personal information from other information like courses. But I want to make a form where I can create a new record, and make it so that this new record gets put in all the different tables. So the personal information in a table called person and the information about courses in the corresponding course table. I hope you can understand my explanation. If not please ask and I will try to rephrase. - J.Rommers
Then you should design your forms to reflect the table structure. E.g. have a main form with the person data (bound to person table), and a subform for courses, where 0..n courses for the person can be entered. -- Collecting all data in a giant form + unnormalized table and then distributing it to various normalized tables is not a good idea IMHO. - Andre

2 Answers

0
votes

Just figured out the awnser to my question! I used the following code

Private Sub addNew_Click()
Dim db As Object
Dim rst As Recordset

Set db = CurrentDb
Set rst = db.OpenRecordset("select * from Person", dbOpenDynaset)


rst.AddNew
    rst!PeopleSoftNr = tbPeopleSoftNr.Value
    rst!Name = tbName.Value
    rst!Birthday = tbBirthday.Value
rst.Update

Set rst2 = db.OpenRecordset("select * from Dental", dbOpenDynaset)

rst2.AddNew
    rst2!PeopleSoftNr = tbPeopleSoftNr.Value
    rst2!Dental = tbDental.Value
rst2.Update
End Sub

This allows me to write data to multiple tables.

0
votes

In my opinion the best way to do, what you described, is creating a form for the personal data and include a subform to assign courses of the persons.

Detail information about the courses should be edited in an own form for the courses.