Solved

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

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

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

Secure Your Active Directory - April 20, 2017

Active Directory plays a critical role in your company’s IT infrastructure and keeping it secure in today’s hacker-infested world is a must.
Microsoft published 300+ pages of guidance, but who has the time, money, and resources to implement? Register now to find an easier way.

Question has a verified solution.

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

Suggested Solutions

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…
Access custom database properties are useful for storing miscellaneous bits of information in a format that persists through database closing and reopening.  This article shows how to create and use them.
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

749 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