Solved Lock form based on combobox (1 Viewer)

mishash

Member
Local time
Today, 17:08
Joined
Aug 20, 2020
Messages
52
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

Well-known member
Local time
Today, 11:08
Joined
Apr 9, 2015
Messages
4,339
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, 15:08
Joined
May 23, 2011
Messages
4,717
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

..forever waiting... waiting for jellybean!
Local time
Today, 23:08
Joined
May 7, 2009
Messages
19,169
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, 17:08
Joined
Aug 20, 2020
Messages
52
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, 17:08
Joined
Aug 20, 2020
Messages
52
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, 17:08
Joined
Aug 20, 2020
Messages
52
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, 15:08
Joined
May 23, 2011
Messages
4,717
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, 17:08
Joined
Aug 20, 2020
Messages
52
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, 15:08
Joined
May 23, 2011
Messages
4,717
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, 17:08
Joined
Aug 20, 2020
Messages
52
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, 17:08
Joined
Aug 20, 2020
Messages
52

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

..forever waiting... waiting for jellybean!
Local time
Today, 23:08
Joined
May 7, 2009
Messages
19,169
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, 15:08
Joined
May 23, 2011
Messages
4,717
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
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 23:08
Joined
May 7, 2009
Messages
19,169
3 is better than 2.
 

bob fitz

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

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 23:08
Joined
May 7, 2009
Messages
19,169
The code can be simplified to 2 subs.
 

Users who are viewing this thread

Top Bottom