If... VBA code

[AndyCompanyZ]

I have a piece of code (shown below) that is supposed to look at a field on a form (Me.ScheduledNumber) and compare it to another field (Me.CourseMInDel and Me.CourseMaxDel) and compare the values and give a message to the user if the results are, in the first case less than the CourseMInDel and in the second more than CourseMaxDel. the messagebox comes up for the first one always even if the number is more than the CourseMInDel. I have run through the code in the debugger and it shows the values which are correct from the form i.e. for instance 18 for ScheduledNumber and 14 for CourseMInDel but when I step through the code it runs the message and then exits the code (which it should if they were correct). If I have more than the CourseMaxDel in the
ScheduledNumber it still shows the first message.

Private Sub EventDelegateListFinalised_Click()
If (Me.EventDelegateListFinalised.Value = -1) And (Me.ScheduledNumber.Value < Me.CourseMInDel.Value) Then
    MsgBox "There are less than the minimum amount of delegates scheduled on this event.Please check before finalising"
Exit Sub
If (Me.EventDelegateListFinalised.Value = -1) And (Me.ScheduledNumber.Value > Me.CourseMaxDel.Value) Then
    MsgBox "There are too many delegates scheduled on this event. Please check before finalising"
Exit Sub
End If
End If
DoCmd.OpenQuery "EventUpdate", acViewNormal, acEdit
End Sub

 

[bob fitz]

Hi

You may like to try this:

Private Sub EventDelegateListFinalised_Click()
If (Me.EventDelegateListFinalised.Value = -1) And (Me.ScheduledNumber.Value < Me.CourseMInDel.Value) Then
    MsgBox "There are less than the minimum amount of delegates scheduled on this event.Please check before finalising"
   Exit Sub
ElseIf (Me.EventDelegateListFinalised.Value = -1) And (Me.ScheduledNumber.Value > Me.CourseMaxDel.Value) Then
    MsgBox "There are too many delegates scheduled on this event. Please check before finalising"
   Exit Sub
End If
 
DoCmd.OpenQuery "EventUpdate", acViewNormal, acEdit
 
End Sub

 

[AndyCompanyZ]

Thanks Bob but that doesn't work I had tried something similar It still only goes to the first option even if the ScheduledNumber is higher than CourseMInDel

 

[stopher]

what results do you get in the immediate window when you put this as your first line of code:

debug.print Me.EventDelegateListFinalised.Value, Me.ScheduledNumber.Value, Me.CourseMInDel.Value

Chris

 

[AndyCompanyZ]

I get the values from the form which are correct

 

[stopher]

And they are....

 

[AndyCompanyZ]

-1 19 12 in the instance I tested it on. which should have flagged the second message byt brought up the first message.

 

[stopher]

Maybe you can post your database?

 

[AndyCompanyZ]

I can put the relevant part up I'll just strip it down abit.

 

[AndyCompanyZ]

Problem is I use 2010

 

[VilaRestal]

Your original code definitely won't work as proper indenting will show:

Private Sub EventDelegateListFinalised_Click()
    If (Me.EventDelegateListFinalised.Value = -1) And (Me.ScheduledNumber.Value < Me.CourseMInDel.Value) Then
        MsgBox "There are less than the minimum amount of delegates scheduled on this event.Please check before finalising"
        Exit Sub
        If (Me.EventDelegateListFinalised.Value = -1) And (Me.ScheduledNumber.Value > Me.CourseMaxDel.Value) Then
            MsgBox "There are too many delegates scheduled on this event. Please check before finalising"
            Exit Sub
        End If
    End If
    DoCmd.OpenQuery "EventUpdate", acViewNormal, acEdit
End Sub

The second If statement is only parsed if the first if statement is true and won't ever get parsed because of the first Exit Sub statement.

Bob's revision of it should fix that.
You said you'd tried something similar.
Did you actually try that code?

 

[AndyCompanyZ]

Yes I tried the revision with the elseif in it but I can see now it is indented the problem. I am going to have a play around for a bit with it thanks everyone....I'll be back (probably)

 

[AndyCompanyZ]

OK I have tried using the code above I have it here again:

Private Sub EventDelegateListFinalised_Click()
If (Me.EventDelegateListFinalised.Value = -1) And (Me.ScheduledNumber < Me.CourseMInDel.Value) Then
MsgBox "There are less than the minimum amount of delegates scheduled on this event.Please check before finalising"
Exit Sub
ElseIf (Me.EventDelegateListFinalised.Value = -1) And (Me.ScheduledNumber.Value > Me.CourseMaxDel.Value) Then
MsgBox "There are too many delegates scheduled on this event. Please check before finalising"
Exit Sub
End If
 
DoCmd.OpenQuery "EventUpdate", acViewNormal, acEdit
 
End Sub

But now it says I have an invalid . or ! on the (Me.ScheduledNumber < Me.CourseMInDel.Value) line but it worked OK before but now if I debug.print it it says "there is an invalid use of the . or ! operator".

 

[VilaRestal]

I notice the first if has Me.ScheduledNumber but the second has Me.ScheduledNumber.Value. The second is more likely to work (the first refers to the control not its value)

Me.control.value is subtly different to Me!field (if the control is named the same as the field). Generally, Me!field is more reliable because it doesn't depend on the name of the control - it accesses the recordset's field value rather than the slightly more roundabout way of going the control's value.

So, I'd try changing the code to:

Private Sub EventDelegateListFinalised_Click()
    If (Me!EventDelegateListFinalised = -1) And (Me!ScheduledNumber < Me!CourseMInDel) Then
        MsgBox "There are less than the minimum amount of delegates scheduled on this event.Please check before finalising"
        Exit Sub
    ElseIf (Me!EventDelegateListFinalised = -1) And (Me!ScheduledNumber > Me!CourseMaxDel) Then
        MsgBox "There are too many delegates scheduled on this event. Please check before finalising"
        Exit Sub
    End If

    DoCmd.OpenQuery "EventUpdate", acViewNormal, acEdit

End Sub

 

[AndyCompanyZ]

I think that has it thanks a lot.

 

[amrizvi]

Try this way

If (Me!EventDelegateListFinalised = -1) And (Me!ScheduledNumber < Me!CourseMInDel) Then
MsgBox "There are less than the minimum amount of delegates scheduled on this event.Please check before finalising"
Exit Sub
Endif

If (Me!EventDelegateListFinalised = -1) And (Me!ScheduledNumber > Me!CourseMaxDel) Then
MsgBox "There are too many delegates scheduled on this event. Please check before finalising"
Exit Sub
End If

DoCmd.OpenQuery "EventUpdate", acViewNormal, acEdit