Question Multi-Condition IF THEN

[larryg99]

Hello All...



I have been struggling to solve an issue with a multi-condition IF statement. If anyone has any ideas, I would certainly appreciate hearing them.

Here is the situation: MS Access 2010. Unbound form, unbound text and combo boxes.
4 controls: (me.tb1), (me.tb2), (me.cmb1) and (me.cmb2)

Me.tb1 can be blank, numeric, or alpha-numeric. Me.tb2 can be blank, numeric, or alpha-numeric.
Me.cmb1 and me.cmb2 are combo boxes with numerous text selections available to the user.

Condition1: I want to pop msg1 if me.tb1 is alpha-numeric AND me.tb2 is blank, and me.cmb1 <>”Select1” OR me.cmb2 <> “Select2”. I think the problem here is, the condition passes if me.tb1 is blank, because blank is also not alpha-numeric. If me.tb1 is blank, and me.cmb1 OR me.cmb2 are the correct text, then the condition should pass, i.e move on to next test. I only want to pop the message if me.tb1 is alpha-numeric and me.tb2 is blank and me.cmb1 and/or me.cmb2 <> "Select1" or "Select2" combo-box selection.

Condition2: I also need to pop msg2 (different message) if me.tb1 is blank, and me.cmb1 <>”Select3” OR me.cmb2 <>”Select4”. (here I don't care what is in me.tb2)

I need them to test in that order, i.e. test for condition1, then test for condition2. If Condition1 fails, it loops the user back until Condition1 passes, then moves on to test for Condition2.
I have code working for Condition2, and I can get Condition1 to work for 1 condition but not the others at the same time (i.e blank, numeric, and alpha-numeric). I just can’t seem to get it working for all conditions. I test for these conditions just before the open-recordset action. Both the conditional testing and the saving of the record is currently in the same private sub, triggered by a “Save” (on-click event) button on the form.

Any help would certainly be appreciated. Sorry I can't post the actual code I'm using due to Chinese wall at work. They have everything locked down pretty tight, I can't even copy and paste when working remotely.

 

[CJ_London]

Not at all clear what you are asking or trying to do. If your work won't let you copy paste, suggest you explain the problem to them and ask for their help.

You need to be clear about what blank actually is - it could be null or it could a be a zero length string - you would normally use something like nz(mycontrol,"")="" to cover both options

 

[larryg99]

Sorry CJ if I wasn't clear. The term "blank", as I used the term, means, the user enters nothing in that field. tb1 and tb2 are unbound, and no calculations are being preformed on the data being entered. Thank you for your response.

 

[sneuberg]

I suggest creating boolean variables for your conditions for example:

dim bolcmb1NotSelect1 as Boolean
bolcmb1NotSelect1 = me.cmb1 <>”Select1”

and using them in your if statement. You might prefer shorter names, Then you can put in a break and see in the locals window what's going on.

Also remember than AND has precedence over OR and sometime you need parenthesis to state what you really mean.

 

[arnelgp]

if me.tb1 & "" <> "" and me.tb2 & "" = "" and (me.cmb1 & "" <> "Select1" or me.cmb2 & "" <> "Select2" then
msgbox "condition1 is met"

end if

if me.tb1 & "" = "" and (me.cmb1 & "" <> "Select3" or me.cmb2 & "" <> "Select4") Then
msgbox "condition2 is met"
end if

 

[sneuberg]

I believe the previous post by arnelgp needs a right parenthesis after "Select2") in the first if-then.

 

[smig]

Sorry CJ if I wasn't clear. The term "blank", as I used the term, means, the user enters nothing in that field. tb1 and tb2 are unbound, and no calculations are being preformed on the data being entered. Thank you for your response.

What is Nothing ?
Is an empty string "" is nothing ?
Is a space only string " " is nothing ?

I prefer checking it by:
Trim(MyControl & "")=""

this will cover Null, Empty string and Spaces only string.