Use Form to Add Bulk Records to Junction Table


This question is in regards to Access 2007, but I'm sure advice would be applicable for all versions. I hope so anyway .

I have three main tables I'm working with: Student, Service, and StudentService (which is a junction table for Student and Service). Here is the structure:

Student Table
StudentID (pk)

ServiceID (pk)

StudentService Table
StudentServiceID (pk)
StudentID (fk)
ServiceID (fk)

So far, I've managed to use a form to create services in the Service table. Now, I'm trying to figure out how to enroll students into those services. I know that means I have to find a way to get the StudentID, and the ServiceID into the StudentService table. I am able to do this manually, but most likely I'm going to need to be doing this for hundreds of records, so I'm looking for a better way, through a form is possible.

Ideally, my users would be able to choose the Service they want to enroll students in, then choose the students to enroll in that service. Does this make sense? Is it even possible? I've tried so many different approaches (mostly centered around append queries), but am coming up with nothing but frustration. If anyone can help, I'd really appreciate it. Thank you!

Post your answer or comment

comments powered by Disqus
how to use macro to add a record from form to table?
i have been struggling for 1 hour... pretty annoying...
anyone can help?...
thank u soooo much^^

Hi all, I am using Access 2010

I have uploaded database to show where i am at, but I am now stuck on the following:

As you will see I have a form called Main which dispays all active ( uninvoiced ) customers which has a list box that when selected show the Invoice.

What I want is when the "ViewSimpleInvoice Frm" is displayed I can click on the "Add Another Booking" button this then opens another form to add another record to the selected invoice.

I am open to any other suggestions of completing this task.



This is probaly a simple thing, but any help would be appreciated. I have a Customers form and would like to install a command button that opens the NewShipment form to Add New Record. I am using Access 2010 and my design so far is;
One to Many (CustomerTbl to ShipmentTbl)
One to Many (ShipmentTbl to PartTbl) a shipment will contain many customers
One to Many (PartTble to PartTypeTbl)
My logic is that a customer will have many shipments. Shipments will include multiple customers and many parts. The many parts will be of many types. Now if this confuses you it is ok, I am confused myself. Any helps any one could give a monkey banging away at his keyboard would be appreciated.

So Simple a Monkey could do it, yeah right!

I have a table of information. I have created a form to input all the data necessary in order to add a record to the table. The form is not currently based off of the table and is simply a bunch of text boxes with labels.

The reason why I didn't create the form based off the table is because I don't want all the contents of the table to be displayed when the form is loaded. I want to form to be soley for adding new records to the table. I also don't want the form to display any information from the table when it loads, I want it blank. I don't want any chance of the person that is using the form to be confused or accidentally be able to edit the data in the table.

I have added a button that I want to use to initiate adding the new record.

How can I now retrieve all the data in the textboxes and use that to add a new record to the table. Is there simply a few lines of code that can do this that I can add to the on_click of the button?

Hi all,

I'm new to the forum as well as somewhat of a beginner in Microsoft Access. I am working currently on campus helping a program fix its Access database. Pretty much, they have three tables all related by an email. Each table has certain information about the person. The form has all the fields of each table. Currently, I can use the form to update current information about a person, and this will change that field in the table. There is also a new button on the form that creates a new record. However, this new record is only added to the current form datasheet and does not add the new record to each of the three tables. I'm wondering if there is a simple way to do this? I'm not that proficient in VBA coding but I do understand it, so if that is a solution please let me know. Thanks in advance for all your help!

I have a database which has a table of contact details. There's a separate table which contains related information about Appointments each of the contacts makes.

There are then two forms: one which allows entry/viewing of Contact details and one which allows viewing of appointments by customer. At the top of the Appointments form is a combo box that lets you select which customer's appointments you want to view.

Not all the customers who are listed in the contacts table are listed in the customer's combo box on the appointments screen, because they have not yet ever had an appointment, so don't have a record in the appointments table.

What I want to do is this: on the Appointments screen, I want there to be a button that says "New Customer". This should bring up another form which functions as a message box, in which there's a combo box listing all contacts from the Contacts table. The user should select a customer from this, then click a button. When the button is clicked a new record should be created in the Appointments table, which this customer's name in the "Customer" field. The small message-box form should then close, and the Appointments form should update to allow entry of an appointment for the newly added customer.

So far, I've got as far as creating the message-box form and adding the combo box. However, I'm not sure what code I can use to make Access add a record for the selected customer to the Appointments table, and then display that record on the Appointments form.

I hope this makes sense, since it's difficult to describe, and that somebody will be able to point me in the right direction.

Many thanks,


Hi there,

Does anybody know how I would set a form to create a new record when it is opened aswell as having a cmd button to add new record.

I have a database used by several people with a differing knowledge of Access DBs. I have noticed that some people are overwriting data by typing over the first record in the db instead of clicking the add new record button.

I was wondering if I could set the form in question to automatically add a new record when it is opened?

Any help would be greatly appreciated!



TestMultipleRecords.zipI have a form where I am trying to add multiple records to at a time.

The form “Form1” pulls data in from two tablesTable “Orders” is just order details and is pre-populated of the necessary data (No data manipulation will be needed here but the data will need to be displayed on the form)Table “OrderItems” is essentially empty in my form, this is the table I want to add data to. The thought be here is that a person will be going though each record and manually adding in the additional information required. Each item could have multiple entries, hence the need to be able to add multiple entries at once.
I will need the ability to add multiple records per time to the tableFor each order number, all records will need to have the same Order ID as the original order
I have added a sample DB which I have included narratives to better explain what I am trying to accomplish.
Any help would be greatly appreciated.

I am trying to make a form to to add data. so I made my table, made my main form and then made another form to update the table and placed a command button on the main form to open the update form.

The problem is is that it works fine to add one record however every time I open and change info it just changes the same record rather than adding a new record.

Am I doing the right thing or am I way off?

I am developing a database for a local team's fan club which has three tables- tblMatches, tblEvents and tblBookings, each of which has a corresponding form to add a record to the database. The primary key for tblMatches is 'Match ID' and the primary key for tblEvents is 'Event ID'. Both also have a field called 'Seats remaining', or the number of seats left that can be booked. Seats are booked through the form for tblBookings which has four fields: Event ID, Seatnumberevent, Match ID and Seatnumbermatch and a button called 'Add Booking'. How can the 'Add Booking' button be set up so that when it is clicked, it adds a record to tblBooking and also subtracts 1 from 'Seats remaining' for the corresponding event or match? Thanks.

So I have a Junction table that is combining a tblParts and and tblVendors that are combined into tblPrice. It uses "Part Number" and "Vendor ID" to join the tables. Each part can have multiple vendors with different prices.

I have a form that is used to add new parts to the tblParts table, and I want to have a button that launches another from to add a Vendor as a supplier for a part. (All the vendors are already in the tblVendors table.) The second form comes up just fine, and I can get it to jump to a new record (using the OnLoad), but when it does that it clears all the fields. However, I want to keep one of the fields filled. When a user is hitting this button, the part number from tblParts field needs to stay the same, so when the user selects a vendor from my list, it generates a new unique record with the Part number and the Vendor ID.

I have tried searching, and I can't seem to locate the correct settings or code to accomplish this.

Help? Thanks!


I have a form that have three tabs. In one of the tabs I have many fields that belog to a table (InspectionTable) that uses a foreing key that relates it to the MasterTable of the database.

I would like to create a button in a form to allow users to add a new records in the InspectionTable after having added a record. After clicking this button the fields from the InspectionTable should pop-up in the tab.

I am having problems, because the form in based on the MasterTable that is related to the InspectionTable using a foreing key. How can I create the button? I am using Access 2007

Any help???

I was testing a button in a form with an OnClick event. I wanted to see if I could use a form to insert a new record into a table, I tried the following but got a Syntax Error:

INSERT INTO Table1 (Org, Date, Name)
VALUE ('#Me.Org#', #Me.Date#, '#Me.Name#')

Org and Name fields are text fields. Date field uses dd/mm/yyyy.

I went into Form View, filled in the three fields and clicked on the button.

Can you help? Thanks.

Is it possible to use one form to add records to two different tables that have a linked field? Or do I have to make two different forms?

I have an Assigned Order table and Assigned Order Lines table with the order number as a common field. These tables are updated the previous end of business with new data from the server through another process but many times we come accross what we call a late order (a must deliver today) and have to add it to the access tables to make all the reports accurate for the days deliveries. I appreciate any suggestions.

Hello, I'm new to the forum, this is my first post. I'm revisiting Access after a good few years and I'm already facing a problem (I'm using Access 2007 now).

I'm building a simple database to keep track of applicants for a job, and more specifically the 'referees' designated by each applicant. My simple database relates as follows:

Now I want to enable the user to add a new applicant, and to add referees for that applicant on the same form. I designed the form as follows:

When the 'Add Referee' button is clicked, I load a form to enter the new referee's details, but at the same time, I want to add a record into the tblCorrespondence table to link the two.

If I try to add a new record on the sub-form where it says * (New), Access will not allow it. But even if it did, this is not the method I prefer for adding new referees, I would prefer to use a seperate from, as I stated earlier.

If anyone could help me with the simplest solution for this I would be very grateful. I hope this problem isn't too simplistic for this forum, there seem to be a lot of pretty clued up heads on here!

Good Morning,

I was wondering if it was possible to let the user select a table, in which he/she need to add or edit a record.

The reason is as follows:

I am making a database to determine the relationship between Regulatory Requirements and Standards used in our branche sector.

Sometimes the Standards change, and in order to get a good view on what documents are refering to the standard(s) in question, i want to make a database in which u can either search by the name of the Regulatory Requirements or the Standards.


I am currently working on a form, that gives the user the opportunity to add/edit records in the table. And instead of having to make 10 different forms, i was wondering if it was possible to make 1 form (with subform?) and give the user the option to select the table he wants to add records to.

Is that possible?


We currently are using a MS Access 2010 project linked to SQL 2005 Database. The SQL DB and Project we created a while back.

Recenlty I was asked to create a form to collect more data for our business. I followed the step below:
1. Logged to SQL Server Management Studio and created the new table with all required fields. I opened the table and added few test records to it.
2. Opened MS Access 2010 Project, selected Forms and used the Form Wizard to create a form based on the table created in step 1 above.

After the form was created I opened it but for some reason I am not able to add new records or make changes to existing records. The button to add new record is grayed out.

The SQL DB and MS Access 2010 Project were created by an employee that is no longer with our company.

Can someone assist in resolving the issue why I am not able to add new records? For some reason, I can created forms based on existing tables and able to add new records on the forms. Only the new table and associated forms don't allow me to add new records.


We have employees do a daily audit on all our process activities for the day, but currently these are just filed as records and not utilized to drive internal improvement. I created a database that we can keep track of the question category, the specific requirement, and then whether the process passed or failed that particular part of the audit. I would like the data entry person to be able to go into a form with all the questions already in it, and then just check the pass/fail box to indicate whether the audit was passed or not, so that I can then chart where we fail the most and direct our improvement activities to those areas. The audit table has a column for the question ID and whether or not the audit was passed, how do I create a form pre-loaded with all the questions, and then insert the question ID's and audit results into the table at once?

Thanks in advance for any help.

I am having trouble creating a data entry page.

The database is the index to a record/CD collection. One table is songs, one is recordings ("sources"), and a third contains links between the two. Each record in songs has a unique ID, LRN. Each record in sources has a unique ID, acqno. Suppose a particular song has LRN 294 and appears on acqno 101, 252, and 747. This would be contained in the links table.
LRN Acqno
294 101
294 252
294 747

My problem is that I have a data entry form that pulls up an individual record within songs, and then I want to "Add new recording," which is to say, I want to add a new record to the links table that contains the LRN for the CURRENT record in songs and then be prompted to manually enter an acqno (referring to a new source that has just been added).

I seem to have at best a vague idea how to do this part. I have created a macro called AddLink that the "Add new recording" button activates, but all I get are error messages.

Any ideas? Thank you in advance.

So I've been using the following code in order to add new records using another form, however I keep getting an error.

	For i = 1 To 8
    If ("txtJob" & i) > "" Then
    DoCmd.RunSQL " INSERT INTO tLabour (LabourDate, EmployeeID, ProjectID, Hours) VALUES ('" &
Forms!AddTimesheet![cboLabourDate] & "', '" & Forms!AddTimesheet![cboEmployee] & "', '" & Forms!AddTimesheet!("cboJob" & i) &
"', '" & Forms!AddTimesheet!("txtJob" & i) & " ');"
    End If
Next i

The Error I get is that access can't append a new record because of a type conversion failure.

The Insert Into seems to be working though, because if I check my table afterwards the new records are there, however for each correct record another extra incorrect record exists with the correct LabourDate and EmployeeID but null values for ProjectID and Hours.

Help me remove the excess records and fix this error message!

I have four tables with unique records as shown below. I'd like to add a record for a new item to the database by selecting the values from combo boxes for Supplier, Item and Unit. If the values do not exist the user would then type in a new value. SuppliersItemCode and Cost would always be new values. What's the best way to go about this? I am unsure how to add a record to a normalized database where you sometimes have to use / reference existing unique values in multiple tables via foreign keys for the new record.

The logic of the form would be:

1. Select existing or add new Supplier.
2. Select existing or add new Item.
3. Select existing or add new Unit.
4. Enter new SuppliersItemCode
5. Enter Cost

SupplierID (primary key)
Supplier (indexed unique)

ItemID (primary)
Item (indexed unique)

UnitID (primary key)
Unit (indexed unique

SuppliersItemCostCode (primary key)
ItemID (foreign key)
UnitID (foreign key)
SupplierID (foreign key)

Any advice or assistance is greatly appreciated, thank you.

I know what's wrong with my query, but I don't know how to fix it.

Attached is a screen shot of the relationships in my query. You'll notice I reference 5 tables, and 2 queries. The two Queries contain expressions for combining fields in the tables with date of birth and date of death info. For data entry I have separated day of birth (and death), month of birth (and death) and year of birth (and death). These queries combine those fields into date strings month/day/year so that I can use them in a complex calculation. (To find the exact age of a person upon death in years, months, and days.)

The problem I have is that while referencing these queries in the query I use for data entry, I can't add new records. I have discovered that if I remove those queries referencing expressions then I am once again able to add new records. However, if I remove them, then my calculation field on my form doesn't work!

Any ideas are welcome.
Also attached is a picture of my relationships for your reference. (Sorry if it's a little fuzzy, I had to resize it a tad in order to attach it.)

I have a form based on a select query (query does calculations as well). The query pulls from 2 linked tables, and it's not allowing me to add new records. On the form properties, I have "allow additions, allow deletions, allow edits, and data entry" set to yes. I've tried adding code to go to a new record when the form opens and this doesn't work, either.

Any suggestions?
Thanks in advance.

How do I code a button located on my main form to add a new record to a subform?

I can reference controls in the subform easily enough, but to add a new record uses DoCmd, which doesn't seem to work with subforms...

Any ideas?

Not finding an answer? Try a Google search.