IIf Statement in a form

[Tumby]

I have a form with the following boxes-
MicroBusiness - a Yes/No box
MEAC - a box which has a numeric value
MLessThan10Staff- a Yes/No box
MLessThan2MillTurnover - a Yes/No box

The criteria for the MEAC box is- <55000

What I want to do is - If any of the last three conditions apply for the 'tick' to automatically appear in thr MicroBusiness Box.
The criteria for the MEAC box is- <55000

I have tried thr following in the control source for the MicroBusiness box-
but the box just greys out.

How do I do this?

I posted a similar post in the 'Queries' section as I was trying to do it via a query- but it would be much better if I could do it in the form.

 

[RuralGuy]

Are they CheckBoxes or TextBoxes? There are a lot of "box" controls in Access. Are these controls unbound or bound to fields in the RecordSource of the Form.

 

[steven.darby]

Ok on your 3 entry fields, whether numbers or boxes add event procedure to After_update to all 3.

In the code for all 3 afterupdates put in "Call UpdateMicroBusiness"

e.g.
Public Sub MEAC_AfterUpdate ()
Call UpdateMicroBusiness
End sub

then create the code for the function named UpdateMicroBusiness, so now if any of the 3 fields is changed(technically this means after the field is updated) it runs the update function.

So, add function like this... (just copy and paste in at the bottom of the 'afterupdates'

Private Function UpdateMicroBusiness ()
If me.MEAC.value < 5500 Or me.MLessThan10Staff.value = -1 Or me.MLessThan2MillTurnover = -1 then me.MicroBusiness.value = -1 else me.MicroBusiness.value = 0
End Function

'Everything from "If...." to "...value=0" should be all on the same line
'Depending what your boxes are you may need to change the above from -1 or 0 to '"yes" OR "no". (A checkbox that is checked has a value of -1, empty is 0.)
'It doesnt matter whether your controls are bound or not

'You might also consider on the Microbusiness control, set properties, data tab, locked = yes then the user of the form can't change the value to yes/no directly, it can only be set depending on the choices in your 3 entry controls (by running the code above).

 

[RuralGuy]

It doesnt matter whether your controls are bound or not

Unless you want the control completed based on bound values.

 

[steven.darby]

I meant to execute the code and set value of 'Microbusiness' based on 3 other control values, that will work whether the controls are bound or not.

(And, not necessarily, you can set recordsource and use Dlookup in code if you wanted to and still not need bound fields, all depends on what the op is doing / wants)

 

[Tumby]

To-RuralGuy

The boxes are as follows-
MicroBusiness - a Yes/No box - check box with tick
MEAC - a box which has a numeric value - bound
MLessThan10Staff- a Yes/No box -check box - bound
MLessThan2MillTurnover - a Yes/No box- check box - bound

I'm afraid Stephens answer is much too complicated for me!

 

[Brianwarnock]

I'm afraid Stephens answer is much too complicated for me!

Not a valid response.

Go into the form design view, right click a text box click properties from the menu thay opens.
Click the event tab on the properties menu that opens find After update click on the ... on the right, select code builder and hey presto it all becomes clear as to where to put the code

Repeat for other controls then add function as stated.

Brian

 

[RuralGuy]

Let's see if I have this right.

IF: [MEAC - a box which has a numeric value - bound] < 55,000
OR: [MLessThan10Staff- a Yes/No box -check box - bound] is checked
OR: [MLessThan2MillTurnover - a Yes/No box- check box - bound] is check
THEN: [MicroBusiness - a Yes/No box - check box with tick] needs to be checked

Does that sound right?

 

[Tumby]

Yes RuralGuy!

 

[RuralGuy]

In that case Steven.darby got it right and you simply need to slow down a bit and implement what he said. You need to get to the code module for your form and copy and paste this code:

Private Function UpdateMicroBusiness()
    If Me.MEAC < 55000 Or Me.MLessThan10Staff Or Me.MLessThan2MillTurnover Then
        Me.MicroBusiness = True
    Else
        Me.MicroBusiness = False
    End If
End Function

 

[steven.darby]

Be brave Tumby you can do it!! (save a copy of your database, then play about till you get it to work --- this is how everyone learns, sooner or later you will discover the power of VBA, then you unlock the secrets of building a database worth putting your name to, I ain't no programming expert, and you dont need to be, you just have to learn some of this lingo. My advice if your a complete novice -- get yourself a copy of VBA for dummies or something similar, trust me it'll be worth every penny!

dont forget, follow instructions to the letter, if you dont understand, then research it until you do.

all the best

 

[Tumby]

I would like to thank you for all your help. I am sorry if I sounded ungrateful to Stephen I didn't mean to be. But it all sounded so complicated it scared me!

However I will slow down, try what has been suggested and will not give up until I have cracked it.

The sound of VB does scare me- but I will take your advice and get a book.

Thanks again.

 

[Brianwarnock]

We have all scratched our heads and said, B%**$r this, I'll never understand it, but the biggest programs started small and grew, as do we programmers.

Best of luck

Brian