MJH MJH - 1 year ago 70
SQL Question

How to Get around null received from a bound form

I have a parent form with a button. The button's event is VBA code, that calls a function. The function needs to receive some fields from the child form.

button event:

Private Sub btn_Click()
Call myFunction(childForm!contactId, childForm!companyId, _
childForm!lastName, childForm!firstName, childForm!email)
End Sub

The problem is, that sometime some or all of the fields are null, and then I get an error message.


Public Sub myFunction(ByVal contactId As Integer, ByVal companyId As Integer, _
ByVal lastName As String, ByVal firstName As String, ByVal email As String)

Is there a way to take care of this aside from writing a long piece of code with conditions and variables?

if isnull(childForm!contactId) then
strContactId = ""
End If

Answer Source

You can use the Nz Function:

    Private Sub btn_Click()
        Call myFunction(Nz(childForm!contactId,0), Nz(childForm!companyId,0), _
        Nz(childForm!lastName,""), Nz(childForm!firstName,""), Nz(childForm!mail,"")) 
    End Sub
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download