Solved

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

Posted on 2016-10-11
4
47 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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 37

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

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

It’s the first day of March, the weather is starting to warm up and the excitement of the upcoming St. Patrick’s Day holiday can be felt throughout the world.
Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.

737 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