Solved

Restricting form fields in MS Access 2007

Posted on 2014-04-30
10
658 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--
[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
  • 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
Transaction Monitoring Vs. Real User Monitoring

Synthetic Transaction Monitoring Vs. Real User Monitoring: When To Use Each Approach? In this article, we will discuss two major monitoring approaches: Synthetic Transaction and Real User Monitoring.

 

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
 

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

Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

Question has a verified solution.

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

AutoNumbers should increment automatically, without duplicates.  But sometimes something goes wrong, and the next AutoNumber value is a duplicate.  This article shows how to recover from this problem.
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…
Introduction to Processes
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …

707 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