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.

Thanks.

Patrick


Sponsored Links:



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.

Thanks
Sean




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 )




Hi,

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 !
Paul




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_Add_Record_Click:
Exit Sub

Err_Add_Record_Click:
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.

Thanks




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?

Thanks-

Anissa

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.




Hello,
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
Loop
' 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
rsa.AddNew
![Change Order ID] = ChangeOrderID
![Change Date] = changeDate
![Change Type] = changeType
![Description] = Description
![Quantity] = Quantity
![Price] = Price
![Status ID] = statusID
rsa.Update

rsa.Close
Set rsa = Nothing

rs.MoveNext
Wend

End If

rs.Close
Set rs = Nothing


End Sub

Thanks for any Help

Steve




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?

Regards
Paul Sibson
Salisbury, UK




Hi,
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
STRSQLL = "SELECT * FROM Table1;"
Set RSTT = DBSS.OpenRecordset(STRSQLL)
With RSTT
I = 1
For I = 1 To 4
.AddNew
!fldVar("F" & I) = Me("txtF" & I)
.Update
Next I
End With
End Sub

Thanks in advance for your kind help.
Regards,
Manthiralaya




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:

tblParts

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

tblPurchases

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!!