Option Group/ Message Boxes

[DGagnon819]

Hi All,
I'm posting this code for a AfterUpdate event hoping that a resident expert can give me some guidance. I never took VB in college and my skills are very crude to say the least.

On my form I have a Option Group and it is used to populate text boxes that are normally hidden. The values of these text boxes are then used by a ComboBox query to populate a list. In cases where the query does not return a value for the ComboBox I want a MsgBox to give the user instructions.

The Option Group seems to work just fine. It's only when the user selects a option where a null value exists that I'm having problems.

How can I get the MsgBox to correctly display?

Private Sub TrainingType_AfterUpdate()
    
    ClockNumber.Requery
    If TrainingType.Value = 1 Then
    JobCode.Value = Null
    ProjectMandatoryCode.Value = "000ZZ"
    ITTCode.Value = Null
    DepartmentCode.Value = Null
    TrainingEventName.Enabled = True
    DateCompleted.Enabled = True
        
    ElseIf TrainingType.Value = 2 Then
    JobCode.Value = ClockNumber.Column(4)
    ProjectMandatoryCode.Value = Null
    ITTCode.Value = Null
    DepartmentCode.Value = Null
    TrainingEventName.Enabled = True
    DateCompleted.Enabled = True
            
    ElseIf TrainingType.Value = 3 Then
    JobCode.Value = Null
    ProjectMandatoryCode.Value = Null
    DepartmentCode.Value = Null
    ITTCode.Value = ITTPortalCode.Value
    TrainingEventName.Enabled = True
    DateCompleted.Enabled = True
    
    ElseIf TrainingType.Value = 4 Then
    JobCode.Value = Null
    ProjectMandatoryCode.Value = Null
    ITTCode.Value = Null
    DepartmentCode.Value = CrossTrainingCode.Value
    TrainingEventName.Enabled = True
    DateCompleted.Enabled = True
    
    ElseIf TrainingType.Value = 3 And IsNull(ITTPortalCode.Value) Or ITTPortalCode.Value = "" Then
    JobCode.Value = Null
    ProjectMandatoryCode.Value = Null
    DepartmentCode.Value = Null
    ITTCode.Value = ITTPortalCode.Value
    TrainingEventName.Enabled = False
    DateCompleted.Enabled = False
    
    MsgBox "This Employee is not enrolled in ITT Portal Training." & _
           vbCrLf & "You must enroll this employee in ITT Portal Training before events can be recorded." & _
           vbCrLf & "Please use the Edit Employee Information form to enroll this employee in ITT Portal Training.", vbOKOnly
    
    ElseIf TrainingType.Value = 4 And IsNull(CrossTrainingCode.Value) Or CrossTrainingCode.Value = "" Then
    JobCode.Value = Null
    ProjectMandatoryCode.Value = Null
    ITTCode.Value = Null
    DepartmentCode.Value = CrossTrainingCode.Value
    TrainingEventName.Enabled = False
    DateCompleted.Enabled = False
    
    MsgBox "This Employee is not enrolled in Cross Training." & _
           vbCrLf & "You must enroll this employee in Cross Training before events can be recorded." & _
           vbCrLf & "Please use the Edit Employee Information form to enroll this employee in Cross Training.", vbOKOnly
    
    
    End If
    
End Sub

I am attaching a picture of my form in case it helps. It will show the text boxes that I'm referring to. Respectfully,
D.Gagnon

 

[Lister]

Without running to deep into the in's and out's of you code it could be as simple as testing for Null at the start of the event.
For example

If IsNull(Me.YourFieldName) Then
msgbox "You have a null value, what do you want to do about it?", vbInformation, "Null Value"
End If

See if that is any help to you

 

[DGagnon819]

Dear Lister,
Thank you for taking the time to respond to my message. I understand your point about testing for a Null condition but the actual implementation escapes me. The reason is because the option group tests for different conditions and the Null condition is just one of several that need to be tested for.

Further complicating the issue for me is the fact that two option group options need to test for a IsNull and a Is Not Null condition. Like I said before, I'm not a VB expert by any means.

The option group works and either inputs or retrieves the correct values. But, where the two specific values that I mentioned earlier IsNull then I need the msgboxes to appear. I was working on it earlier and I got the msgbox to appear but after clicking the 'Ok' button the second msgbox immediately appeared after it.

Thank you for your assistance,
D.Gagnon

 

[DGagnon819]

Bump. I know my problem is a syntax issue and any help from you VB experts would be greatly appreciated.

D.Gagnon

 

[gemma-the-husky]

can you put these tests in the before update test for the group instead of the after update.

then you can inform the user, and cancel the update - and either make him pick another option, or set the option group to some further "indeterminate response" - you may need another option group setting for this.

After the user deals with the problem he can pick another option, and you can manage what he is doing if the option group is still set to the "position o/s" setting