Lock down records

[Caddie]

Attached is a screenshot of the form. As you can see at the top there is the customer information, then the contract details that has the status field in it. Each customer can have multiple contracts, some verified some not. The users use the arrow buttons to scroll through the different contracts, or the different service addresses of each contract and so on. If I put any code on the open event it will cause issues because user won't be able to scroll to the next contract or service address and so on.

Maybe this is something that I won't be able to do, based on the design of the form?

 

[dkinley]

That's why I switched it to the OnCurrent event for the current record which is what I should have proposed from the beginning. If the user is scrolling records then it should be ...

If Me.cmbComboBoxName = "Verified" Then 
      Me.AllowEdits = False
Else
      Me.AllowEdits = True
End If

Now, the "Verified" bit needs to be the value of the bound column of the combo box. So if the field actually stores an integer, then the condition will need to be = 1, say if 'verified' is stored as 1.

-dK

 

[vbaInet]

This looks like you have 3 subforms? I could be wrong because it would depend on how it was designed.

I was thinking you meant locking the Status control, so dkinley is pointing you in the right direction with his approach.

 

[Caddie]

This is definite progress in the right directions; however there are now 2 issues:

1 - If the status is "verified" on the contract details, I cannot edit the information on the contract details (great), but it still allows me to edit the Child forms of that verified contract. IE: Service address or Service Details or the contract. How do I make it so the records (can be multiple service addresses or service details for 1 verified contract) cannot be edited either.

2 - As mentioned above, if the combobox is set to verified then I can't make edits to the contract however, it won't let me change the combobox to say Pending either though. It actually lets me change it, but when I save the change and go back to that record it is still listed as Verified.

Any thoughts on either of these two issues?

Thanks for you time, it is much appreciated.

 

[dkinley]

Alright ... at least some forward progress!

1. You will need to add additional statements to change the disposition of the subforms, too.
Me.SubformName1.AllowEdits = False
Me.SubformName2.AllowEdits = False

2. You are not 'locking' the combo box. I think that even when the control is locked you can still click it - you are not able to change the underlying data.

Not knowing if there is a solution, my workaround in the past is that I have used two controls to faciliate this before. Depending on the status of the record (completed or not) I will set the visibility of the combo box and a regular control so that one shows or the other, thus no manipulation if in a completed state.

Here is where vbaInet was speaking of some sort of rollback method. The only thing I could think of was a password protection on the control or form or something. Like the OnClick event of the control, a button, on the forms OnOpen event with some sort of logic that went ...

If Me.AllowEdits = False Then
    ... perform password check and unlocking method here
End If

You can do a search on password protecting a form or a control and pick the best solution for you. I usually used the OnOpen event because I used the dual control method but it increased the complexity of the form having popups to determine if the user wants to change the form status and then checking to see if the correct password is entered. Not a showstopper, just some work.

-dK

 

[vbaInet]

Right, you should be locking the controls then:

Dim ctl As Control

If Me.cmbComboBoxName = "Verified" Then
    For Each ctl In Me
        If ctl.ControlType = acCheckBox Or ctl.ControlType = acTextBox  Or ctl.ControlType = acComboBox Then
            ctl.Enabled = False
        End If
    Next
Else
    For Each ctl In Me
        If ctl.ControlType = acCheckBox Or ctl.ControlType = acTextBox  Or ctl.ControlType = acComboBox Then
            ctl.Enabled = True
        End If
    Next
End If

If the user wants to amend a Verified record then they should click a button which will re-enable the controls. That would be the ELSE part you would need for the button.

You didn't confirm whether the there are subforms on the main form?

 

[Caddie]

Hi Guys -

For the time being I'm not going to worry about allowing the users to "UNVERIFY" once it's been verified. So, this is what I've done do far:

Private Sub Form_Current()
If Me.Combo39 = "Verified" Then
Me.AllowEdits = False
Me.Service_Addresss_Details.Form.AllowEdits = False
Else
Me.AllowEdits = True
Me.Service_Addresss_Details.Form.AllowEdits = True
End If
End Sub

This leaves me with one problem (that I'm aware of ) I have a subform inside the Service_Addresss_Details Form (called Service Details) as well. But I can't pass this command on to it. I tried to go to the "on current event" of the service details subform but it doesn't allow me to refer to the parent form.

 

[dkinley]

I would say it is a reference issue and you should be able to do it to the sub-subform in the same scope (that is in sequence with your existing code). You just need to reference it correctly.

Check out this link to get to the properties of that form in the same manner you did for the form and subform.

-dK

 

[Caddie]

You are bang on! What was throwing me off is that when you enter the data for the sub-subform the little drop down box that normally pops up doesn't but you can still enter code.

Now it looks like this:

Private Sub Form_Current()
Me.Service_Addresss_Details.Enabled = False
If Me.Combo39 = "Verified" Then
Me.AllowEdits = False
Me.Service_Addresss_Details.Form.AllowEdits = False
Me.Service_Addresss_Details.Form.Service_details.Form.AllowEdits = False
Else
Me.AllowEdits = True
Me.Service_Addresss_Details.Form.AllowEdits = True
Me.Service_Addresss_Details.Form.Service_details.Form.AllowEdits = True
End If
End Sub

Thanks ALL.

 

[dkinley]

You betcha.

Glad to get you where you needed to be.

Remember to look up password protecting forms/controls and you should find many solutions to choose from. I like vbaInet's proposal of adding a button somewhere that does some sort of pwd validation to unlock the form.

Good luck!
-dK

 

[Caddie]

Now we're talking!

Private Sub Combo39_Enter()
If Me.Status = "Verified" Then
Dim pass As String
pass = InputBox("Record locked, please enter password")
If pass <> "Welcome2" Then
MsgBox ("Invalid Password")
Exit Sub
Else
Me.AllowEdits = True
Me.Service_Addresss_Details.Form.AllowEdits = True
Me.Service_Addresss_Details.Form.Service_details.Form.AllowEdits = True
Me.Status.Value = "Pending"
Me.Contract__.SetFocus
End If
End If
End Sub

Works perfectly! Thanks All!