Invalid use of Null (Msg) (2002 SP-2)

Topic

New Reply

In the following code the only real variable is strRecipient = Me.CarrierName. If the operator fails to select the CarrierName on the form, but clicks the cmdbtn to invoke this code, he gets the MSmsg “Invalid use of Null” warning. The problem is that there is no description of where the null entry is located, and this form has nearly fifty entries. I would like to create my own pop-up to alert to the error and to describe where the null entry is located (i.e. “You must first select a carrier”). I suspect it would go in the ErrHandler, but I’m not confident of that.

Private Sub cmdLoadConfirmEmail_Click()
On Error GoTo ErrHandler
‘Email confirm to assigned carrier

Dim strReportName As String
Dim strRecipient As String
Dim strSubject As String
Dim strMessage As String
Dim blnEditMail As Boolean

strReportName = “rptLoadConfirmSingleEmail”
strRecipient = Me.CarrierName
strSubject = “Load Confirm Attached for PRO# ” & Me.ProNo
strMessage = “Please reply to this message with LOAD CONFIRMED (or similar text) in the message body.” _
& vbCrLf & vbCrLf & “Thank You,” & vbCrLf & “Bryan”
blnEditMail = True

DoCmd.SendObject acSendReport, strReportName, acFormatSNP, strRecipient, , , strSubject, strMessage, blnEditMail
Exit Sub

ErrHandler:
If Err.Number = 2501 Then
‘E-mail cancelled – just ignore the error
Else
‘Other error – inform user
MsgBox Err.Description, vbExclamation
End If
End Sub

Reply | Quote

Replies

Before the line strRecipient = Me.CarrierName, insert code like this:

If IsNull(Me.CarrierName) Then
MsgBox “You must first select a carrier”, vbExclamation
Me.CarrierName.SetFocus
Exit Sub
End If

If there are more required fields, you can use similar code for those.

Reply | Quote

Hans:

Thanks for the reply. I had tried a number of ways in the ErrHandler and then finally tried something similar to your suggestion, but got lost in how to format the “If IsNull” statement. I have tried your suggestion, but am getting a “Compile Error: Method or data member not found” at SetFocus. Here is the (partial) code”

Dim strReportName As String
Dim strRecipient As String
Dim strSubject As String
Dim strMessage As String
Dim blnEditMail As Boolean

strReportName = “rptLoadConfirmSingleEmail”
‘Check to see that a carrier has been selected
If IsNull(Me.CarrierName) Then
MsgBox “You must first designate a carrier.”, vbExclamation
Me.CarrierName.SetFocus
Exit Sub
End If
strRecipient = Me.CarrierName
strSubject = “Load Confirm Attached for PRO# ” & Me.ProNo
strMessage = “Please reply to this message with LOAD CONFIRMED (or similar text) in the message body.” _
& vbCrLf & vbCrLf & “Thank You,” & vbCrLf & “Bryan”
blnEditMail = True

Your assistance greatly appreciated.

Reply | Quote

Perhaps the control bound to the CarrierName field has a different name (in fact, many experts recommend to make the name and control source different). You need to use the name of the control. If that doesn’t solve it, what is CarrierName?

Reply | Quote

In design view it displays “CarrierName”, but this is not a TextBox, it’s a ComboBox. So, it’s “real” name is ComboCarrierName.

“ComboCarrierName” (no quotes) works just fine.

Thanks for another lesson learned (and hopefully not forgotten.

Reply | Quote

OK, glad it worked. Explanation: you have a control (combo box in this case) named ComboCarrierName bound to the field named CarrierName. For most purposes, it doesn’t matter whether you test the control or the field for being Null: IsNull(Me.CarrierName) and IsNull(Me.ComboCarrierName) will return the same value. But you can only set focus to a control, not to a field. Me.CarrierName.SetFocuse causes an error since SetFocus is not a method of a field.

Reply | Quote

Hans:

[EDIT] I was entering this when you replied.
If I may be so bold, and to perhaps gather some knowledge here.

Reply | Quote

I think the reply I posted in the meantime corresponds to your question, but if you would like more info, don’t hesitate to ask.

Reply | Quote

In the code provided in post 332508 ; is there some way to have it fire when a form is closed?

I am using similar code in frmOrderNew to e-mail a confirmation that I have received the order. I would like for this to occur when I close the form (rather than taking the risk of forgetting to select a cmdbtn). This way the confirmation will always be sent whenever a new order is entered (I would retain the cmdbtn in frmOrderDetail in case I need to re-send the confirmation).The frmOrderNew already has the following code in the BeforeUpdate event:

Private Sub Form_BeforeUpdate(Cancel As Integer)

‘Assigns new Pro# to order when form is closed.

Dim dbs As DAO.Database, rs As DAO.Recordset
Dim lngProNo As Long
Set dbs = CurrentDb
Set rs = dbs.OpenRecordset(“tblProNoControl”)
rs.Edit
rs!NextAvailableProNo = rs!NextAvailableProNo + 1
lngProNo = rs!NextAvailableProNo
rs.Update
ProNo = lngProNo
Set rs = Nothing
Set dbs = Nothing
End Sub

Hope I’m not getting too far off subject.

Reply | Quote

What I would do is put a “Close” command button on the form, and call the code in the On Click event of the form. And I would take away the standard ways to close the form, so that the user MUST use the command button to close it. See (for example) post 94249 for a way to prevent the user from closing the form except using the command button.

Reply | Quote

Uh. . .

I believe I have now mastered the art of complicating the most simple matters! (The really sad thing is that I spend about an hour trying to figure out how to combine the code in the AfterUpdate event).

Thank you!

Reply | Quote

>> I believe I have now mastered the art of complicating the most simple matters!

I see a flourishing career in corporate management in your future

Reply | Quote

As a corporation of one . . .I’m in trouble

Well, there seems to be an issue with this resolution. I suppose the OnClose event of the cmdbtn must be firing prior to the BeforeUpdate code of the form because it’s returning a request for the Pro# (which is assigned by the BeforeUpdate event). Back to the drawing board.

[EDIT] It ocurrs to me that the frmOrderNew (after it is closed) posts the new order to the frmLoadBoard and is displayed as “NEW” in txtStatus (conditional formatting). Clicking on the Pro# of this new order brings up frmOrderDetail (nothing different from frmOrderNew except it loads in “edit” mode rather than “add”, and the fact that the Pro# has already been assigned). If I add a cmdbtn (DoCmd.SendObject) on frmOrderDetail, that would work fine except that there would not be any inticator that I clicked on it. I’m getting away from this thread so I am posing that question in post 332839 .

Reply | Quote

As a corporation of one . . .I’m in trouble

Well, there seems to be an issue with this resolution. I suppose the OnClose event of the cmdbtn must be firing prior to the BeforeUpdate code of the form because it’s returning a request for the Pro# (which is assigned by the BeforeUpdate event). Back to the drawing board.

[EDIT] It ocurrs to me that the frmOrderNew (after it is closed) posts the new order to the frmLoadBoard and is displayed as “NEW” in txtStatus (conditional formatting). Clicking on the Pro# of this new order brings up frmOrderDetail (nothing different from frmOrderNew except it loads in “edit” mode rather than “add”, and the fact that the Pro# has already been assigned). If I add a cmdbtn (DoCmd.SendObject) on frmOrderDetail, that would work fine except that there would not be any inticator that I clicked on it. I’m getting away from this thread so I am posing that question in post 332839 .

Reply | Quote

>> I believe I have now mastered the art of complicating the most simple matters!

I see a flourishing career in corporate management in your future

Reply | Quote

Uh. . .

I believe I have now mastered the art of complicating the most simple matters! (The really sad thing is that I spend about an hour trying to figure out how to combine the code in the AfterUpdate event).

Thank you!

Reply | Quote

What I would do is put a “Close” command button on the form, and call the code in the On Click event of the form. And I would take away the standard ways to close the form, so that the user MUST use the command button to close it. See (for example) post 94249 for a way to prevent the user from closing the form except using the command button.

Reply | Quote

In the code provided in post 332508 ; is there some way to have it fire when a form is closed?

I am using similar code in frmOrderNew to e-mail a confirmation that I have received the order. I would like for this to occur when I close the form (rather than taking the risk of forgetting to select a cmdbtn). This way the confirmation will always be sent whenever a new order is entered (I would retain the cmdbtn in frmOrderDetail in case I need to re-send the confirmation).The frmOrderNew already has the following code in the BeforeUpdate event:

Private Sub Form_BeforeUpdate(Cancel As Integer)

‘Assigns new Pro# to order when form is closed.

Dim dbs As DAO.Database, rs As DAO.Recordset
Dim lngProNo As Long
Set dbs = CurrentDb
Set rs = dbs.OpenRecordset(“tblProNoControl”)
rs.Edit
rs!NextAvailableProNo = rs!NextAvailableProNo + 1
lngProNo = rs!NextAvailableProNo
rs.Update
ProNo = lngProNo
Set rs = Nothing
Set dbs = Nothing
End Sub

Hope I’m not getting too far off subject.

Reply | Quote

I think the reply I posted in the meantime corresponds to your question, but if you would like more info, don’t hesitate to ask.

Reply | Quote

Hans:

[EDIT] I was entering this when you replied.
If I may be so bold, and to perhaps gather some knowledge here.

Reply | Quote

OK, glad it worked. Explanation: you have a control (combo box in this case) named ComboCarrierName bound to the field named CarrierName. For most purposes, it doesn’t matter whether you test the control or the field for being Null: IsNull(Me.CarrierName) and IsNull(Me.ComboCarrierName) will return the same value. But you can only set focus to a control, not to a field. Me.CarrierName.SetFocuse causes an error since SetFocus is not a method of a field.

Reply | Quote

To find out the name of a control you need to look at its name property in the properties box. The name property is in the other group.

Just because you have a combo box does not guarantee that its name will be combosomething. It depends on how you created it.

Reply | Quote

John:

Thanks again for your input. Amazingly enough, I did manage to look up the name in the properties box this time. Some of the controls are named “Combo…” and others are named “Cmb…” (which really gets interesting when others (command buttons) are “Cmd…”). This little journey has revealed a number of things to me, not the least of which is comprehensive naming conventions. Having a greater understanding of code would be helpful in avoiding some “simple” problems (if only in the difference between calling an entry vs. calling a control). Oh well, onward and upward!

Thanks!
It’s Guiness time!

Reply | Quote

John:

Thanks again for your input. Amazingly enough, I did manage to look up the name in the properties box this time. Some of the controls are named “Combo…” and others are named “Cmb…” (which really gets interesting when others (command buttons) are “Cmd…”). This little journey has revealed a number of things to me, not the least of which is comprehensive naming conventions. Having a greater understanding of code would be helpful in avoiding some “simple” problems (if only in the difference between calling an entry vs. calling a control). Oh well, onward and upward!

Thanks!
It’s Guiness time!

Reply | Quote

To find out the name of a control you need to look at its name property in the properties box. The name property is in the other group.

Just because you have a combo box does not guarantee that its name will be combosomething. It depends on how you created it.

Reply | Quote

In design view it displays “CarrierName”, but this is not a TextBox, it’s a ComboBox. So, it’s “real” name is ComboCarrierName.

“ComboCarrierName” (no quotes) works just fine.

Thanks for another lesson learned (and hopefully not forgotten.

Reply | Quote

Perhaps the control bound to the CarrierName field has a different name (in fact, many experts recommend to make the name and control source different). You need to use the name of the control. If that doesn’t solve it, what is CarrierName?

Reply | Quote

Hans:

Thanks for the reply. I had tried a number of ways in the ErrHandler and then finally tried something similar to your suggestion, but got lost in how to format the “If IsNull” statement. I have tried your suggestion, but am getting a “Compile Error: Method or data member not found” at SetFocus. Here is the (partial) code”

Dim strReportName As String
Dim strRecipient As String
Dim strSubject As String
Dim strMessage As String
Dim blnEditMail As Boolean

strReportName = “rptLoadConfirmSingleEmail”
‘Check to see that a carrier has been selected
If IsNull(Me.CarrierName) Then
MsgBox “You must first designate a carrier.”, vbExclamation
Me.CarrierName.SetFocus
Exit Sub
End If
strRecipient = Me.CarrierName
strSubject = “Load Confirm Attached for PRO# ” & Me.ProNo
strMessage = “Please reply to this message with LOAD CONFIRMED (or similar text) in the message body.” _
& vbCrLf & vbCrLf & “Thank You,” & vbCrLf & “Bryan”
blnEditMail = True

Your assistance greatly appreciated.

Reply | Quote

Before the line strRecipient = Me.CarrierName, insert code like this:

If IsNull(Me.CarrierName) Then
MsgBox “You must first select a carrier”, vbExclamation
Me.CarrierName.SetFocus
Exit Sub
End If

If there are more required fields, you can use similar code for those.

Reply | Quote

Instead of handling it as an error you could also explicitly test for the condition yourself, before the code that sends the email

if isnull(me.carriername) then
         msgbox("You must first select a carrier.")
         me.carriername.setfocus
         exit sub
end if

Reply | Quote

John:

Very much appreciate your input/code. Please see msg string.

Reply | Quote

John:

Very much appreciate your input/code. Please see msg string.

Reply | Quote

Instead of handling it as an error you could also explicitly test for the condition yourself, before the code that sends the email

if isnull(me.carriername) then
         msgbox("You must first select a carrier.")
         me.carriername.setfocus
         exit sub
end if

Reply | Quote