Solved Lock form based on combobox

[mishash]

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.

I am sorry, lost both of you. I am not familiar with VBA jargon "You just Call the form_current" - what it means?
What should I use instead of requery in order not to be thrown to the 1st record?

 

[bob fitz]

The code to set the control properties was put in the forms OnCurrent event. So arnelgp was suggesting to call that event (make it run) from the combos After Update event.

 

[mishash]

The code to set the control properties was put in the forms OnCurrent event. So arnelgp was suggesting to call that event (make it run) from the combos After Update event.

how?

 

[bob fitz]

Call Form_Current
The word Call is optional

 

[mishash]

IMHO the following would be better:

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

Error message "Invalid use of NULL"

 

[bob fitz]

On which line does the code break

 

[mishash]

Call Form_Current
The word Call is optional

Bob, I appreciate your help, but it does not help
What code and where should I put instead of AfterUpdate Requery?

 

[mishash]

On which line does the code break

On opening the mainform
Private Sub SetCtrlProps()
Me.OrderDate.Enabled = Me.OrderStatusID <> 5

 

[bob fitz]

Replace:

    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

With:

    Me.OrderDate.Enabled = Nz(Me.OrderStatusID,0) <> 5
    Me.CustomerID.Enabled = Nz(Me.OrderStatusID,0) <> 5
    Me.TransactionTypeID.Enabled = Nz(Me.OrderStatusID,0) <> 5
    Me.PaymentTypeID.Enabled = Nz(Me.OrderStatusID,0) <> 5
    Me.OrderNotes.Enabled = Nz(Me.OrderStatusID,0) <> 5
    Me.InvoiceNumber.Enabled = Nz(Me.OrderStatusID,0) <> 5
    Me.ReceiptNumber.Enabled = Nz(Me.OrderStatusID,0) <> 5
    Me.frmOrderDetailsSubfrm.Enabled = Nz(Me.OrderStatusID,0) <> 5

 

[mishash]

Replace:

    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

With:

    Me.OrderDate.Enabled = Nz(Me.OrderStatusID,0) <> 5
    Me.CustomerID.Enabled = Nz(Me.OrderStatusID,0) <> 5
    Me.TransactionTypeID.Enabled = Nz(Me.OrderStatusID,0) <> 5
    Me.PaymentTypeID.Enabled = Nz(Me.OrderStatusID,0) <> 5
    Me.OrderNotes.Enabled = Nz(Me.OrderStatusID,0) <> 5
    Me.InvoiceNumber.Enabled = Nz(Me.OrderStatusID,0) <> 5
    Me.ReceiptNumber.Enabled = Nz(Me.OrderStatusID,0) <> 5
    Me.frmOrderDetailsSubfrm.Enabled = Nz(Me.OrderStatusID,0) <> 5

It would be 4th change of the code.I need to stop somewhere
Is there any way to stay with the original approach

If Me.OrderStatusID = 5 Then
    Me.OrderDate.Enabled = False
    Me.frmOrderDetailsSubfrm.Enabled = False
....
Else
    Me.OrderDate.Enabled = True
    Me.frmOrderDetailsSubfrm.Enabled = True
End If

and just replace Me.Requery in combo AfterUpdate with something that will refresh the form on current record and won't throw me to the 1st record?

 

[bob fitz]

It would be 4th change of the code.I need to stop somewhere
Is there any way to stay with the original approach

IMHO the time to stop is when you have a satisfactory solution
As I said earlier and as has been shown here, the original approach simply won't work.
I don't really understand where the problem is in cut/paste a few lines of code but I'm happy to stop now if you no longer want to pursue this.

 

[mishash]

IMHO the time to stop is when you have a satisfactory solution
As I said earlier and as has been shown here, the original approach simply won't work.
I don't really understand where the problem is in cut/paste a few lines of code but I'm happy to stop now if you no longer want to pursue this.

Your original code was something I was able to understand code-wise, so I was hoping there is some simple tweak to make it work. I prefer to understand the advised code, not to just copy-past it. Calling user-defined functions is not something I am familiar with, so I could not even understand the error message.
I did copy-past your last suggestion and it seems to work. Thank you, I appreciate your time and patience!

 

[bob fitz]

Your original code was something I was able to understand code-wise, so I was hoping there is some simple tweak to make it work. I prefer to understand the advised code, not to just copy-past it. Calling user-defined functions is not something I am familiar with, so I could not even understand the error message.
I did copy-past your last suggestion and it seems to work. Thank you, I appreciate your time and patience!

I'm glad that we have found a solution
I completely agree that it is always better to understand the code you are offered.
If there is anything about the code that you would like an explanation of, please post back.