?
Solved

VBA Code to save and/or modify record in access

Posted on 2011-04-19
18
Medium Priority
?
702 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
0
Comment
Question by:Lawrence Salvucci
  • 7
  • 6
  • 5
18 Comments
 
LVL 26

Accepted Solution

by:
Nick67 earned 2000 total points
ID: 35430506
This is doable, but gets very involved.
The save button is easy--add a command button and follow the wizard for record|save
The undo button is easy--add a command button and follow the wizard for record|undo
The close button is almost as easy--add a command button and follow the wizard.
Hide the normal close 'x'
Then in the Form Close event you need to add some code

Dim response As Integer
If Me.Dirty Then  'the record is unsaved
    response = MsgBox("You haven't saved the record.  Do so?", vbYesNoCancel)
    Select Case response
        Case vbYes
            Me.Dirty = False 'saves
        Case vbNo
            Me.Undo 'undoes all changes
        Case vbCancel
            Exit Sub 'cancels the close event
    End Select
End If

Modify is trickier
You can use a button that does me.locked = true
But then EVERYTHING -- including unbound controls is locked

Create a button cmdChange.
Put this code in and modify it according to your need

Dim myboolean As Boolean

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

'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
0
 
LVL 75
ID: 35430545
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.
0
 
LVL 75
ID: 35430580
@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
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 26

Expert Comment

by:Nick67
ID: 35430658
<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
0
 
LVL 75
ID: 35430736
"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
0
 
LVL 1

Author Comment

by:Lawrence Salvucci
ID: 35437408
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
0
 
LVL 75
ID: 35437423
"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
0
 
LVL 26

Expert Comment

by:Nick67
ID: 35437449
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
http://www.experts-exchange.com/Microsoft/Development/MS_Access/Q_26918640.html
0
 
LVL 1

Author Comment

by:Lawrence Salvucci
ID: 35437456
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.
0
 
LVL 26

Expert Comment

by:Nick67
ID: 35437462
@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'?
0
 
LVL 75
ID: 35437480
"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
0
 
LVL 26

Expert Comment

by:Nick67
ID: 35437519
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
0
 
LVL 1

Author Comment

by:Lawrence Salvucci
ID: 35437526
@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?
0
 
LVL 26

Expert Comment

by:Nick67
ID: 35437564
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?
0
 
LVL 1

Author Comment

by:Lawrence Salvucci
ID: 35437583
Yes, I understand now. Thank you. Still digesting everything and trying to sum it all up on how I should proceed.
0
 
LVL 1

Author Closing Comment

by:Lawrence Salvucci
ID: 35453292
Thanks for the help. It's working exactly how I want it to.
0
 
LVL 75
ID: 35453555
:-)
0
 
LVL 26

Expert Comment

by:Nick67
ID: 35453568
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
0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Did you know that more than 4 billion data records have been recorded as lost or stolen since 2013? It was a staggering number brought to our attention during last week’s ManageEngine webinar, where attendees received a comprehensive look at the ma…
Explore the ways to Unlock VBA Project Password Excel 2010 & 2013 documents. Go through the article and perform the steps carefully to remove VBA Excel .xls file.
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…
Suggested Courses

840 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