Solved

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

Posted on 2016-10-11
4
51 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

Creating Instructional Tutorials  

For Any Use & On Any Platform

Contextual Guidance at the moment of need helps your employees/users adopt software o& achieve even the most complex tasks instantly. Boost knowledge retention, software adoption & employee engagement with easy solution.

Question has a verified solution.

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

AutoNumbers should increment automatically, without duplicates.  But sometimes something goes wrong, and the next AutoNumber value is a duplicate.  This article shows how to recover from this problem.
Microsoft Access is a place to store data within tables and represent this stored data using multiple database objects such as in form of macros, forms, reports, etc. After a MS Access database is created there is need to improve the performance and…
Familiarize people with the process of utilizing SQL Server views from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Access…
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…

705 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