Solved

MS Access - Update record without closing Form

Posted on 2011-02-22
11
649 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
Ransomware: The New Cyber Threat & How to Stop It

This infographic explains ransomware, type of malware that blocks access to your files or your systems and holds them hostage until a ransom is paid. It also examines the different types of ransomware and explains what you can do to thwart this sinister online threat.  

 
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

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!

Question has a verified solution.

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

Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
If you need a simple but flexible process for maintaining an audit trail of who created, edited, or deleted data from a table, or multiple tables, and you can do all of your work from within a form, this simple Audit Log will work for you.
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …

626 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