Results 1 to 9 of 9
  1. #1
    4 Star Lounger
    Join Date
    May 2001
    Location
    Oxfordshire
    Posts
    456
    Thanks
    0
    Thanked 0 Times in 0 Posts

    No message box (2003 SP2)

    I need to display a message box if a field on a sub-form is blank. But the criteria in the if statement doesn't seem to pick up when there is no record.

    I've tried using "" & Is Null. The first option has no effect, an e-mail is still created. The second presents run-time error 424.

    This is the code (so far); <font color=blue>
    Private Sub Comments_LostFocus()
    Dim strMsgTxt As String
    Dim strSubject As String
    Dim strAddy

    strMsgTxt = "Dear " & Me![sfrmResp]![Resp1stName] & "," & vbCrLf _
    & "This is to inform you that material from complaint number " & Me![ComplaintNumber] _
    & " has been placed in the " & Me![Press] & " returns area, for your attention." _
    & vbCrLf & "Regards."

    strSubject = "Customer returns; Complaint " & Me![ComplaintNumber]

    strAddy = Me![sfrmResp]![Email]

    If Me![ScrapOrReturns] <> "Scrapped" Then
    If strAddy = "" Then
    MsgBox "Please contact the relevant person about this complaint." _
    , vbOK, "No e-mail address"
    Else
    DoCmd.SendObject To:=strAddy, _
    Subject:=strSubject, MessageText:=strMsgTxt
    End If
    End If

    End Sub</font color=blue>

  2. #2
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: No message box (2003 SP2)

    Try:
    <code>strAddy = Nz(Me![sfrmResp]![Email],"")</code>
    and see if that helps.
    Regards,
    Rory

    Microsoft MVP - Excel

  3. #3
    4 Star Lounger
    Join Date
    May 2001
    Location
    Oxfordshire
    Posts
    456
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: No message box (2003 SP2)

    Well that has stopped the e-mail from being created, but the message box doesn't appear <img src=/S/confused.gif border=0 alt=confused width=15 height=20>

  4. #4
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: No message box (2003 SP2)

    What do you get if you change the code to this:
    <pre>Private Sub Comments_LostFocus()
    Dim strMsgTxt As String
    Dim strSubject As String
    Dim strAddy As String

    strMsgTxt = "Dear " & Me![sfrmResp]![Resp1stName] & "," & vbCrLf _
    & "This is to inform you that material from complaint number " & Me![ComplaintNumber] _
    & " has been placed in the " & Me![Press] & " returns area, for your attention." _
    & vbCrLf & "Regards."

    strSubject = "Customer returns; Complaint " & Me![ComplaintNumber]

    strAddy = Nz(Me![sfrmResp]![Email],"")
    msgbox "strAddy = " & straddy & "; length: " & len(strAddy)

    If Me![ScrapOrReturns] <> "Scrapped" Then
    If strAddy = "" Then
    MsgBox "Please contact the relevant person about this complaint." _
    , vbOK, "No e-mail address"
    Else
    DoCmd.SendObject To:=strAddy, _
    Subject:=strSubject, MessageText:=strMsgTxt
    End If
    End If

    End Sub
    </pre>

    Regards,
    Rory

    Microsoft MVP - Excel

  5. #5
    4 Star Lounger
    Join Date
    May 2001
    Location
    Oxfordshire
    Posts
    456
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: No message box (2003 SP2)

    Hmm, that's curious. I can't understand what's happening, but something is <img src=/S/smile.gif border=0 alt=smile width=15 height=15>

    OK, when the [Email] field has no record, an OK message box appears with the following
    strAddy= ; Length :0

    When there is a valid e-mail address, it's;
    strAddy= ****.****@*****.com Length; 26 . Then the e-mail message appears.

    What doesn't appear, is my msgBox???

  6. #6
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: No message box (2003 SP2)

    What is the value of <code>ScrapOrReturns</code> when the email is blank?
    Regards,
    Rory

    Microsoft MVP - Excel

  7. #7
    4 Star Lounger
    Join Date
    May 2001
    Location
    Oxfordshire
    Posts
    456
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: No message box (2003 SP2)

    Whoops, forgot to check that.

    If that does not have "Scrapped" then there are 2 consecutive msgBox's; the first is the one from your code, the second from mine.

    So what do I need to do now to see only mine, please?

  8. #8
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: No message box (2003 SP2)

    Go back to what you had originally, but with the <code>strAddy = Nz(...)</code> line from my first response.
    Regards,
    Rory

    Microsoft MVP - Excel

  9. #9
    4 Star Lounger
    Join Date
    May 2001
    Location
    Oxfordshire
    Posts
    456
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: No message box (2003 SP2)

    Now I feel a bit of plonker. <img src=/S/blush.gif border=0 alt=blush width=15 height=15>

    That works just right. Many thanks.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •