By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
434,984 Members | 2,615 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 434,984 IT Pros & Developers. It's quick & easy.

Disabling combo box changes after selection

P: 28

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:
Expand|Select|Wrap|Line Numbers
  1. Private Sub PHIconsultantcombo_LostFocus()
  2. PHIconsultantcombo.Locked = (PHIconsultantcombo.Text <> "")
  4. End Sub
My current form_current event code looks like this:
Expand|Select|Wrap|Line Numbers
  1. Private Sub Form_Current()
  2. Me.RecordsetClone.MoveLast
  3. Me.RecordsetClone.MoveFirst
  5. If Me.Lead_State <> "WA" Then
  6. Me.Statewarning = Null
  7. Else
  8. Me.Statewarning = "Note: THC is not sold in WA"
  9. End If
  11. If Me.RecordsetClone.RecordCount = CurrentRecord Then
  12.     Me.but_next.Enabled = False
  13.     Me.but_last.Enabled = False
  14.     Else
  15.     Me.but_next.Enabled = True
  16.     Me.but_last.Enabled = True
  17. End If
  19. If CurrentRecord = 1 Then
  20.     Me.but_previous.Enabled = False
  21.     Me.but_first.Enabled = False
  22.     Else
  23.     Me.but_previous.Enabled = True
  24.     Me.but_first.Enabled = True
  25. End If
  26. End Sub
How do I add the lostfocus code to the current event form code? Or am I on completely the wrong track?
Jun 6 '08 #1
Share this Question
Share on Google+
9 Replies

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

Expand|Select|Wrap|Line Numbers
  1. Private Sub PHIconsultantcombo_AfterUpdate()
  2.      If PHIconsultantcombo.Text <> "" then = PHIconsultantcombo.Locked = True
  3. End Sub
Would that work? It doesn't need to be inside the form_current event does it?
Jun 6 '08 #2

Expert 2.5K+
P: 3,532
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)>
Jun 7 '08 #3

P: 28
Thanks missinglinq.

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

Expand|Select|Wrap|Line Numbers
  1. Private Sub PHIconsultantcombo_LostFocus()
  2. 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.
Jun 10 '08 #4

Expert 2.5K+
P: 3,532
I'd just place it as the first line, immediately following

Private Sub Form_Current()

Linq ;0)>
Jun 10 '08 #5

P: 28
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?


My new code is below

Expand|Select|Wrap|Line Numbers
  1. Private Sub Form_Current()
  2. PHIconsultantcombo.Locked = (PHIconsultantcombo.Value <> "")
  3. Me.RecordsetClone.MoveLast
  4. Me.RecordsetClone.MoveFirst
  7. If Me.Lead_State <> "WA" Then
  8. Me.Statewarning = Null
  9. Else
  10. Me.Statewarning = "Note: THC is not sold in WA"
  11. End If
  13. If Me.RecordsetClone.RecordCount = CurrentRecord Then
  14.     Me.but_next.Enabled = False
  15.     Me.but_last.Enabled = False
  16.     Else
  17.     Me.but_next.Enabled = True
  18.     Me.but_last.Enabled = True
  19. End If
  21. If CurrentRecord = 1 Then
  22.     Me.but_previous.Enabled = False
  23.     Me.but_first.Enabled = False
  24.     Else
  25.     Me.but_previous.Enabled = True
  26.     Me.but_first.Enabled = True
  27. End If
  28. End Sub
Jun 10 '08 #6

Expert 2.5K+
P: 3,532

Expand|Select|Wrap|Line Numbers
  1. PHIconsultantcombo.Locked = (PHIconsultantcombo.Value <> "")

Expand|Select|Wrap|Line Numbers
  1. If Not Me.NewRecord Then
  2. PHIconsultantcombo.Locked = Not IsNull(PHIconsultantcombo.Value)
  3. Else
  4. PHIconsultantcombo.Locked = False
  5. End If
Linq ;0)>
Jun 10 '08 #7

P: 28
Thanks so much for your help missinglinq,

Your solution works perfectly.
Jun 11 '08 #8

Expert 2.5K+
P: 3,532
Glad we could help!

Linq ;0)>
Jun 11 '08 #9

Expert Mod 15k+
P: 31,492
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).
Expand|Select|Wrap|Line Numbers
  1. Me.PHIconsultantcombo.Locked = _
  2.     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).
Jun 12 '08 #10

Post your reply

Sign in to post your reply or Sign up for a free account.