Solved

Restricting form fields in MS Access 2007

Posted on 2014-04-30
10
657 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
Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

 

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

Independent Software Vendors: 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

Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
This article describes a method of delivering Word templates for use in merging Access data to Word documents, that requires no computer knowledge on the part of the recipient -- the templates are saved in table fields, and are extracted and install…
In this fourth video of the Xpdf series, we discuss and demonstrate the PDFinfo utility, which retrieves the contents of a PDF's Info Dictionary, as well as some other information, including the page count. We show how to isolate the page count in a…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

733 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