Solved Lock form based on combobox (1 Viewer)

mishash

Member
Local time
Today, 14:11
Joined
Aug 20, 2020
Messages
47
I am building a simple Order form with OrderDetails subform. I need all the controls on the main form (CustomerName, OrderDate etc) and on the subform (Quantity, Listprice etc) to become locked (changes disabled) once I select Closed status in the OrderStatus combobox of the main Order form (only OrderStatus combobox should remain active in case I decide to update the order anyway).
I guess it has something to do with AfterUpdate event of the OrderStatus combobox and Current event Me.AllowEdits = False of both forms.
My VBA (un)knowledge does not allow me to make it properly.
I would appreciate experts' help.
 

Ranman256

Registered User.
Local time
Today, 07:11
Joined
Apr 9, 2015
Messages
3,698
Check on 2 events

Code:
Sub LockForm()
Me.AllowEdits = cboStatus<>"Closed"
End sub


Sub form_onCurrent()
LockForm
End sub

Sub cboStatus_afterupdate()
LockForm
End sub
 

bob fitz

AWF VIP
Local time
Today, 12:11
Joined
May 23, 2011
Messages
4,161
Use code in the Main forms Current event and the combos AfterUpdate event.
Something like:
Me.NameOfSubform.Locked = Me.NameOfCombo = "Closed"

should lock the subform when "Closed" has been selected and unlock it if anything else is selected, assuming that the values of the combo are text values.
If you only have a small number of controls on the main form to deal with then use something like:
Me.ControlName.Locked = Me.NameOfCombo = "Closed"
for each of the controls

Obviously, you will need to substitute you form and control names in the expressions
 

arnelgp

error reading drive A:
Local time
Today, 20:11
Joined
May 7, 2009
Messages
11,146
on the Current Event of the Main Form:

private sub form_current()
With me.subformName.Form
.AllowEdits = (Nz(Me.comboName,"") <> "Closed")
.AllowAdditions = (Nz(Me.comboName,"") <> "Closed")
.AllowDeletions = (Nz(Me.comboName,"") <> "Closed")
end with
end sub


on the combo on Mainform, AfterUpdate

private sub combo_afterudate()
call form_current
end sub
 

mishash

Member
Local time
Today, 14:11
Joined
Aug 20, 2020
Messages
47
on the Current Event of the Main Form:

private sub form_current()
With me.subformName.Form
.AllowEdits = (Nz(Me.comboName,"") <> "Closed")
.AllowAdditions = (Nz(Me.comboName,"") <> "Closed")
.AllowDeletions = (Nz(Me.comboName,"") <> "Closed")
end with
end sub


on the combo on Mainform, AfterUpdate

private sub combo_afterudate()
call form_current
end sub

Thank you for your solution. Unfortunately it locks only the subform. I need to lock both Mainform and Subform except for the combobox control itself.
 

mishash

Member
Local time
Today, 14:11
Joined
Aug 20, 2020
Messages
47
Did you try the solution that I offered in post #3

Dear Bob, thank you for your reply.
I was looking for more systematic solution for the Mainform (not locking every control separately). I will try your solution again.
Also, comboboxes on the subform, although disabled for updates, still open its value lists (not completely frozen).
I've been trying to find the solution in MS Northwind DB (in Orders form once an order is invoiced, all the controls become completely frozen), but could not find the relevant code.
 

mishash

Member
Local time
Today, 14:11
Joined
Aug 20, 2020
Messages
47
Check on 2 events

Code:
Sub LockForm()
Me.AllowEdits = cboStatus<>"Closed"
End sub


Sub form_onCurrent()
LockForm
End sub

Sub cboStatus_afterupdate()
LockForm
End sub

Thank you for your reply. Something is missing. Should I change the AllowEdits property of the Mainform to No first?
 

bob fitz

AWF VIP
Local time
Today, 12:11
Joined
May 23, 2011
Messages
4,161
Dear Bob, thank you for your reply.
I was looking for more systematic solution for the Mainform (not locking every control separately). I will try your solution again.
Also, comboboxes on the subform, although disabled for updates, still open its value lists (not completely frozen).
I've been trying to find the solution in MS Northwind DB (in Orders form once an order is invoiced, all the controls become completely frozen), but could not find the relevant code.
Perhaps:
Me.NameOfSubform.Enabled = Me.NameOfCombo <>"Closed"

For the main form, if you want something more automated then I would suggest you give a value to the tag property of each control to be Enabled/Disabled. Loop through the forms controls and set the Enabled property accordingly.

Whether you automate the process or write one line of code for each control AFAIK there is no other way that would not also affect the combo box
 

mishash

Member
Local time
Today, 14:11
Joined
Aug 20, 2020
Messages
47
Perhaps:
Me.NameOfSubform.Enabled = Me.NameOfCombo <>"Closed"

For the main form, if you want something more automated then I would suggest you give a value to the tag property of each control to be Enabled/Disabled. Loop through the forms controls and set the Enabled property accordingly.

Whether you automate the process or write one line of code for each control AFAIK there is no other way that would not also affect the combo box

Thank you again. I guess I should add some code in the Combo AfterUpdate or in subform Requery so that Enable\Disable will apply with immediate effect, without refreshing the form?
 

bob fitz

AWF VIP
Local time
Today, 12:11
Joined
May 23, 2011
Messages
4,161
Thank you again. I guess I should add some code in the Combo AfterUpdate or in subform Requery so that Enable\Disable will apply with immediate effect, without refreshing the form?
You just need the code in the forms OnCurrent event and in the AfterUpdate event of the combo box.

Another way that would be favoured by many would be to put the code in a Sub Procedure (perhaps called "SetCtrlsEnabledProp") and call that procedure in the forms OnCurrent event and the Combos AfterUpdate event.
The advantage is that you then have only one procedure to change if the db is developed in the future. For example, you add another control to one of the forms. If you have the code duplicated in two events you would need to change it in both.
 

mishash

Member
Local time
Today, 14:11
Joined
Aug 20, 2020
Messages
47
You just need the code in the forms OnCurrent event and in the AfterUpdate event of the combo box.

Another way that would be favoured by many would be to put the code in a Sub Procedure (perhaps called "SetCtrlsEnabledProp") and call that procedure in the forms OnCurrent event and the Combos AfterUpdate event.
The advantage is that you then have only one procedure to change if the db is developed in the future. For example, you add another control to one of the forms. If you have the code duplicated in two events you would need to change it in both.
 

mishash

Member
Local time
Today, 14:11
Joined
Aug 20, 2020
Messages
47

I've managed with this:

Code:
Private Sub Form_Current()
If Me.OrderStatusID = 5 Then
    Me.OrderDate.Enabled = False
    Me.CustomerID.Enabled = False
    Me.TransactionTypeID.Enabled = False
    Me.PaymentTypeID.Enabled = False
    Me.OrderNotes.Enabled = False
    Me.InvoiceNumber.Enabled = False
    Me.ReceiptNumber.Enabled = False
    Me.frmOrderDetailsSubfrm.Enabled = False
Else
    Me.OrderDate.Enabled = True
    Me.CustomerID.Enabled = True
    Me.TransactionTypeID.Enabled = True
    Me.PaymentTypeID.Enabled = True
    Me.OrderNotes.Enabled = True
    Me.InvoiceNumber.Enabled = True
    Me.ReceiptNumber.Enabled = True
    Me.frmOrderDetailsSubfrm.Enabled = True
End If
End Sub

Private Sub OrderStatusID_AfterUpdate()
Me.Requery
End Sub



Don't know if it is optimal, but this is what I needed.
Thank you very much!
 

arnelgp

error reading drive A:
Local time
Today, 20:11
Joined
May 7, 2009
Messages
11,146
You do not requery the form when the status change.
It will bring new record and you will be back to the first record on the form.

You just Call the form_current.
 

bob fitz

AWF VIP
Local time
Today, 12:11
Joined
May 23, 2011
Messages
4,161
IMHO the following would be better:
Code:
Private Sub Form_Current()
    Call SetCtrlProps
End Sub

Private Sub SetCtrlProps()
    Me.OrderDate.Enabled = Me.OrderStatusID <> 5
    Me.CustomerID.Enabled = Me.OrderStatusID <> 5
    Me.TransactionTypeID.Enabled = Me.OrderStatusID <> 5
    Me.PaymentTypeID.Enabled = Me.OrderStatusID <> 5
    Me.OrderNotes.Enabled = Me.OrderStatusID <> 5
    Me.InvoiceNumber.Enabled = Me.OrderStatusID <> 5
    Me.ReceiptNumber.Enabled = Me.OrderStatusID <> 5
    Me.frmOrderDetailsSubfrm.Enabled = Me.OrderStatusID <> 5
End Sub


Private Sub OrderStatusID_AfterUpdate()
    Call SetCtrlProps
End Sub
 

bob fitz

AWF VIP
Local time
Today, 12:11
Joined
May 23, 2011
Messages
4,161
I missed post #14 by arnelgp which I'm sure would work.
 

arnelgp

error reading drive A:
Local time
Today, 20:11
Joined
May 7, 2009
Messages
11,146
The code can be simplified to 2 subs.
 

Users who are viewing this thread

Top Bottom