Solved

Restricting form fields in MS Access 2007

Posted on 2014-04-30
10
656 Views
Last Modified: 2014-05-14
Hello,

I have a MS Access 2007 DB; in the forms, whenever the user saves/exits and brings the same information back up in the form, I want all of the populated fields to be disabled from editing.

Is this possible without restricting access to the empty fields?  I want the user to be able to edit those fields after the original fields are disabled.

Thanks
0
Comment
Question by:--TripWire--
  • 5
  • 5
10 Comments
 
LVL 28

Accepted Solution

by:
omgang earned 300 total points
ID: 40033016
Hoping I understood your question correctly.  The following procedure will lock form controls that have values but not lock form controls that are empty.  

OM Gang


Private Sub Form_Current()

On Error GoTo Err_Form_Current

    Dim ctl As Control
   
    For Each ctl In Me.Form.Controls

        Select Case ctl.ControlType
            Case 109, 106      '109 = TextBox; 106 = CheckBox
                If Nz(Me(ctl.Name), "") <> "" Then
                    Me(ctl.Name).Locked = True
                Else
                    Me(ctl.Name).Locked = False
                End If
               
            Case 111            '111 = ComboBox
                If Nz(Me(ctl.Name).Column(1), "") <> "" Then
                    Me(ctl.Name).Locked = True
                Else
                    Me(ctl.Name).Locked = False
                End If
           
            Case Else
           
        End Select
       


    Next
     
Exit_Form_Current:
    Set ctl = Nothing
   Exit Sub

Err_Form_Current:
   MsgBox Err.Number & " (" & Err.Description & ") in procedure Form_Current of VBA Document Form_frmCategory"
   Resume Exit_Form_Current
End Sub
0
 
LVL 28

Assisted Solution

by:omgang
omgang earned 300 total points
ID: 40033152
Forgot to include ListBox control

I also changed the column to be evaluated for ComboBox and ListBox as the columns are indexed from 0 and the first column is the one typically saved to the table.

            Case 110, 111            '110 = ListBox, 111 = ComboBox
                If Nz(Me(ctl.Name).Column(0), "") <> "" Then
                    Me(ctl.Name).Locked = True
                Else
                    Me(ctl.Name).Locked = False
                End If
           
            Case Else
           
        End Select
0
 

Author Closing Comment

by:--TripWire--
ID: 40033197
Thanks!
0
 

Author Comment

by:--TripWire--
ID: 40064777
I've noticed that some of the CheckBox fields will allow user interaction, while others will not.
If the CheckBox is blank on the form, I would the user to be able to check it.  But if it's already checked, I don't want the user to be able to uncheck it.  How do I go about this?
0
 
LVL 28

Expert Comment

by:omgang
ID: 40064863
I don't  think I tested the checkbox controls because the routine doesn't work as-is for them.

Here's updated sub that includes proper check for Checkbox control values - Checkbox is Boolean (Yes/No, True/False, -1/0)
OM Gang


Private Sub Form_Current()

On Error GoTo Err_Form_Current

    Dim ctl As Control
   
    For Each ctl In Me.Form.Controls
   
    'Debug.Print ctl.Name & " : " & ctl.ControlType

        Select Case ctl.ControlType
            Case 106            '106 = CheckBox
                If Me(ctl.Name) = 0 Then
                    Me(ctl.Name).Locked = False
                Else
                    Me(ctl.Name).Locked = True
                End If
               
            Case 109            '109 = TextBox
                If Nz(Me(ctl.Name), "") <> "" Then
                    Me(ctl.Name).Locked = True
                Else
                    Me(ctl.Name).Locked = False
                End If
               
            Case 110, 111            '110 = ListBox, 111 = ComboBox
                If Nz(Me(ctl.Name).Column(0), "") <> "" Then
                    Me(ctl.Name).Locked = True
                Else
                    Me(ctl.Name).Locked = False
                End If
           
            Case Else
           
        End Select
       
    Next
     
Exit_Form_Current:
    Set ctl = Nothing
   Exit Sub

Err_Form_Current:
   MsgBox Err.Number & " (" & Err.Description & ") in procedure Form_Current of VBA Document Form_frmCategory"
   Resume Exit_Form_Current
End Sub
0
Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

 

Author Comment

by:--TripWire--
ID: 40064932
For some reason it only works when I code it like this...

If Me(ctl.Name) = -1 Then
                    Me(ctl.Name).Locked = True
'skip else statement
End If

Open in new window


Not sure why.
Thanks anyway
0
 
LVL 28

Expert Comment

by:omgang
ID: 40064940
If you don't include the Else part then once the control is locked you have no means to unlock it.  That's why I evaluate the control each time and either Lock or Unlock.

OM Gang
0
 

Author Comment

by:--TripWire--
ID: 40064954
Yes, once the checkbox has been locked, I don't want it to be unchecked once you've closed the form.
0
 
LVL 28

Expert Comment

by:omgang
ID: 40064966
On my test form I am able to cycle through records so I set the procedure to run OnCurrent (each time the record changes) and lock/unlock controls each time.  If you're only working with one record when the form opens then your change will work just fine.
OM Gang
0
 

Author Comment

by:--TripWire--
ID: 40065018
You're right.
I was able to add the else without problem.  *fingers crossed*
0

Featured Post

U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

Question has a verified solution.

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

Suggested Solutions

If you’re thinking to yourself “That description sounds a lot like two people doing the work that one could accomplish,” you’re not alone.
Computer science students often experience many of the same frustrations when going through their engineering courses. This article presents seven tips I found useful when completing a bachelors and masters degree in computing which I believe may he…
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …
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 …

911 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

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now