SendObject help requested

  • Thread starter Thread starter AJ
  • Start date Start date
A

AJ

Please see below code.

When each of the fields from my form associated with the "msgstring" code
has data entered, the Send works perfect. However, if any of the fields has
nothing (=0) then it does not work at all. Can anyone tell me why?

msgstring1 = IIf(Me.Combo464 = 0, "", Me.Combo464.Column(1) & ";")
msgstring2 = IIf(Me.Assigned_To_2 = 0, ";", Me.Assigned_To_2.Column(1) & ";")
msgstring3 = IIf(Me.Assigned_To_3 = 0, ";", Me.Assigned_To_3.Column(1) & ";")
msgstring4 = IIf(Me.Assigned_To_4 = 0, "", Me.Assigned_To_4.Column(1))

DoCmd.SendObject acSendNoObject, , , msgstring1 & msgstring2 & msgstring3 &
msgstring4, , , , , , False
End Sub

Thank you!!!
 
Put a breakpoint on your DoCmd line and run your code. When the breakpoint
triggers, type the following in the VBE Immediate window:
? msgstring1 & msgstring2 & msgstring3 & msgstring4

The result is what you are passing for that argument. Does it look right?

1) I believe you only want a trailing ; when there is a value. You are also
adding them (sometimes) when there is no value to separate. Not 100% sure
but I suspect that might cause problems.

2)
if any of the fields has nothing (=0)
Click to expand...
Well, since 0 does NOT equal Nothing, I have to question whether your combo
is returning 0 like you think it is. An empty combo returns Null (which is
NOT zero, "", or Nothing. It is Null). And what happens if no selection at
all is made?

The following might work better:

msgstring1 = IIf(IsNull(Me.Combo464), "", Me.Combo464.Column(1) & ";")
msgstring1 = mstring1 & IIf(IsNull(Me.Assigned_To_2), "",
Me.Assigned_To_2.Column(1) & ";")
msgstring1 = mstring1 & IIf(IsNull(Me.Assigned_To_3), "",
Me.Assigned_To_3.Column(1) & ";")
msgstring1 = mstring1 & IIf(IsNull(Me.Assigned_To_4), "",
Me.Assigned_To_4.Column(1) & ";")

If Len(msgstring1)>0 then
' At least one selection was made so we have one trailing blank we
need to remove.
' Note: adding the trailing semicolon to #4 isn't a mistake, it was
consistency to keep the next line from breaking.
msgstring1 = Left(msgstring1, Len(msgstring1)-1)
DoCmd.SendObject acSendNoObject, , , msgstring1, , , , , , False
Else
MsgBox "Unable to send. Please select at least one recipient."
End If
 
Thank you


George Nicholson said:
Put a breakpoint on your DoCmd line and run your code. When the breakpoint
triggers, type the following in the VBE Immediate window:
? msgstring1 & msgstring2 & msgstring3 & msgstring4

The result is what you are passing for that argument. Does it look right?

1) I believe you only want a trailing ; when there is a value. You are also
adding them (sometimes) when there is no value to separate. Not 100% sure
but I suspect that might cause problems.

2)
Well, since 0 does NOT equal Nothing, I have to question whether your combo
is returning 0 like you think it is. An empty combo returns Null (which is
NOT zero, "", or Nothing. It is Null). And what happens if no selection at
all is made?

The following might work better:

msgstring1 = IIf(IsNull(Me.Combo464), "", Me.Combo464.Column(1) & ";")
msgstring1 = mstring1 & IIf(IsNull(Me.Assigned_To_2), "",
Me.Assigned_To_2.Column(1) & ";")
msgstring1 = mstring1 & IIf(IsNull(Me.Assigned_To_3), "",
Me.Assigned_To_3.Column(1) & ";")
msgstring1 = mstring1 & IIf(IsNull(Me.Assigned_To_4), "",
Me.Assigned_To_4.Column(1) & ";")

If Len(msgstring1)>0 then
' At least one selection was made so we have one trailing blank we
need to remove.
' Note: adding the trailing semicolon to #4 isn't a mistake, it was
consistency to keep the next line from breaking.
msgstring1 = Left(msgstring1, Len(msgstring1)-1)
DoCmd.SendObject acSendNoObject, , , msgstring1, , , , , , False
Else
MsgBox "Unable to send. Please select at least one recipient."
End If
Click to expand...
 
Back
Top