Solved

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

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

Expert Comment

by:Rey Obrero
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 119

Accepted Solution

by:
Rey Obrero 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 34

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

Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

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…
Today's users almost expect this to happen in all search boxes. After all, if their favourite search engine juggles with tens of thousand keywords while they type, and suggests matching phrases on the fly, why shouldn't they expect the same from you…
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
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…

863 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

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now