Help with Before Update Event please

[JohnB]

Hi. I have the following code in the 'Before Update Event' of combo
cboSchoolName. The message appears OK if I try to select a value in
cboSchoolName before selecting one in cboPlacementStage but after clicking on
OK, the user is still then able to select a value in cboSchoolName. What am
I doing wrong?

Thanks for any help, JohnB



If IsNull(Me!cboPlacementStage) Then
MsgBox "You must make a selection in the 'Placement Stage' field first"
Cancel = True
End If

 

[SusanV]

Hi John,

I would enable / disable cboSchoolName based on cboPlacementStage having a
value:

Me.cboSchoolName.Disabled = Is Null(Me.cboPlacementStage)

Of course, this assumes that cboPlacementStage does not have a default
value, and will be Null until the user selects something...

 

[JohnB]

Thanks Susan.

Where do I put your code? For info, the combos are on a subform and the
mainform is on a Tab Control forms page.

JohnB

 

[SusanV]

Sorry that air code is no good. Bah, bad day today! I don't know WHAT I was
thinking (me.control.disabled doesn't even *exist* for Pete's sake!)

Ran through it in a sample db I have and this will definitely work:

On the Subform's On Activate event:
Me.LOM.Enabled = False
End Sub


On the control cboPlacementStage's AfterUpdate event:
If Not IsNull(Me.cboPlacementStage) Then
Me.cboSchoolName.Enabled = True
Else: Me.cboSchoolName.Enabled = False
End If
End Sub



Again my apologies, and I hope this actually DOES help <abashed grin>

Susan

 

[SusanV]

Whoops, that should have read:

On the Subform's On Activate event:
Me.cboSchoolName.Enabled = False
End Sub


On the control cboPlacementStage After Update event:
If Not IsNull(Me.cboPlacementStage) Then
Me.cboSchoolName.Enabled = True
Else: Me.cboSchoolName.Enabled = False
End If
End Sub

 

[JohnB]

Hi. No worries - I'll believe anything you say!

Still a bit puzzled. What does LOM mean in your first set of code? Is this
meant to disable cboSchoolName? If so, how does it work? Also, is the
subforms On Activate event triggered when the form/subform is presented when
the appropriate Tab is clicked? (In the past I've used On Current for this
type of thing - just checking). Lastly, the second line of your second set
of code seems redundant - cboPlacementStage will always have content after
it is updated (or is there a different situation that could occur?)

As you might realise from my replies, I'm away from my mdb at present so
can't try the code out. It will be tomorrow before I can.

Many thanks for the help. JohnB

 

[JohnB]

Oops, our posts are bumping into each other! OK, got that but is the On
Active event the correct one and what about that second line of 'redundant'
code.

Cheers, JohnB

 

[SusanV]

That was pasted form my sample DB, sorry! should have been
Me.cboPlacementStage.Enabled, as I stated in my hastily sent Whoops!
response

 

[SusanV]

You could also use OnCurrent of the subform.

The "redundant" code in the AfterUpdate event is in case the user sets
something in the cboPlacementStage then changes their mind and deletes the
entry. For true error correction in this case, this line should be added to
the Else:
me.cboSchoolName = vbNullString, deleting the School as well as the
Placement info. So...

Me.cboSchoolName = " "
Me.cboSchoolName.Enabled = False
That should do the trick.

 

[JohnB]

Thanks Susan. That clarifies a lot. One last point - does your code:

Else:
Me.cboSchoolName = " "

put 'something' in cboSchoolName? If so, perhaps

Else:
Me.cboSchoolName = Null

would be better. I'm thinking of having some other code in the subform that
will test for content in cboScholName, so when it looks empty I want it to
be really empty. I'm thinking zero length strings
versus Null here.

Thanks again, JohnB

 

[JohnB]

Hi again. Just thought I'd let you know that your code works perfectly. I
used Null instead of " ". Also, I reposted about the text in cboSchoolName
being going light grey when it is disabled - the reply came back to look at
setting it to Locked rather than Disabled. I'll look into that and see what
it does. Thanks again for all your help. JohnB

 

[SusanV]

Glad to help! Personally I prefer to set controls to disabled, graying them
out, so users don't call me to say that their keyboard is broken simply
because a control won't take data <grin>