type mismatch on cancel

[edo janssen]

When I cancel the inputbox of if i leave the inputbox empty and click on OK I get a type mismatch error. The field OBNVac is a currency field.

Who can help me out?

[/SIZE][/FONT][FONT=Arial][SIZE=2]Dim Controle As Integer
Dim sInputE As Currency
Controle = DLookup("OBNVac", "tblHeffingTarief", "Jaar = " & "[Tekst0]")
If Controle = "0" Then
Inputbox:
    sInputE = Inputbox("Het tarief voor de vacatiegelden van " & Me.Tekst0 & " is" & vbCrLf & _
    "nog niet ingevuld. Geef het bedrag op!", "Tarief vacatiegeld OBN")
    DoCmd.RunSQL "Update tblHeffingTarief SET OBNVac='" & sInputE & "' WHERE Jaar=" & Me.Tekst0 & ";"
    If sInputE = vbCancel Then
        sInputE = 0
        Exit Sub

[/SIZE][/FONT] 
[FONT=Arial][SIZE=2]

 

[pbaldy]

You didn't mention where the error is, but I see three things right off. If the variable is an Integer, this test will never be met:

If Controle = "0" Then

you want

If Controle = 0 Then

Also, if the DLookup() returns a Null (like when it can't find the value) you'll get an error because Integer can't accept a Null. You can either declare the variable as Variant or wrap your DLookup() in an Nz() function. Also, I'm pretty sure that the user cancelling the InputBox will return a ZLS, so try

If sInputE = vbNullString Then

and you probably want that test before you use the variable in the SQL (I guess that's 4 things).

 

[edo janssen]

Thanks Paul, this problem is solved!

 

[pbaldy]

Happy to help!