We help IT Professionals succeed at work.

Check out our new AWS podcast with Certified Expert, Phil Phillips! Listen to "How to Execute a Seamless AWS Migration" on EE or on your favorite podcast platform. Listen Now

x

VBA Code to save and/or modify record in access

Lawrence Salvucci
on
Medium Priority
1,005 Views
Last Modified: 2012-05-11
I know MS Access saves a record when you close a form but I want to make my user form more controlled. I want to have a series of buttons like below:

Save Button - Save's record and writes it to the table
Modify Button - Need to click modify in order to make changes to the record
Undo Button - Undo changes made before saving the record
Close Button - If the record was changed before you close the form make it prompt the user to save or close and undo changes.

The key to this is to have the record locked so you cannot edit it without clicking the modify button first. So I would need to add VBA code to lock the form and only be able to change the data when you click the modify button.

Any ideas on how I can set this up? Or perhaps someone has a sample with this type of functionality in it
Comment
Watch Question

CERTIFIED EXPERT
Most Valuable Expert 2014
Commented:
Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Application Developer
CERTIFIED EXPERT
Top Expert 2007

Commented:
The simplest, easiest and fastest way to lock/unlock all controls (except the button that controls this) is as follows:

Add a tab control to your form with the following properties:

One Page (tab)
Style = None
Back Style = Transparent

Put all of the controls on this tab - except a command button do the locking/unlocking.
You can now enable / disable the tab control - and subsequently all controls on it with one line of code

Then add a button, maybe in the Form Header - anywhere except on the tab, as follows

Private Sub btnModify_Click()
    Me.YourTabControlName.Enabled = True  ' False
End Sub
This is VERY fast also.
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Application Developer
CERTIFIED EXPERT
Top Expert 2007

Commented:
@Nick67:
Re "You can use a button that does me.locked = true"
There is no Locked property for a Form itself (Me).

re:"Hide the normal close 'x'"
This is contrary to normal best practices UI design.  User *expect* the Close button to be available.

Also, I disagree with using the wizards.  The OP will never learn best practices coding or an understanding of what is going on by using wizards.

Save Button

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

Undo Button

Private Sub btnUndo_Click()
     ' optional message '' Are you sure ...?"
    Me.Undo
End Sub

mx
CERTIFIED EXPERT
Most Valuable Expert 2014

Commented:
<There is no Locked property for a Form itself (Me).>
You're right, my bad

But now, how to intercept and cancel a close if me.dirty = true.
Every test I just did with a shim db, the 'x' forced a save, and closed
Which is NOT the OP's desire

Short of replacing the 'x' how can that be altered?

And the wizards--they have their place.  I learned code from examining what the wizards coded and extending it.
Recording macros in Excel was useful too.

If you NEVER look behind what the event is, then you learn nothing.
That'd be macros in Access, something virtually no one uses or should use
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Application Developer
CERTIFIED EXPERT
Top Expert 2007

Commented:
"Short of replacing the 'x' how can that be altered?"
There are a couple of ways, but ... I need to zzzz now.
Back tomorrow ...

mx
Lawrence SalvucciDirector of Information Technology

Author

Commented:
Why not just disable the "x"?

@Nick67:

Is there an easier way to lock all control than having to list them all in code? I have quite a few controls so there has to be an easier way to write that in vb code.

'change .ownerID etc to match the controls YOU want locked or unlocked
    With Me
        .OwnerID.Locked = myboolean
        .CustJobNumber.Locked = myboolean
        .ConsigneeID.Locked = myboolean
        .InsTypeID.Locked = myboolean
        .InsLocationID.Locked = myboolean
        .cmdInspectors.Enabled = Not (myboolean)
        .[Date].Locked = myboolean
        .[WO#].Locked = myboolean
        .[PO#].Locked = myboolean
    End With
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Application Developer
CERTIFIED EXPERT
Top Expert 2007

Commented:
"Why not just disable the "x"?"

This is contrary to normal best practices UI design.  User *expect* the Close button to be available.
I mentioned this @ http:#a35430580

"Is there an easier way to lock all control than having to list them all in code? I have quite a few controls so there has to be an easier way to write that in vb code."

I explained a much simpler way @ http:#a35430545  ... using a Tab control.

Perhaps you missed my posts?

mx
CERTIFIED EXPERT
Most Valuable Expert 2014

Commented:
There is:

Private Sub cmdChange_Click()
Dim ctl As Control
Dim myboolean As Boolean

If cmdChange.Caption = "Change details" Then
    myboolean = False
    cmdChange.Caption = "EDITING"
Else
    myboolean = True
    cmdChange.Caption = "Change details"
End If

For Each ctl In Me.Controls
    If (ctl.ControlType = acTextBox Or ctl.ControlType = acCheckBox Or ctl.ControlType = acComboBox) = True Then
        ctl.Locked = myboolean
    End If
Next ctl
End Sub
 

Look here near the bottom for bits about control enumeration
https://www.experts-exchange.com/Microsoft/Development/MS_Access/Q_26918640.html
Lawrence SalvucciDirector of Information Technology

Author

Commented:
My apologies. I must have overlooked your close button post. I've trained my users to use the close button on my form instead of the "x". So I've disabled my "x" button on my form. They have a tendency to hit the "x" on the entire database by accident all the time so I just disabled them all. I could turn them back on but then I would need to add code to the form close event in order to catch if any changes were made without saving.

I did read your approach by using the tab form and that would work for me. I will try that approach. I also did some research today and found this code that uses the "tag" property on each control.


Public Sub FormLock(frmForm As Form, booLock As Boolean)
   Dim ctrl As Control
   For Each ctrl In frmForm
       If ctrl.Tag = "Lock" Then
           ctrl.Locked = booLock
       End If
   Next
End Sub




If you put "lock" in each of them then I can lock them using the following code in the "on open" event of the form.

FormLock Me, True

The problem with this is if there are openargs coming from another form via the "on load" event of the form then for some reason it skips the code I mentioned about in the "on open" event. So I'm not 100% sure I can get this to work correctly.
CERTIFIED EXPERT
Most Valuable Expert 2014

Commented:
@mx

How do you get around the problem of the 'x' forcing a save and then closing without allowing the code to check if the user intended the dirty close to be saved?
I just couldn't get that to happen.  If the x was used, it saved the record and bailed.  Is there a way around that, short of removing the 'x'?
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Application Developer
CERTIFIED EXPERT
Top Expert 2007

Commented:
"code that uses the "tag" property on each control. "
I personally do not use the Tag for this sort of thing.  It's just way too easy to accidentally blow out the value in the Tag property during design/development.  But hey, that's just me.  I don't see it as 'safe and reliable'

@Nick ... I will try to post that tonight.

mx
CERTIFIED EXPERT
Most Valuable Expert 2014

Commented:
I'm not big on 'tag' either.
Naming conventions have uses

Public Sub FormLock(frmForm As Form, booLock As Boolean)
   Dim ctrl As Control
   For Each ctrl In frmForm
       If ctrl.Name like "*Lock*" Then
           ctrl.Locked = booLock
       End If
   Next
End Sub
Lawrence SalvucciDirector of Information Technology

Author

Commented:
@Nick67

What is the difference between this code that you posted earlier:

For Each ctl In Me.Controls
    If (ctl.ControlType = acTextBox Or ctl.ControlType = acCheckBox Or ctl.ControlType = acComboBox) = True Then
        ctl.Locked = myboolean
    End If
Next ctl
End Sub


To the code you just posted:

Public Sub FormLock(frmForm As Form, booLock As Boolean)
   Dim ctrl As Control
   For Each ctrl In frmForm
       If ctrl.Name like "*Lock*" Then
           ctrl.Locked = booLock
       End If
   Next
End Sub

Do they do the same thing?
CERTIFIED EXPERT
Most Valuable Expert 2014

Commented:
The top snippet will lock all textboxes, checkboxes and comboboxes only.
Listboxes and other control types aren't included

The second snippet, adapted from your own post, would lock any control that had 'lock' as part of its name.
So three controls on a form

txtInspectorIDLock
cboJobs
lstNamesLock

The first and last would lock, the middle one would not.

Other ways to utiltize this:
I always leave my bound controls named the way Access chooses (usually the bound field name)
My unbound controls, I use hungarian notation (txt, cbo, lst chk)
This would lock everything EXCEPT my unbound controls

Public Sub FormLock(frmForm As Form, booLock As Boolean)
   Dim ctrl As Control
   For Each ctrl In frmForm
        select case true            
            case ctrl.Name like "txt*" 'do nothing
            case ctrl.Name like "cbo*" 'do nothing
            case ctrl.Name like "lst*" 'do nothing
            case else
                 ctrl.Locked = booLock
       End select
   Next ctrl
End Sub

Catch my drift?
Lawrence SalvucciDirector of Information Technology

Author

Commented:
Yes, I understand now. Thank you. Still digesting everything and trying to sum it all up on how I should proceed.
Lawrence SalvucciDirector of Information Technology

Author

Commented:
Thanks for the help. It's working exactly how I want it to.
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Application Developer
CERTIFIED EXPERT
Top Expert 2007

Commented:
:-)
CERTIFIED EXPERT
Most Valuable Expert 2014

Commented:
Hey @mx

<How do you get around the problem of the 'x' forcing a save and then closing without allowing the code to check if the user intended the dirty close to be saved?>

Nick
Unlock the solution to this question.
Thanks for using Experts Exchange.

Please provide your email to receive a sample view!

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.