How to make text entry into table based on clicking an button in forms in Access 2010

Question

I have created a form with submit buttons on it.

I have entered the data in the text box and then clicked on submit button.But the data is not getting saved in the table.Also,it is not showing any error message. It is not working at all.

Private Sub CmdAddNew_Click() 'add data to table CurrentDb.Execute "INSERT INTO tblemployee(firstname,lastname,Address,city)" & _ " VALUES('" & Me.txtfirstname & "','" & Me.txtlastname & "','" & Me.txtaddress & "','" & Me.txtcity & "')"

ChrisProsser · Accepted Answer · 2013-10-08 06:19:33Z

1

try this:

Private Sub CmdAddNew_Click() Dim dbs As DAO.Database, Sqltext As String, iCount As Integer Set dbs = CurrentDb Sqltext = "INSERT INTO tblemployee(firstname,lastname,Address,city) " & _ "VALUES('" & Me.txtfirstname & "','" & Me.txtlastname & _ "','" & Me.txtaddress & "','" & Me.txtcity & "');" Debug.Print "SQL statement generated with variables:" & vbCrLf & Sqltext dbs.Execute Sqltext, dbFailOnError iCount = dbs.RecordsAffected Debug.Print "..." & iCount & " row(s) inserted" End Sub

The debug.print messages will print to the immediate window (Ctrl+g) to view from VBA editor, you can delete them if you want to once you have confirmed it's working.

edited Oct 8, 2013 at 6:19

answered Oct 8, 2013 at 6:11

ChrisProsser

13.2k6 gold badges38 silver badges45 bronze badges

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

7 Comments

keval kumar Over a year ago

it's giving an error "index or primary key cannot contain a null value"

ChrisProsser Over a year ago

Okay, this means that we are either not inserting into all of the columns in tblemployee (and at least one of the missing columns is required) or that we have named one or more of the columns incorrectly in the query. Can you compare the SQL query in the debug window to the column names in the table.

keval kumar Over a year ago

Chris you are correct but now it's showing an error "record is deleted".

ChrisProsser Over a year ago

Can you expand on which of my suggestions was correct and what you have done to fix it please.

keval kumar Over a year ago

I have added another column to tblemployee and modified the query to Sqltext = "INSERT INTO tblemployee(EmployeeID,firstname,lastname,Address,city) " & _ "VALUES('" & Me.EmployeeID & "','" & Me.txtfirstname & "','" & Me.txtlastname & "','" & _ Me.txtaddress & "','" & Me.txtcity & "');"

|

Collectives™ on Stack Overflow

How to make text entry into table based on clicking an button in forms in Access 2010

1 Answer 1

7 Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

7 Comments

Related