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

Disabling combo box changes after selection

P: 28
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:
Expand|Select|Wrap|Line Numbers
  1. Private Sub PHIconsultantcombo_LostFocus()
  2. PHIconsultantcombo.Locked = (PHIconsultantcombo.Text <> "")
  3.  
  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
  4.  
  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
  10.  
  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
  18.  
  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


LBryant
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
  4.  
Would that work? It doesn't need to be inside the form_current event does it?
Jun 6 '08 #2

missinglinq
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 <> "")
  3.  
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

missinglinq
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?

Thanks.

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
  5.  
  6.  
  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
  12.  
  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
  20.  
  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
  29.  
Jun 10 '08 #6

missinglinq
Expert 2.5K+
P: 3,532
Change

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

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
  6.  
Linq ;0)>
Jun 10 '08 #7

P: 28
Thanks so much for your help missinglinq,

Your solution works perfectly.
Jun 11 '08 #8

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

Linq ;0)>
Jun 11 '08 #9

NeoPa
Expert Mod 15k+
P: 31,186
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.