Solved

Add a record to a table and confirm it was added - MS Access

Posted on 2016-10-11
4
40 Views
Last Modified: 2016-10-11
I have an Access database with a form that has 3 textboxes (textbox1, textbox2, textbox3) and a command button.

I have an Access table (MyTable) with 3 fields (Field1, Field2, Field3).

I need the code that will write (i.e. add a record) the textbox data to the table (textbox1 goes to Field1, textbox2 goes to Field2, etc) when the command button is clicked and I also need to confirm that the record was actually added.

Thank you.
0
Comment
Question by:dbfromnewjersey
  • 2
4 Comments
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 41838628
it will be a lot easier if you will bind your form to the table, i.e.,
-set the Record Source of the form to MyTable
- set the textboxes Control Source to  Field1, Field2, Field3 respectively

- you can then use the command button to  move the form to New record, the form will be ready to take a new input

place this code in the click event of the command button

private sub button_Click()
docmd.GoToRecord,,acNewRec

end sub
0
 
LVL 120

Accepted Solution

by:
Rey Obrero (Capricorn1) earned 500 total points
ID: 41838642
if you really want to use codes, try this

private sub button_click()
dim rs as dao.recordset
set rs=currentdb.openrecordset("MyTable")

with rs
     .addnew
     !Field1=Me.textbox1
     !Field2=Me.textbox2
     !Field3=Me.textbox3
     .update
end with

if dcount("*","MyTable", "Field1=" & me.textbox1 & " and Field2=" & me.textbox2 & " and Field3=" & me.textbox3) >0 then
   msgbox "Record added"
end if

end sub


that is assuming all fields are Number Data type
if there Text Data type like Field1, the syntax will be  "Field1='" & me.textbox1 & "'
0
 
LVL 35

Expert Comment

by:PatHartman
ID: 41839014
How is this question different from the earlier one where you marked Crystal's answer as correct?
0
 
LVL 31

Expert Comment

by:Helen_Feddema
ID: 41839017
Also -- it will be very helpful in future if you give your fields, controls and forms (and other objects) meaningful names.  The Leszynski Naming Convention (LNC) is generally used.  I have created free add-ins to semi-automatically apply the appropriate LNC prefixes to database objects and controls -- here are some links (there are different versions for different Access versions):

LNC Rename add-in (Access 2000-2003)
http://www.helenfeddema.com/Files/code10.zip
http://en.wikipedia.org/wiki/Leszynski_naming_convention


LNC Rename add-in (Access 2007-2010)
Controls only:
http://www.helenfeddema.com/Files/code63.zip
Objects and Controls:
http://www.helenfeddema.com/Files/code63.zip
0

Featured Post

Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

In the previous article, Using a Critera Form to Filter Records (http://www.experts-exchange.com/A_6069.html), the form was basically a data container storing user input, which queries and other database objects could read. The form had to remain op…
Preparing an email is something we should all take special care with – especially when the email is for somebody you may not know very well. The pressures of everyday working life stacked with a hectic office environment can make this a real challen…
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

825 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question