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

Question

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

You can do something like this or this

Anatoly Ruchka
– Anatoly Ruchka

2014-12-16 17:55:04 +00:00
Commented Dec 16, 2014 at 17:55 — Anatoly Ruchka
– Anatoly Ruchka, Commented Dec 16, 2014 at 17:55

Gord Thompson · Accepted Answer · 2014-12-16 18:51:54Z

1

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

edited Dec 16, 2014 at 18:51

Gord Thompson

125k38 gold badges251 silver badges458 bronze badges

answered Dec 16, 2014 at 17:52

Erik Loebl

664 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

user1894555 Over a year ago

Thanks for the reply - im confused by the recordsource part though. If i set the record source as tblPeriod, it only shows the periodID. I want it to show the period name, as the user would not know what id relates to each period. Also, in the sql statement, what does the m_ stand for? why is that required?

Erik Loebl Over a year ago

The combo box has the "Row Source" property on the "Data" tab. Use the "triple dots" to make a query like "SELECT periodID, periodName FROM tblPeriods", then click on the "Format" tab and enter "2" in the "Column Count" property, and 0";2" in the "Column Widths" property. Then you will be able to see the periodName text you are selecting, but storing the periodID. I normally prefix my table's fieldnames with the initials of the table name, so the "m_" is from "tblMaster", it's not required, it personal preference. Does that help?

user1894555 Over a year ago

Yeah i think ive got it now...One last thing, if i wanted to be able to select a class, and have the cobo box for pupils to only show pupils from that class, is there a way to do that? i.e can you control the records in the combo box based on the item selected in a previous combo?

Erik Loebl Over a year ago

Yes, you may want to check this out, vbahowto.com/how-to-create-dynamic-combo-boxes-in-access

Collectives™ on Stack Overflow

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

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related