Invalud Use of Null

E

Emma Aumack

Can someone tell me why I get an "Invalid Use of Null" Error here?

Private Sub CBO_BPV_CONTACT_3_DblClick(Cancel As Integer)
On Error GoTo Err_cbo_BPV_Contact_3_DblClick


Dim stDocName As String
Dim stLinkCriteria As String
Dim MyContactOpenArgs As String

MyContactOpenArgs = Me!BPV_Contact3_ID

stDocName = "Frm_NewContacts"
stLinkCriteria = "[Contact_ID] =" & Me![BPV_Contact3_ID]

If IsNull(Me!BPV_Contact3_ID) Then

DoCmd.OpenForm stDocName, acNormal, , , acFormAdd, acDialog,
MyContactOpenArgs

Else

DoCmd.OpenForm stDocName, acNormal, , stLinkCriteria, acFormEdit,
acDialog

End If

Exit_cbo_BPV_Contact_3_DblClick:
Exit Sub

Err_cbo_BPV_Contact_3_DblClick:
msgbox Err.Description
Resume Exit_cbo_BPV_Contact_3_DblClick
End Sub
 
G

George Nicholson

MyContactOpenArgs = Me!BPV_Contact3_ID
stLinkCriteria = "[Contact_ID] =" & Me![BPV_Contact3_ID]

If Me![BPV_Contact3_ID] is Null then both the above would generate an error
because you can't assign Null to a variable that has been declared as a
string.

Several ways to handle this. Here's one:
(Generally, if you need to reference a control or field multiple times, try
to do it just once and capture the value and handle the possibilty of Null
at the same time.)

Dim lngContact as Long

lngContact = nz(Me!BPV_Contact3_ID,0)
stDocName = "Frm_NewContacts"

If lngContact = 0 Then
' Field was Null
DoCmd.OpenForm stDocName, acNormal, , , acFormAdd, acDialog,
lngContact(??)
Else
stLinkCriteria = "[Contact_ID] =" & lngContact
DoCmd.OpenForm stDocName, acNormal, , stLinkCriteria, acFormEdit,
acDialog
End If

--
HTH,
George


Emma Aumack said:
Can someone tell me why I get an "Invalid Use of Null" Error here?

Private Sub CBO_BPV_CONTACT_3_DblClick(Cancel As Integer)
On Error GoTo Err_cbo_BPV_Contact_3_DblClick


Dim stDocName As String
Dim stLinkCriteria As String
Dim MyContactOpenArgs As String

MyContactOpenArgs = Me!BPV_Contact3_ID

stDocName = "Frm_NewContacts"
stLinkCriteria = "[Contact_ID] =" & Me![BPV_Contact3_ID]

If IsNull(Me!BPV_Contact3_ID) Then

DoCmd.OpenForm stDocName, acNormal, , , acFormAdd, acDialog,
MyContactOpenArgs

Else

DoCmd.OpenForm stDocName, acNormal, , stLinkCriteria, acFormEdit,
acDialog

End If

Exit_cbo_BPV_Contact_3_DblClick:
Exit Sub

Err_cbo_BPV_Contact_3_DblClick:
msgbox Err.Description
Resume Exit_cbo_BPV_Contact_3_DblClick
End Sub
 
O

Ofer Cohen

I think that the logic behind your (If statement) is wrong,
you sending an OpenArgs parameter if the Me!BPV_Contact3_ID is empty, which
doesn't make sense.

Try:

Private Sub CBO_BPV_CONTACT_3_DblClick(Cancel As Integer)
On Error GoTo Err_cbo_BPV_Contact_3_DblClick


Dim stDocName As String
Dim stLinkCriteria As String
Dim MyContactOpenArgs As String

stDocName = "Frm_NewContacts"

If IsNull(Me!BPV_Contact3_ID) Then
MyContactOpenArgs = "Apply here a different value to the openArgs or
remove it, there s no reason sending empty value"
DoCmd.OpenForm stDocName, acNormal, , , acFormAdd, acDialog,
MyContactOpenArgs

Else
stLinkCriteria = "[Contact_ID] =" & Me![BPV_Contact3_ID]
DoCmd.OpenForm stDocName, acNormal, , stLinkCriteria, acFormEdit,
acDialog

End If

Exit_cbo_BPV_Contact_3_DblClick:
Exit Sub

Err_cbo_BPV_Contact_3_DblClick:
msgbox Err.Description
Resume Exit_cbo_BPV_Contact_3_DblClick
End Sub
 
E

Emma Aumack

That was my original thought and maybe I didn't explain well enough in my
original post.

This is what I am trying to do:

I have two forms. Frm_ActMstr and Frm_NewCont. On Frm_ActMstr there are 3
hidden fields BPV_Contact1_ID, BPV_Contact2_ID, BPV_Contact3_ID, that are
used to populate contact information fields on Frm_ActMstr.

Originally, a user would enter the contact information via the
frm_NewContact by clicking on an "Add contact" button that opens
frm_NewContact, enter contact info, then close Frm_NewContact. On close
frm_ActMstr.cbo_BPV_Contact1, 2 and 3 are requeried. Then using a combobox
that holds the new contact name, select the newly added contact thereby
connecting the New Contact to Frm_ActMstr.

Unfortunately, requirements have changed and now all contacts must be
connected to and account on Frm_ActMstr. In order to do this, depending on
the users to make that connection is not realistic. So what needs to happed
is, when a user wants to add a new contact, they double click on the
BPV_Contact1, or BPV_Contact2, or BPV_Contact3 combobox field which would
open frm_NewContact and then enter their contact information.

When they close frm_NewContact, the newly entered contact information would
populate the appropriate frm_ActMstr.BPV_Contact1_ID,
frm_ActMstr.BPV_Contact2_ID, or frm_ActMstr.BPV_Contact3_ID (Depending on
which one they double clicked to begin with), thereby populating the other
contact information fields on frm_ActMstr.

In addition, if the user double-clicks on a contact name in
Frm_ActMster.ContactName that is already populated, I want them to be able to
edit existing contact information (this part is relatively easy to do with
the docmd.open form).

Tbl_Contacts does not have a FK_field but tbl_ActMstr has corresponding
FK_BPV_Contact1_ID, FK_BPV_Contact2_ID, FK_BPV_Contact3_ID where the
Contact_ID is stored.

I think using open args is the way to go, I just don't understand how to get
the contact_ID into the APPROPRIATE BPV_Contact1_ID, BPV_Contact2_ID or
BPV_Contact3_ID. That is where I think the suggestion came to pass the null
value to frm_NewContacts. In order to get the correct field name to pass the
Contact_ID back to?

I hope I've been clearer this time. It seems like the solution shouldn't be
this hard for me.

Thanks for your help!

--
www.bardpv.com
Tempe, Arizona


Ofer Cohen said:
I think that the logic behind your (If statement) is wrong,
you sending an OpenArgs parameter if the Me!BPV_Contact3_ID is empty, which
doesn't make sense.

Try:

Private Sub CBO_BPV_CONTACT_3_DblClick(Cancel As Integer)
On Error GoTo Err_cbo_BPV_Contact_3_DblClick


Dim stDocName As String
Dim stLinkCriteria As String
Dim MyContactOpenArgs As String

stDocName = "Frm_NewContacts"

If IsNull(Me!BPV_Contact3_ID) Then
MyContactOpenArgs = "Apply here a different value to the openArgs or
remove it, there s no reason sending empty value"
DoCmd.OpenForm stDocName, acNormal, , , acFormAdd, acDialog,
MyContactOpenArgs

Else
stLinkCriteria = "[Contact_ID] =" & Me![BPV_Contact3_ID]
DoCmd.OpenForm stDocName, acNormal, , stLinkCriteria, acFormEdit,
acDialog

End If

Exit_cbo_BPV_Contact_3_DblClick:
Exit Sub

Err_cbo_BPV_Contact_3_DblClick:
msgbox Err.Description
Resume Exit_cbo_BPV_Contact_3_DblClick
End Sub

--
Good Luck
BS"D


Emma Aumack said:
Can someone tell me why I get an "Invalid Use of Null" Error here?

Private Sub CBO_BPV_CONTACT_3_DblClick(Cancel As Integer)
On Error GoTo Err_cbo_BPV_Contact_3_DblClick


Dim stDocName As String
Dim stLinkCriteria As String
Dim MyContactOpenArgs As String

MyContactOpenArgs = Me!BPV_Contact3_ID

stDocName = "Frm_NewContacts"
stLinkCriteria = "[Contact_ID] =" & Me![BPV_Contact3_ID]

If IsNull(Me!BPV_Contact3_ID) Then

DoCmd.OpenForm stDocName, acNormal, , , acFormAdd, acDialog,
MyContactOpenArgs

Else

DoCmd.OpenForm stDocName, acNormal, , stLinkCriteria, acFormEdit,
acDialog

End If

Exit_cbo_BPV_Contact_3_DblClick:
Exit Sub

Err_cbo_BPV_Contact_3_DblClick:
msgbox Err.Description
Resume Exit_cbo_BPV_Contact_3_DblClick
End Sub
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top