Solved

MS Access - Update record without closing Form

Posted on 2011-02-22
11
586 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
[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
  • 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
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!

 
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

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Question has a verified solution.

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

Suggested Solutions

In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
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…
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.

710 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