CODEWITHSUNDEEP
databasems-accessvbams-access-2010
user1894555
user1894555

Reputation: 109

Insert new record into a table from a form, using data from multiple tables

I am fairly new to both Access and Databases and I'm struggling with how to add a new record into a table using a form, which gets its options from multiple other tables.

I have 4 tables which contain: a list of periods (tblPeriod), a list of pupils (tblNames), a list of subjects (tblAreaOfLearning) and a list of levels (tblLevels).

I want to create a form which will allow a user to select the period, then the pupil, then the subject, and then the level at which that pupil is attaining at that subject, and then add the ID's for all of these into a 5th table called tblMaster

I have no idea how to do this though? I have created a query linking everything to the master table by the ID's, but i have no idea how to create the form and the command to insert this into the new table?

enter image description here

All help appreciated

Upvotes: 0

Views: 5020

Answers (1)

Erik Loebl
Erik Loebl

Reputation: 66

Here's an idea:

It looks like you can scrap your query idea, and do this instead...

Create a new form with 4 combo boxes:

cboPeriodID- user to select the period (recordsource tblPeriod query) cboNameID- then the pupil (recordsource tblNames query) cboAreaOfLearningID- then the subject (recordsource tblAreaOfLearning query) cboLevelID- then the level at which that pupil is attaining at that subject (recordsource tblLevels query)

Then have a command but at the bottom of the form with an Event Procedure to run the SQL to insert the values into the master table.

Sub btnSubmit_Click()

dim strSQL as string

strSQL="INSERT INTO tblMaster (m_period_id,m_name_id,m_areaoflearn_id,m_level_id) VALUES (" & cboPeriodID & "," & cboNameID & "," & cboAreaOfLearningID & "," & cboLevelID & ")"

CurrentDB.Execute strSQL End Sub

Upvotes: 1

Related Questions