Solved

MS Access - Update record without closing Form

Posted on 2011-02-22
11
541 Views
Last Modified: 2012-06-27
My question seems basic but I've always let the form save changes to the current record by closing the form which is bound to a table.  Now I've been asked to save the changes on a button click that does not close the form.  Changes need to be saved along the way instead of waiting till the very end when the user is done with the form.

Is there an easier way than itemizing each field equal to the value on the form?  I know I can execute an SQL command UPDATE table SET..... or do a DAO openrecordset SELECT then set each field.....

But there are 27 fields on my form that may have been changed.
Thanks!
Helen
0
Comment
Question by:greenprg
  • 6
  • 3
  • 2
11 Comments
 
LVL 75

Accepted Solution

by:
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform) earned 500 total points
ID: 34958652
You can easily do this with a button on the form, with one line of code in the button Click event.

Private Sub btnSave_Click()
    Me.Dirty = False  ' save the record
End Sub

mx
0
 
LVL 19

Expert Comment

by:MINDSUPERB
ID: 34958655
Requery the form to reflect the changes of data would be a solution.

Me.requery

Sincerely,
Ed
0
 
LVL 75
ID: 34958659
Note that as soon as any Bound control on the Form is edited, the Form becomes 'Dirty'.  At this point ... if the Save button is clicked, the current changes will be Saved.  Continue to make edits on the same record, and Save again ... and so on.

mx
0
Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

 
LVL 75
ID: 34958669
Ed ... that is not really the best practice just to save a Record.  You Requery when you want to see changes made by other users on the same recordset.  While Requerying will save the record ... it is completely unnecessary.

mx
0
 

Author Closing Comment

by:greenprg
ID: 34958700
Perfect.  I knew there must be a simple command to do this.  Thanks for the explanation.  That really helps too.

Helen
0
 
LVL 75
ID: 34958710
You are welcome.

Are you on the West Coast ?

mx
0
 
LVL 19

Expert Comment

by:MINDSUPERB
ID: 34958711

@mx

I might missed the question. Thanks for the input.

Ed
0
 

Author Comment

by:greenprg
ID: 34958727
Yes.  I can be quite the night owl but I seem to get the most done after my kids are all in bed.  Thanks for the quick response.  I was afraid I was going to need to wait until morning to finish this up.
Helen
0
 
LVL 75
ID: 34958741
West coast where ... just curious.  LA area here.
" I was afraid I was going to need to wait until morning to finish this up."
nah ... 24x7x365 ... or so it seems :-)

mx
0
 

Author Comment

by:greenprg
ID: 34958756
Orange county.  Office is in Gardena so lucky me, I get to work from home and work whenever I want to.  
Helen
0
 
LVL 75
ID: 34958804
The OC ... really.  Small world ... How bout that ... I work for the power company that supplies your power :-)
Gardena ... nice you don't have to drive there every day ... 405 not pretty!
The IE (barely) ... Rancho Cucamonga ... just up the 57 to the 10 .

Time to zzzz out now ...

catch up with you later ...

===
0

Featured Post

Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

Question has a verified solution.

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

Introduction When developing Access applications, often we need to know whether an object exists.  This article presents a quick and reliable routine to determine if an object exists without that object being opened. If you wanted to inspect/ite…
A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
Familiarize people with the process of utilizing SQL Server stored procedures 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 Micr…
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…

808 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