CODEWITHSUNDEEP
vbams-access
Josh Ellis
Josh Ellis

Reputation: 11

Microsoft Access VBA - Locking a field when value in the field is not null

I have fallen into a rut building a quote creation tool. I have a combo box that selects an item for the quote and will populate all of the fields for that line. In this case, I want the [Cost] field to be locked, preventing any changes. However, there are times when a user must type an item for the quote that is not in the combo box; therefore, all of the fields on that line will be null and must be typed manually.

I am trying to write a VBA event where [Cost] is locked when [Cost] is not null, preventing any change; and where [Cost] is not locked when [Cost] is Null, allowing editing.

Private Sub Form_Current()
If IsNull(Me.Cost) = True Then
    Me.Cost.Locked = False
Else
    Me.Cost.Locked = True
End If
End Sub

Design view of subform

Form view of complete form

Upvotes: 1

Views: 919

Answers (1)

June7
June7

Reputation: 21379

Sometimes code must be in multiple events to achieve desired result. Since you will programmatically populate Cost if an item is selected from combobox list, put code in form Current event and combobox AfterUpdate event. Could have a Sub behind form that can be called by both events.

Programmatically setting control's properties will affect ALL instances of control. An alternative is to use Conditional Formatting to dynamically enable/disable a textbox or combobox.

To allow user option to edit manually entered Cost value for a new record adds complication. VBA or Conditional Formatting rule could be conditional - don't disable if focus is on a new record and service item is not in lookup table.

Upvotes: 1

Related Questions