MsgBox code error... (1 Viewer)

[Ironis]

Hey

I've got a msgbox code error, which I can't figure out.. Can anyone help me?

here's the code:

Private Sub E9AMLess6()
    Dim space, enc, swim, enc2 As Integer
    Dim Response As VbMsgBoxResult
    Dim Msg, Msgs, Msgss, Title, Titles, Titless, Style, Styles
    
    space = 18 - (sumEnc9AML + sumEnc9AMT)
    enc = (sumEnc9AML + sumEnc9AMT)
    swim = (sumSwim9AML + sumSwim9AMT)
    enc2 = (sumEnc9AML + sumEnc9AMT + txtNumPers)
    
    Msg = "There are " & space & " spaces available." & _
            " There are " & enc & " Encounters and " & swim & " Swims." & _
            "Please try another day or time."
    Style = vbOKOnly
    Title = "Reservation Error"
    
    Msgs = "Are you sure you want to complete this reservation?"
    Titles = "Complete Reservation?"
    Styles = vbOKCancel + vbCritical + vbDefaultButton1
    
    Msgss = "Reservation successful." & _
            " Now there are " & enc2 & " spaces for the Encounter left."
    Titless = "Reservation Successful!"
    If (sumEnc9AML + sumEnc9AMT + txtNumPers) > 18 Then
        If MsgBox(Msg, Style, Title, "", "") = vbOKOnly Then DoCmd.DoMenuItem acFormBar, acEditMenu, acUndo, , acMenuVer70
    Else
        Response = MsgBox(Msgs, Styles, Titles, "", "")
        If Response = vbCancel Then
            DoCmd.DoMenuItem acFormBar, acEditMenu, acUndo, , acMenuVer70
            MsgBox "Reservation Cancelled", vbOKOnly, "Cancelled", "", ""
        Else
            MsgBox Msgss, "", Titless, "", ""
            DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70
        End If
    End If
End Sub

When I run the code, the system gives a Type Mismatch error...

Can anyone plz help me out??
Thanks in advance,

Dennis (Ironis 8) )

 

[pdx_man]

On what line?

 

[Ironis]

    Else
>>>>   Response = MsgBox(Msgs, Styles, Titles, "", "") <<<<
            If Response = vbCancel Then
            DoCmd.DoMenuItem acFormBar, acEditMenu, acUndo, , acMenuVer70
            MsgBox "Reservation Cancelled", vbOKOnly, "Cancelled", "", ""
        Else
            MsgBox Msgss, "", Titless, "", ""
            DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70
        End If
    End If
End Sub

The line between the >>> <<<
Maybe it's the 'Dim Response as vbMsgBoxResult' ?

 

[pdx_man]

Dimension Response as datatype Variant. VbMsgBoxResult is an enumeration of constants, not a datatype.

Dim Response as Variant

 

[Ironis]

Oke, this doesn't work.. I still get the error when running the code..

 

[jjturner]

Several notes:

1) space, enc , and swim are being declared as Variants by default

2) If any of your form fields that you're using to derive values for these variables are Null , this will propagate through your calculations for them, returning Null (a Null in the calculation for enc2 will error out to 'Invalid use of Null')

3) Your last argument in your MsgBox functions must be numeric as per the Access help files - by putting an empty string in this argument, you've created a type mismatch. Leave the last two arguments blank since you're not using them:

Response = MsgBox(Msgs, Styles, Titles)

Regards,
John

 

[Ironis]

So what you mean is that space, enc and swim should be declared as integers?

The 'Invalid use of Null' error is being ignored by a simple code:
On error goto err
>>Calculaton
exit sub
err:
end sub

This is how I ignore the invalid use of null. When the calculation is null, the result will be '0', and can be used in the calculations.

And the last argument... now i c that i got a type mismatch.. I'm going to test it now, and then check back for the result.

Thanks for the help so far..

 

[R. Hicks]

Response should be Dim'd as an Integer.

"Space" is a "Reserved Word" in Access ... for the function Space()

Also .....
You can not Dim the variable as you are doing. At least you are not getting what you think you are.

Dim space, enc, swim, enc2 As Integer

In the line above ... only "enc2" will be delared as an Integer.
All of the other variables will be "Variants".

It should be:
Dim space As Integer, enc As Integer, swim As Integer, enc2 As Integer

Or .... Like this:

Dim space As Integer
Dim enc As Integer
Dim swim As Integer
Dim enc2 As Integer

This holds true in the other lines where you have not declared the variables. If you do not declare the datatype ... it will assume the Variant dataype.

I can't say that what I have posted will clear up your problem ... but it's a place to start.

HTH
RDH

 

[Ironis]

Well, my code works now. It was the "" in the piece of code for the msgbox. Everything works perfectly now. I removed the whole 'Response' thing, and 'space' works perfectly, but I will rename it, to avoid troubles in the future.

I declared all the things you said as variant, and they just work perfecly. Only their outcome is a integer tho, so I will dim them as integer, it might help speeding up a little.

The rest works just fine, and again, thank you for all your help!

Cya, Dennis (Ironis )