Struggling with Another If And Or Statement

[reberryjr]

So, I'm struggling with yet another one of these statements. I've tried both sets of code below, and I keep getting the message box when each box is completed, except for the txt_Street2 box. The idea is that if any of the 5 are not null, but either Street1, City, ST or Zip are null, I should get the message box. How have I coded this incorrectly?

'If Not Len(Me.txt_Street1.Value) = 0 Or Not Len(Me.txt_Street2.Value) = 0 Or Not Len(Me.txt_City.Value) = 0 Or Not Len(Me.cobo_ST.Value) = 0 Or Not Val(Me.txt_Zip.Text) = 0 And _'(Len(Me.txt_Street1.Value) = 0 Or Len(Me.txt_City.Value) = 0 Or Len(Me.cobo_ST.Value) = 0 Or Val(Me.txt_Zip.Text) = 0) Then
'    Response = MsgBox("Please complete the partial address entered.")
'    If Response = vbOK Then Me.txt_Street1.SetFocus
'    Exit Sub
'End If


If Not Len(Me.txt_Street1.Value) = 0 Or Not Len(Me.txt_Street2.Value) = 0 Or Not Len(Me.txt_City.Value) = 0 Or Not Len(Me.cobo_ST.Value) = 0 Or Not Len(Me.txt_Zip.Value) = 0 And _
(Len(Me.txt_Street1.Value) = 0 Or Len(Me.txt_City.Value) = 0 Or Len(Me.cobo_ST.Value) = 0 Or Len(Me.txt_Zip.Value) = 0) Then
    Response = MsgBox("Please complete the partial address entered.")
    If Response = vbOK Then Me.txt_Street1.SetFocus
    Exit Sub
End If

 

[Roderick_E]

So, I'm struggling with yet another one of these statements. I've tried both sets of code below, and I keep getting the message box when each box is completed, except for the txt_Street2 box. The idea is that if any of the 5 are not null, but either Street1, City, ST or Zip are null, I should get the message box. How have I coded this incorrectly?

'If Not Len(Me.txt_Street1.Value) = 0 Or Not Len(Me.txt_Street2.Value) = 0 Or Not Len(Me.txt_City.Value) = 0 Or Not Len(Me.cobo_ST.Value) = 0 Or Not Val(Me.txt_Zip.Text) = 0 And _'(Len(Me.txt_Street1.Value) = 0 Or Len(Me.txt_City.Value) = 0 Or Len(Me.cobo_ST.Value) = 0 Or Val(Me.txt_Zip.Text) = 0) Then
'    Response = MsgBox("Please complete the partial address entered.")
'    If Response = vbOK Then Me.txt_Street1.SetFocus
'    Exit Sub
'End If


If Not Len(Me.txt_Street1.Value) = 0 Or Not Len(Me.txt_Street2.Value) = 0 Or Not Len(Me.txt_City.Value) = 0 Or Not Len(Me.cobo_ST.Value) = 0 Or Not Len(Me.txt_Zip.Value) = 0 And _
(Len(Me.txt_Street1.Value) = 0 Or Len(Me.txt_City.Value) = 0 Or Len(Me.cobo_ST.Value) = 0 Or Len(Me.txt_Zip.Value) = 0) Then
    Response = MsgBox("Please complete the partial address entered.")
    If Response = vbOK Then Me.txt_Street1.SetFocus
    Exit Sub
End If

I like to error trap and append to string then display it once. Something like:

Dim errmsg as string
if trim(me.text1)="" then
errmsg = "Textbox1 data needed"
endif
if trim(me.textbox2)="" then
errmsg = errmsg & vbcr & "Textbox2 data needed"
endif
if trim(me.textbox3)="" then
errmsg = errmsg & vbcr & "Textbox3 data needed"
endif
if errmsg<>"" then
msgbox errmg, vbcritical,"FORM INCOMPLETE"
exit sub
endif
'if ok then continue with macro...

Obviously you can personalize your messages more.

 

[reberryjr]

I was hoping to avoid having to call out each control.

 

[reberryjr]

Finally realized that I hadn't enclosed the first part of the statement in parentheses.