Disabling combo box changes after selection

[dozingquinn]

Hello,

I have a combo box [PHIconsultantcombo] on a form that I would like to 'lock' the selection of after the user makes a selection, then either moves to the next record or saves the form. This selection will therefore be unable to be changed afterwards.

From my research, I'm assuming that the following code needs to be placed into form_current event somehow:

Private Sub PHIconsultantcombo_LostFocus()
PHIconsultantcombo.Locked = (PHIconsultantcombo.Text <> "")
 
End Sub

My current form_current event code looks like this:

Private Sub Form_Current()
Me.RecordsetClone.MoveLast
Me.RecordsetClone.MoveFirst
 
If Me.Lead_State <> "WA" Then
Me.Statewarning = Null
Else
Me.Statewarning = "Note: THC is not sold in WA"
End If
 
If Me.RecordsetClone.RecordCount = CurrentRecord Then
    Me.but_next.Enabled = False
    Me.but_last.Enabled = False
    Else
    Me.but_next.Enabled = True
    Me.but_last.Enabled = True
End If
 
If CurrentRecord = 1 Then
    Me.but_previous.Enabled = False
    Me.but_first.Enabled = False
    Else
    Me.but_previous.Enabled = True
    Me.but_first.Enabled = True
End If
End Sub

How do I add the lostfocus code to the current event form code? Or am I on completely the wrong track?

[LBryant]

If it's a bound control, use AfterUpdate rather than LostFocus

Private Sub PHIconsultantcombo_AfterUpdate()
     If PHIconsultantcombo.Text <> "" then = PHIconsultantcombo.Locked = True
End Sub
 

Would that work? It doesn't need to be inside the form_current event does it?

[missinglinq]

To begin with you need to use the .Value property, not the .Text property.Text is only valid when the control has focus.

And yes, it does have to be in the OnCurrent event! Anytime you set some kind of formatting or properties (such as colors, visibility, enabling) in the AfterUpdate event of a control, you also have to include the code in the form's OnCurrent event. Otherwise, if you move from a record where, for example, a combobox is enabled, to a record where it should be disabled, it will inappropriately be enabled in the second record!

Linq ;0)>

[dozingquinn]

Thanks missinglinq.

Just confirming - I therefore need to use the following code:

Private Sub PHIconsultantcombo_LostFocus()
PHIconsultantcombo.Locked = (PHIconsultantcombo.Value <> "")
 

Since I'm an Access Newbie, how would I best incorporate this into my current event code shown in my question?

Thanks again.

[missinglinq]

I'd just place it as the first line, immediately following

Private Sub Form_Current()

Linq ;0)>

[dozingquinn]

Hi again,

I've now placed the code at the top of form_current. This has managed to prevent combo box changes being made to existing records. However when I go to create a new record I'm met with the error:

Run-time error 94: Invalid use of Null

I select debug and the 'PHIconsultantcombo.Locked' line is highlighted as being the section of code causing the problem.

Is there anyway to overcome this and ensure that any combobox value can be selected for a newly created record?

Thanks.

My new code is below

Private Sub Form_Current()
PHIconsultantcombo.Locked = (PHIconsultantcombo.Value <> "")
Me.RecordsetClone.MoveLast
Me.RecordsetClone.MoveFirst
 
 
If Me.Lead_State <> "WA" Then
Me.Statewarning = Null
Else
Me.Statewarning = "Note: THC is not sold in WA"
End If
 
If Me.RecordsetClone.RecordCount = CurrentRecord Then
    Me.but_next.Enabled = False
    Me.but_last.Enabled = False
    Else
    Me.but_next.Enabled = True
    Me.but_last.Enabled = True
End If
 
If CurrentRecord = 1 Then
    Me.but_previous.Enabled = False
    Me.but_first.Enabled = False
    Else
    Me.but_previous.Enabled = True
    Me.but_first.Enabled = True
End If
End Sub
 

[missinglinq]

Change

PHIconsultantcombo.Locked = (PHIconsultantcombo.Value <> "")
 

to

If Not Me.NewRecord Then
PHIconsultantcombo.Locked = Not IsNull(PHIconsultantcombo.Value)
Else
PHIconsultantcombo.Locked = False
End If
 

Linq ;0)>

[dozingquinn]

Thanks so much for your help missinglinq,

Your solution works perfectly.

[missinglinq]

Glad we could help!

Linq ;0)>

[NeoPa]

I like the way you assign a boolean result to the boolean attribute, and also that you noticed the use of the [ CODE ] tags after your first post and used them subsequently :)

For your current logic, a single line, similar to your first will work (although Linq's code works perfectly too of course).

Me.PHIconsultantcombo.Locked = _
    Not (IsNull(Me.PHIconsultantcombo) Or Me.NewRecord)

I would question the logic of locking the ComboBox after updates (to the control) though, and suggest that it should (possibly - only you know if this makes better sense) lock it only for saved records (with a selected value obviously). To enable this logic, simply use the same code, but only in the OnCurrent event procedure (not in AfterUpdate).