If FieldA = 1, then FieldB MUST = 2???? Can this be coded????

[SusanC]

Hi,
I have a form for data entry relating to experiments.
I have several fields such as catagory, purpose, outcome.
Each field has a listbox to choose criteria from i.e. Category = 1 Environmental, 2 biological, 3 toxicology, 4 other and so on.

What I need to happen is if the user puts in a number "3" in the purpose field, Category must = "1" or "3". If it it doesn't then a msgbox comes up asking the user to check.
I have tried things like below but am sure where to put them and if it's correct?
Anyhelp would be fab:

Forms - Before update propertied of the Purpose field
Iif [forms]![purpose]= "3" AND IIF [forms]![category] = "1" OR "3", "", msgbox "Please check options made".

thanks
Sue

 

[ColinEssex]

If Me.Purpose = 3 Then

Me.Category = 1 or Me.Category = 3 

else

MsgBox "Please check"

End if

Col

 

[SusanC]

Hi,
thanks for the help.
I can seem to get that to work. Does that go in the before update properties?

 

[mhartman]

Hello:

Put the above code in the AfterUpdate of your Purpose control.

Regards

Mark

 

[ColinEssex]

Thats assuming the category field is already completed.

I would put it on the button they use to close the screen, but its hobsons choice really.

Col

 

[SusanC]

Hi there,
I put this in the after update properties. I only get the msg when purpose = 2.
I need the message to pop up if purpose = 1 and category doesn't = 1 or 3.
not sure what to change!?

 

[ColinEssex]

You indicated that you wanted Category to = 3

if the user puts in a number "3" in the purpose field, Category must = "1" or "3". If it it doesn't then a msgbox comes up asking the user to check.

If any of the figures you quoted don't match those 3 figures you mentioned, the code runs the message

I get the impression there are several combinations here that can be right or wrong depending on the Category selected.

Col

 

[SusanC]

Yes you're right. Sorry got myself confused!!
I'll take another look at whats going on!!

 

[SusanC]

Hi,
I have checked I have got the code is as if purpose = 3 then catagory should = 1 and 3 and if not then a msg should appear. However, the message only appears if the purpose field does not = 3. No matter what combinations the are.
I hope this is not getting too frustrating for you - I really appreciate your help so far!?
Sue

 

[GaryPanic]

coming in on the end of this
you need to work out first
if purpose = 1 then cat = 1 or 3
if purpose = 2 then cat =
all the way down your options for purpose
if purpose tied to a table ??
would purpose always have variables

ie if P = 2 then cat = 7

if their are variables how many to each ie will their only be 2 per purpose
might be better to tie this to a table and get a filter option

as an example if their would only be 2 or 3 options set your form up in such a way that if purpose x is select then cat x is visible then you could have a tick box option as long as your cats stop at about 20-30 then this might be a do-able option
if you take this option you may have to think it through a bit
say you select option 1
then cat 1 and 3 became visible , I would also code it some how that all your other cats get the value 0 or false this way when the purpose changes the values would also be changed otherwise you might get a mixuter of purposes and cats that do not relate to each other
don't forget re.query after update (I keep doing this and having a tantrum wondeering why its not updating the form )

 

[ColinEssex]

Sue - if the Purpose is anything other than 3 the message will appear.

You need to list all the combinations which are acceptable

e.g.

Purpose = 1 so Category must only be 2
Purpose = 2 so category must only be 1 and 4

etc - that way all combinations can be catered for in the code. . . . or

Have you considered a Cascading ComboBox (on the Category field) where the Category options to show in the Combo list will be correct for the Purpose number?

Col

 

[SusanC]

Thanks to both of you, gives me a lot to think about.

However, there is only one combination that requires a message box.
All the other combinations do not matter. The only one is if purpose = 3 then cat must = 1 or 3.

Does this mean that I will then have to think of every combination and put them in just for this one combo?

If that is the case I think I will need to rethink the whole thing. mmm puzzler!!

WOuld it be possible to put in a line to say that if purpose does NOT = 1 then do nothing, if it does = 1 then follow the code?

 

[Brianwarnock]

The following code should do the trick

Private Sub Purpose_AfterUpdate()
If Me.Purpose <> 3 Then
Else
If (Me.category = 1 Or Me.category = 3) Then
Else
MsgBox "Please Check"
End If
End If

End Sub

Brian

 

[SusanC]

I think it is working - is it the else you added that makes the difference?!

How can I stop the user from moving to the next record unless they have put in the right criteria. Cos eventhough the message now comes up when it is supposed to the user may ignore it and move on.
thanks...this is great!

 

[Rich]

If this check is crucial then the code belongs in the Forms BeforeUpdate event

 

[SusanC]

I have put it on the On current part and that seems to work - do you think that's okay?

 

[Rich]

Like I said, if it's crucial that the correct value has to be entered then the code belongs in the BeforeUpdate event of the form with a Cancel = True to prevent the update

 

[Brianwarnock]

Rich is correct in that this code should be in the before update event of the form, plus the addition of the Cancel = True. You might also consider a more informative and critical messge. The code would be like

Private Sub Form_BeforeUpdate(Cancel As Integer)

msg = "Please check purpose and category values"
Style = vbCritical
If Me.Purpose <> 3 Then
Else
If (Me.category = 1 Or Me.category = 3) Then
Else
message = MsgBox(msg, Style)
Cancel = True
End If
End If

End Sub


Brian

 

[SusanC]

Hi,
that's working a treat now! Thanks every so much for all the help.
If I wanted to add another condition is that possible?

So
If Me.Purpose <> 3 Then
Else
If (Me.category = 1 Or Me.category = 3) Then
Else
message = MsgBox(msg, Style)
Cancel = True
End If
End If

End Sub

Could I add in another criteria, so If Me.Purpose <> 3 OR 4 Then....plus the rest of the code?
I've tried putting in just OR but it doesn't like it.
thanks
Sue
P.S it's quite satisfying when you get something to finally work isn't it!?!

 

[Mile-O]

I have put it on the On current part and that seems to work - do you think that's okay?

SusanC,

Just to jump in. It would be beneficial if you placed the cursor on the list of possible events and then hit the F1 key to bring up the online help about it. If you did so then you would see that the OnCurrent event is absolutely the wrong place to put such code since it activates when a record is changed on the form.

Anyway:

Looking at your code, I don't quite see the need for all this If Then Else, without actually doing anything.

If Me.Purpose <> 3 Then

Else
    If (Me.category = 1 Or Me.category = 3) Then
    Else
        message = MsgBox(msg, Style)
        Cancel = True
    End If
End If

This could be written as:

If Me.Purpose = 3 And Me.Category <> 1 And Me.Category <> 3 Then
    Message = MsgBox(msg, Style)
    Cancel = True
End If

Or, if you wanted the two separate conditions:

If Me.Purpose = 3 Then
    If Me.Category <> 1 And Me.Category <> 3 Then
        Message = MsgBox(msg, Style)
        Cancel = True
    End If
End If

It just cuts out the needless lines.

To answer your latest question, I think you mean this:

If Me.Purpose = 3 Or Me.Purpose = 4 Then
    If Me.Category <> 1 And Me.Category <> 3 Then
        Message = MsgBox(msg, Style)
        Cancel = True
    End If
End If