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

[SusanC]

That seems to be working! Thanks very much!
I really appreciate everyone's help!
No doubt I'll be back soon with something else!
Sue

 

[SusanC]

...more about If statement in code!!

I knew there would be more!
I forgot I also need to put in a separate criteria but in the same field property. So

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

I would now like to add another criteria so that

If Me.Purpose = 5 And Me.category = 10 then
Message = Different Message
cancel = True
End If

How can I fit this in if that's possible!?
thanks
Sue

 

[Brianwarnock]

I know i slipped up with my convoluted If code but I think if you are going to need to add more conditions , and to be flexible, I would look at the Case statement.

Brian

 

[Dennisk]

Validation should be in the Before_Update event and if the data is in error then you need the following line

Cancel = true

You can put formating in the after_update event

 

[SusanC]

select case statement

Ok - here goes then, does this look right?

If IsNull(me.purpose) then
stradvice = ""
Exit function
End If

Select Case (me.purpose)
Case "3" OR "4" then
IF (me.category) = "1" Or (me.category) = "3"
stradvice = " This is Wrong etc."
Case " 5" And (me.category) = "10"
straadvice = "You can't have this etc"
End Select
End function

thanks
Sue

 

[Mile-O]

Ok - here goes then, does this look right?

If IsNull(me.purpose) then
stradvice = ""
Exit function
End If

Select Case (me.purpose)
Case "3" OR "4" then
IF (me.category) = "1" Or (me.category) = "3"
stradvice = " This is Wrong etc."
Case " 5" And (me.category) = "10"
straadvice = "You can't have this etc"
End Select
End function

Is Me.Purpose a text field or a numerical field? If it's numerical, you shouldn't use the "" to delimit the number.

You don't use OR in a Select Case. You can just use a comma to separate the possiblities. i.e. Case 3, 4

Your IF statement has no THEN or ENDIF clause. You'll have had an error telling you that anyway.

You can't use THEN as part of Select Case structure.

If IsNull(Me.Purpose) Then
    Exit Function
End If

Select Case (me.purpose) 
    Case 3, 4
        If Me.Category = 1 Or Me.Category = 3 Then
            strAdvice = "This is Wrong etc."
        End If
    Case Is = 5 
        If Me.Category) = 10 Then
            strAdvice =  "You can't have this etc"
        End If
End Select

I would say to pay attention to what you are typing as you had spelling errors and extraneous spaces in there that could also cause problems.

 

[Brianwarnock]

No

Try

Private Sub Form_BeforeUpdate(Cancel As Integer)
msg1 = "Please check purpose and category values"
msg2 = "diff message"
Style = vbCritical
Select Case Me.Purpose
Case 3, 4
    If Me.category <> 1 And Me.category <> 3 Then
    message = MsgBox(msg1, Style)
    Cancel = True
    End If
Case 5
    If Me.category = 10 Then
    message = MsgBox(msg2, Style)
    Cancel = True
    End If
Case Else
End Select
End Sub

The Case Else is not mandatory but according to help is good practice.

You can see from this how easy it is to add new Purpose codes.

Brian


Edit Crickey Stewart was quick, I really must be getting old

 

[Mile-O]

I defer to Brian's complete example.

 

[Brianwarnock]

Hi Sue I have just noticed that I didn't put in your IsNull check, although I assume you will be putting out a meaningfull message?

Brian

 

[SusanC]

I'll give that a go thank you very much!
Good advice about paying attention to what I'm typing too!

Not entirely sure what was meant about the IsNull check. If the field is empty then nothing should happen. Do I really need this?
thanks again,
hope you have a good weekend,
Sue

 

[Brianwarnock]

If you don't object to the field being empty then you do not need to check.
Brian

 

[SusanC]

Hoorah!! it's working!
thank you very much everyone who has helped me! Now I've learnt the exciting world of Case statements!
Now onto the next thing!
thanks again,
Sue