Form that opens with no value breaks vba

[Ruegen]

I've got a form that opens with a date as the parameter, null date opens fine but if the form opens with an area that has no null date (which means there is no records with a null date and the form opens blank) then I get a vba error for this code



Function DaylightSwitch()





ElseIf Forms!frmSchools!State = "QLD" Then
Forms!frmSchools!TxtTime = DateAdd("h", 1, Time())




ElseIf Forms!frmSchools!State = "SA" Then
Forms!frmSchools!TxtTime = DateAdd("n", -30, Time())




ElseIf Forms!frmSchools!State = "WA" Then
Forms!frmSchools!TxtTime = DateAdd("h", 4, Time())




ElseIf Forms!frmSchools!State = "NT" Then
Forms!frmSchools!TxtTime = DateAdd("h", 3, Time())


Else: Forms!frmSchools!TxtTime = Time()


End If


End Function

I guess since the form hasn't come up with any results for "QLD" that breaks the vba - how do I get around this? Just make it ignore the error?

[ssanfu]

I guess since the form hasn't come up with any results for "QLD" that breaks the vba

You are missing the first part of the IF() statement.

Function DaylightSwitch()

     'delede the "Else"
'    ElseIf Forms!frmSchools!State = "QLD" Then

    If Forms!frmSchools!State = "QLD" Then
        Forms!frmSchools!TxtTime = DateAdd("h", 1, Time())

    ElseIf Forms!frmSchools!State = "SA" Then
        Forms!frmSchools!TxtTime = DateAdd("n", -30, Time())

    ElseIf Forms!frmSchools!State = "WA" Then
        Forms!frmSchools!TxtTime = DateAdd("h", 4, Time())

    ElseIf Forms!frmSchools!State = "NT" Then
        Forms!frmSchools!TxtTime = DateAdd("h", 3, Time())

    Else     ' don't use the colon (:) in the multi-line form of the IF() function.
        Forms!frmSchools!TxtTime = Time()

    End If

End Function

I would write the function as

Function DaylightSwitch(sState As String)

    If sState = "SA" Then
        Forms!frmSchools!TxtTime = DateAdd("n", -30, Time())

    ElseIf sState = "WA" Then
        Forms!frmSchools!TxtTime = DateAdd("h", 4, Time())

    ElseIf sState = "NT" Then
        Forms!frmSchools!TxtTime = DateAdd("h", 3, Time())

    Else 
        Forms!frmSchools!TxtTime = Time()

    End If

End Function

It would be called like:

=DaylightSwitch(Forms!frmSchools!State)



Where is this function - in the form module or a standard module? (form name??)
How are you calling the function?

[Ruegen]

Module

The Form_Timer() calls the module.

sorry I had an if before and I deleted it without updating my code

This is what i have

Function DaylightSwitch()

If Forms!frmSchools!State = "QLD" Then
Forms!frmSchools!TxtTime = DateAdd("h", 1, Time())




ElseIf Forms!frmSchools!State = "SA" Then
Forms!frmSchools!TxtTime = DateAdd("n", -30, Time())




ElseIf Forms!frmSchools!State = "WA" Then
Forms!frmSchools!TxtTime = DateAdd("h", 4, Time())




ElseIf Forms!frmSchools!State = "NT" Then
Forms!frmSchools!TxtTime = DateAdd("h", 3, Time())


Else: Forms!frmSchools!TxtTime = Time()


End If


End Function

[Ruegen]

The error occurs when the form I have has no​ records because of the parameters haven't pulled up anything - because the module relies on a record value it then breaks when there isn't one.

so if there is no record value "QLD" then it gets an error when I have pulled in an area in QLD

[ssanfu]

The Form_Timer() calls the module.

Strange...... I would have used the form open event, not the timer event.

Functions return a value, even if you do not use the returned value.
So two options...

Option 1:

Function DaylightSwitch()

    'set a default return value
    DaylightSwitch = #12:00:01 AM#

    If Forms!frmSchools!State = "QLD" Then
        Forms!frmSchools!TxtTime = DateAdd("h", 1, Time())

    ElseIf Forms!frmSchools!State = "SA" Then
        Forms!frmSchools!TxtTime = DateAdd("n", -30, Time())

    ElseIf Forms!frmSchools!State = "WA" Then
        Forms!frmSchools!TxtTime = DateAdd("h", 4, Time())

    ElseIf Forms!frmSchools!State = "NT" Then
        Forms!frmSchools!TxtTime = DateAdd("h", 3, Time())

    Else     ' do not use the colon (:) in a multi-line IF() function.
        Forms!frmSchools!TxtTime = Time()

    End If

End Function

Option 2:
Since you are not using the function to return a value, change the timer event code to call a sub...

Private Sub Form_Timer()
    Call DaylightSwitch
End Sub

Sub DaylightSwitch()

    If Forms!frmSchools!State = "QLD" Then
        Forms!frmSchools!TxtTime = DateAdd("h", 1, Time())

    ElseIf Forms!frmSchools!State = "SA" Then
        Forms!frmSchools!TxtTime = DateAdd("n", -30, Time())

    ElseIf Forms!frmSchools!State = "WA" Then
        Forms!frmSchools!TxtTime = DateAdd("h", 4, Time())

    ElseIf Forms!frmSchools!State = "NT" Then
        Forms!frmSchools!TxtTime = DateAdd("h", 3, Time())

    Else    ' do not use the colon (:) in a multi-line IF() function.
        Forms!frmSchools!TxtTime = Time()

    End If

End Sub