Adding records to a table using a form

I have a form (Form1) and a table (tblScanned).

The form has a unbound box called Test. The table has a field called ApplicantID.

I want to scan in Applicant numbers using the form and populate the table.

My vba code is

Private Sub Test_Click()
DoCmd.RunSQL "INSERT INTO tblScanned(ApplicantID)" & _
"VALUES (FORMS!Form1!Test)"
End Sub

I want the form to add the record and then clear the value in the unbound box.

Then I want to scan in a new record and add the new record to the table and clear the value in the unbound box.

Any help would be appreciated.



Post your answer or comment

comments powered by Disqus
Hi, basically what i want to do is append a record to a table using vba. I would like to take two values from my form (productID, supplierID) and insert them into a table (supplierProductsTBL) under the same headings. I've constructed an sql statement but am getting the following error:

runtime error '3346'

number of query values and destination fields are not the same'

I think this happens because the table also contains an autonumber field (supplierProductID) and i'm not referencing this in my sql statement (below):

mySQL = "INSERT INTO SupplierProductsTBL ( SupplierID, ProductID ) VALUES ('" & SupplierID & "'), ('" & ProductID & "')"

i wuld just get rid of the autonumber field but i need this.

Any help on this would be appreciated, cheers mark

Hi there, apologies if this question is a bit basic, I have searched for the answer to this question, and im sure it must be out there but i've had no luck after searching for quite a while.

How do you add data to a related table using a form?

I can add the record fine in the table (clicking the '+' sign at the side of the record brings a drop down with the empty fields to populate the related table)

However I was unsure of how to do this using forms. There is no '+' when i create a form for the parent table.

So for example my parent table (FrmVisitDetails) has 'VisitID' (Autonumber) as the primary key. And the child table (FrmWrkDetails) has 'VisitID' as primary key (number) I want to be able to add a record into FrmWrkDetails once FrmVisitDetails has been populated. I know you could do it as a subform on the original form, but I want them both on seperate forms as there are a lot of fields.


Hello everyone,

Is there a way to automatically add a record to a table when a record is added to another table?

For example: I have a table [contacts] and a table [calls].

The [contacts] table contains prospect demographics (name, address, phone number etc.), the primary key is an autonumber.

The [calls] table contains the fields: CallID, ContactID, CallDate, CallTime, Subject, Notes. This table is used to record information every time a prospect is contacted.

The relationship is 1:n. bound by the autonumber field ContactID.

When a new record is added to the [contacts] table I would like a note to be automatically added to the [calls] table. Actually I want a date stamp and the subject line to read "Prospect added", I dont actually need a note in the notes field.

Is there a way to do this?

I need a way to create new records in a table using code.
Anyone can tell me how?
Is using ms jet with recordsets the only way? (i don't have it )


Does anyone know what code I need to add in the VB section to add new records to a table(thats exists in SQL) ?

In other words, what needs to be included in this section:

Private Sub Add_Record_Click()
End Sub

Thanks !

Access 2007 gurus, please help!

I have a table that I want to add new records to from an unbound form.

I have been looking everywhere for some VBA sample code that would help me to do this but can't find anything useful.

I want to use an unbound form not a bound form!

Can somebody please help me with this!

i am finding it difficult to add a record to my access table 'employee' using a command button which i pasted on my form 'employee'. i have written the event code
Private Sub Add_Record_Click()
On Error GoTo Err_Add_Record_Click

DoCmd.GoToRecord , , acNewRec

Exit Sub

MsgBox Err.Description
Resume Exit_Add_Record_Click

End Sub. but this is not working or i am unable to add a record to the table. can any one help me to solve this problem.

Hello everyone,
I was just wondering if there was anyway that i could add records to a table based upon a number given. For example i have a form that says please enter the amount of disks you have, and say i enter 8 disks, when i click the button to add to the database, i want the table that contains information about the disks to automatically populate 8 records, the key would be autonumber and i wouldn't need any other information in the tables apart from the autonumber that would generate 8 times. I have read that you can loop through the record based upon the amount entered but any help would be appreciated!

Anyone know how I refer to a table using VBA?

There doesn't seem to be a Tables collection and OpenRecordset creates a recordset I believe.

What I'm actually doing is trying to refer to a table so that I can write records to it from a from. The form records are partially generated from another query.


I have a form with 4 tabs. The first 3 tabs take data from the user. The 4th tab displays this data for the user to confirm that it is correct. At this point I would like to pass those values as a record to a table. How can i do this? any help greatly appriciated!

I'm trying to add a set of records to a table where in that table a field's data type is set to AutoNumber. I can this by copying the records and pasting them into the table. However I would like to simplify the process. I tried doing an append query and it would not let me do it. Actually it error'd saying there where violations and etc do you want to do it anyways. Um sure why not, right. Wrong. It changed my autonumber to number and inserted the records with 0 for that field. Now I can not change the field back to AutoNumber. Arggggg. So after I fix this...

Is there a way to append the table with an append query(or any other query or any other way) so I would not have to copy and paste the new records into the table?

Hi- I have a query to append new invoice records to a table I name master Invoice. I am having problems getting this query to look at query CustomerDetails and only append records that do not exist in Master Invoice. I have included my query below. This query with the criteria included will not append records, however, when I remove the criteria, it appends all records, including any that exist in the master invoice. Any thoughts into how to resolve this?



INSERT INTO [Master Invoice] ( [Customer Name], [Address 1], [Address 2], CUSTOMER_City, CUSTOMER_State, CUSTOMER_Zip, Weekending, Quantity, [ITEM ID], Description, [UNIT PRICE], Amount, WHSE_DESC, [Customer ID], [Displayed Terms], [Due Date], [CUSTOMER PO], SalesTax, Payment )
SELECT CustomerDetails.[Customer Name], CustomerDetails.[Address 1], CustomerDetails.[Address 2], CustomerDetails.CUSTOMER_City, CustomerDetails.CUSTOMER_State, CustomerDetails.CUSTOMER_Zip, CustomerDetails.Weekending, CustomerDetails.Quantity, CustomerDetails.[ITEM ID], CustomerDetails.Description, CustomerDetails.[UNIT PRICE], CustomerDetails.Amount, CustomerDetails.WHSE_DESC, CustomerDetails.[Customer ID], CustomerDetails.[Displayed Terms], CustomerDetails.[Due Date], CustomerDetails.[CUSTOMER PO], CustomerDetails.SalesTax, CustomerDetails.Payment
FROM CustomerDetails LEFT JOIN [Master Invoice] ON (CustomerDetails.[CUSTOMER PO] = [Master Invoice].[CUSTOMER PO]) AND (CustomerDetails.WHSE_DESC = [Master Invoice].WHSE_DESC) AND (CustomerDetails.Weekending = [Master Invoice].Weekending) AND (CustomerDetails.[Customer Name] = [Master Invoice].[Customer Name])
WHERE (((CustomerDetails.[Customer Name])[Master Invoice].[Customer Name]) AND ((CustomerDetails.Weekending)[Master Invoice].[Weekending]) AND ((CustomerDetails.WHSE_DESC)[Master Invoice].[WHSE_DESC]) AND ((CustomerDetails.[CUSTOMER PO])[Master Invoice].[CUSTOMER PO]))
GROUP BY CustomerDetails.[Customer Name], CustomerDetails.[Address 1], CustomerDetails.[Address 2], CustomerDetails.CUSTOMER_City, CustomerDetails.CUSTOMER_State, CustomerDetails.CUSTOMER_Zip, CustomerDetails.Weekending, CustomerDetails.Quantity, CustomerDetails.[ITEM ID], CustomerDetails.Description, CustomerDetails.[UNIT PRICE], CustomerDetails.Amount, CustomerDetails.WHSE_DESC, CustomerDetails.[Customer ID], CustomerDetails.[Displayed Terms], CustomerDetails.[Due Date], CustomerDetails.[CUSTOMER PO], CustomerDetails.SalesTax, CustomerDetails.Payment
ORDER BY CustomerDetails.[Customer Name], CustomerDetails.Weekending;

I am working on an input form with linked subform that I am trying to get to add a new record to two linked tables.
I have a Storm Damage table and a Storm Damage Sites table that are linked by road_number, mile_post, and fiscal_year. They have a one-to-one relationship. I got the form to work to view records but when I set it to allow data edits it shows up blank and won't show any records or allow the addition of new records.
I have been adding records by workaround with a separate form for each table but I have to add the same information twice for the primary keys.
Is there a way to be able to update two tables with the same fields (primary key fields) so I can add a record to both tables on the same form or with a form/subform?
I am trying to make it as userfriendly as possible, hopefully my description makes sense.

I'm trying to update(create a Change Order) based on a table of Extra work items( Extra Log) for different projects. I use a form bound to (Extra Log) to easily enter items as needed. I Also use a check box to allow only those items that are checked.To print a change order on this table the data needs to be split into 2 tables first ( Change Order) Second(Change Order details) with a common field Change Order Id to relate them. There are multiple line Items for Each Project and then a Change Order is created From this. I have the Following Code but I get an Error message (Wend Without While). I'm also having difficulty creating a (ssql) statement to filter the data to specific Projects that have a yes/no field checked (True).
The first part of this sub works. I create a special change Order Id based on the project id, then a record is created in the (Change Order) Table.It's creating the line Items that causes the errors. At some point I will use a similar sub to create an Invoice on this created Change Order.

Private Sub cmdchangeorderDetail_Click()

Dim Db As DAO.Database
Dim rs As DAO.Recordset2
Dim rsa As DAO.Recordset2
Dim CustomerID As Long
Dim statusID As Long
Dim projectID As String
Dim ChangeOrderID As String
Dim changeDate As Date
Dim changeType As String
Dim Description As String
Dim Quantity As Long
Dim Price As Currency
Dim Num As Integer
Dim Ctr As Integer
Dim ssql As String

Set Db = CurrentDb

' set the Counter variable to zero so
Ctr = 0
' Will use this variable to fill the last part of the Change order ID
Num = 1

'Set the variables for the change order function
statusID = 0
projectID = Me.Project_ID
CustomerID = Me.[Customer ID]

' String the change Order ID :Will Format ( Project ID & CHA & Num)

ChangeOrderID = Me.Project_ID & "-" & "CHA" & "-" & Num
Do Until Not Changeorders.IsChanged(ChangeOrderID)
Num = Num + 1
ChangeOrderID = Me.Project_ID & "-" & "CHA" & "-" & Num
' Open the Change Ordr Table and Add a record
If Changeorders.CreateChangeOrder(CustomerID, statusID, projectID, ChangeOrderID) Then
End If

' set ssql to criteria string for opentables function
'ssql = "[Customer ID]= " & CustomerID And "[Status ID]= " & statusID And "[Project ID]='" & projectID & "'"

' open the extra log table and fill the variables to update the Change Order
Set rs = Db.OpenRecordset("Extra Log")
With rs
While Not rs.EOF

If (![Change Ordered]) = True And (![Status ID]) = 0 Then
![Extra Date] = changeDate
![Change Type] = changeType
![Description] = Description
![Quantity] = Quantity
![Price] = Price
![Status ID] = statusID
Set rsa = Db.OpenRecordset("Change Order Details")
With rsa
![Change Order ID] = ChangeOrderID
![Change Date] = changeDate
![Change Type] = changeType
![Description] = Description
![Quantity] = Quantity
![Price] = Price
![Status ID] = statusID

Set rsa = Nothing


End If

Set rs = Nothing

End Sub

Thanks for any Help


I am trying to add data in a related table using the PK in the data in a form.
The form is based on a query selecting all Student records. Query not table because I wanted to show descriptions not numbers so have to have linked tables.
The code I have so far is:

Private Sub CmdAddCoursetoStudent_Click()
Dim dbsCollege As DAO.Database
Dim rstStudentAnnualcourses As DAO.Recordset
Dim intStuID As Integer
Dim strSQL As String

intStuID = Forms!frmStudentCourses!StudID
Set dbsCollege = CurrentDb
Set rstStudentAnnualcourses = dbsCollege.OpenRecordset("tblStudentsAnnualCourses ")
strSQL = "INSERT INTO tblStudentsAnnualCourses([StuAnnCrsesStuID]) VALUES (intStuID)"
DoCmd.SetWarnings False
DoCmd.RunSQL strSQL

Me!ComboSelectCourse.Visible = True
Me!CmdSelectCourse.Visible = True
End Sub

and as is works but by using a prameter box to ask for the intStuID and then inserts the record. With single quotes around the intStuID I am not asked for the data and a record is inserted but no data in the appropriate field.
The making visible of controls at the end works and is for the next problem

What am I doing wrong?

Paul Sibson
Salisbury, UK

This query is related to MSAccess-Forms-VBA coding to adding new records in to a table using recordset.
Say for example, I have a table named as 'Table1' with fields F1, F2, F3, F4. In the Form, I have four TextBoxs named as txtF1, txtF2, txtF3, txtF4. I have a Command button named as 'Command19'.
I would like to insert a record into the 'Table1'. I want to use a loop to add the values from TextBoxes to the Fields in the 'Table1'. I want to loop so that I can insert more data.
Here is the code I had written. It gives an error of "Item not found in this collection". What am I doing wrong? Can anyone help?
Private Sub Command19_Click()
Dim DBSS As Database, RSTT As Recordset, STRSQLL As String, Table1 As TableDef, fldVar As Field
Set DBSS = CurrentDb
Set RSTT = DBSS.OpenRecordset(STRSQLL)
I = 1
For I = 1 To 4
!fldVar("F" & I) = Me("txtF" & I)
Next I
End With
End Sub

Thanks in advance for your kind help.

I ask myself is it better to bind the form to a table to create new records, or change old ones (Single record form) or to use recordsets

though it's easier to bind the form to a table using recordsets give more options and freedom.
it's also give a good start if you want to move from Access for the front end application.

How can I add an ID-number that always increases (by 1) each time I add a new record to a table (using a form)?
I want to always use some info from the record that was added last to fill in a different form.
The reason for my question is that I heard that the autonumber does not necessarily increase each time a new record is added...

Hi! At first I'm sorry if there is already such a thread but I couldn't find none.

I have a Base that i use to record parts bought for tractors. Since I have to store some info about each part (and later track the best offerers) I created a table:


in which I want to store ID NAME and CATEGORYID of different parts I also have a table that stores transactions (purchases)


Both of those are connected throo the field ID from tblParts.
I've made a form that adds records to tblPurchases, it uses a combo box to pick the bought part from tblParts, but since we use few hunderts of different parts its pretty annoing to pick them up, or search them from tle combo box. But most annoing will be adding every new part to the tblParts using another miniform, since there area lot of new parts in every invoice.

Is there any way to do so:

I type the part I've just bought to tblPurchase in the combo box, there is no such part in tblParts yet so when I add the record the form autoamaticly ads a new part to tblWares. It is not a problem to set the category later, or set some "none" temporally.

I've just started to play around with VBA so I don't realy know where to start, but a hint should kick me in the right directoin.

Thanks in advance!!

I have a form that isn't bound to a table, so it's for users to enter their own data (registeration form), I don't want the current data from the table shown in the form, hence why it's unbound. Also there is a field in the table that I want to send the data into, age category, if the user has inputted their age as between 11-18 then it has to be stated in the table as junior, 18-40 adult and 40+ senior,any ideas on what the code would be, this is a basis on what I have so far;

	If Me.Age < 11 > 18 Then DoCmd.GoToRecord , , acNewRec

I am not entirely sure as I am relatively new to using vba
many thanks in advance


I am having a hard time trying to figure out how to do the following:

I have two separate tables in my DB. I would like to take a record from one table and swap it with a record from another table using a form. The form would recognize each record by a serial number. So essentially the records would just change location inside of the DB.

Any help would be appreciated.



I have created a form that links back to a table that will allow me to add records to the table. I have added 12 records already but now, all of a sudden, the form will no longer allow me to add records. When I try to delete a record, it is telling me the form is "read only". When I go to the form properties, the allow delete, edits, and additions all say yes. I have not touched the Tools/Securities section of the database. What else could be causing this to happen and how do I fix it? Thanks!!

I have a table called tbl_esttime, what i want to be able to do is that on the click event of a button - write values to the table, so my table structure is:


Job NO
Staff Member

What i need to do is write 5 new records in the table, as such:

Job NO Staff Member Hours
CO00507230 Principal Engineer 0
CO00507230 Project Manager 0
CO00507230 Senior Engineer 0
CO00507230 Senior Technician 0
CO00507230 Technician 0
CO00507230 Engineer 0

the job no will change depending upon the job being worked on, but i guess this can be retrived okay from a stored field on a form for example.

Any help or suggestions would be most welcome, thanks you guys

I'm stuck again.

Here's what i'm trying to do:

I have a table with adresses with multiple fields (ia emailadress and a check box)

I have a table with emailgroups with two fields: groupname and groupmembers. The field groupmembers is basically a string of emailadresses seperated by a semicolon.

In a form with the table with adresses as a source, I'm able to check and uncheck adresses.

Now what I wish to do, is build an email group from the checked adresses and add it as a new record to the table with emailgroups, asking the user for a name for the new group.

I put a button on the form to do that.

Until now, I have this code:

Dim db As DAO.Database
Dim qdf As DAO.QueryDef
Dim strName As String
Dim strQuery As String
Dim rs2 As DAO.Recordset

Set db = CurrentDb

Set rs2 = db.OpenRecordset("SELECT Name From MSysObjects WHERE Name = 'Query2';")
If rs2.RecordCount = 1 Then
DoCmd.DeleteObject acQuery, "Query2"
End If

strName = "Query2"
strQuery = "SELECT TableAdress.Checked, TableAdress.EmailAdres FROM Query1 WHERE (((TableAdress.Checked)=Yes) AND ((Table.EmailAdres) Is Not Null));"

Set qdf = db.CreateQueryDef(strName, strQuery)

This creates a query with the required emailadresses.

Any suggestions on how to proceed from here?

I figure I have to build a string from the records of Query2 and then run some kind of append query, but that is way over my head.

Does anyone have any suggestions?

Thanks in advance!

Not finding an answer? Try a Google search.