Validation Rule?

[cardgunner]

In a subform If the my main.id field is 999999 then the main.alt_id field needs to be greater then 1. I thought i could set the validation rule to be
iif(main.id='999999', '', main.alt_id>1) but that didn't do anything.

Any thoughts or ideas?

 

[boblarson]

Don't use validation rules for this. Use the subform's BEFORE UPDATE event.

Private Sub Form_BeforeUpdate(Cancel As Integer)
   If Me.Parent!ID='9999999' Then
      If Me.Parent!alt_id < 1 Then
         Cancel = True
         MsgBox "Alt ID needs to be greater than 1"
      End If
   End If
End Sub

You would also likely need to issue

Me.Undo

after the message box because they wouldn't be able to get to the main form to change the AltID field if you didn't because it would continue to fail and they would be stuck in a never ending loop.

 

[cardgunner]

I did as asked but i get an error that Me.Parent!alt_id cannot be found.

 

[boblarson]

Are you sure that alt_id is in the recordsource of the main form and is it exactly that name (with the underscore) or has it got a space? Is there a control on the main form bound to that field? If so, try using

Me.Parent.ControlNameHere

instead.

 

[cardgunner]

Alt_id is the field name however it is not in the main foarm form it is in the sub form.

If the sub form.id = 999999 then the sub form.alt_id should be greater then 1.

All I want is if the id is 999999 then the data entry professional has to put in what the alt_id is. They get info from another subform in the main form.

 

[boblarson]

Ah, you had written
iif(main.id='999999', '', main.alt_id>1)

so I assumed it was on the main form.

Try this instead:

Private Sub Form_BeforeUpdate(Cancel As Integer)
   If Me.Parent!ID='9999999' Then
      If Me!alt_id < 1 Then
         Cancel = True
         MsgBox "Alt ID needs to be greater than 1"
      End If
   End If
End Sub

 

[cardgunner]

Well I didn't get any errors however nothing else happened either.

When id was 999999 I was able to leave alt_id null, -1, 1 or 9 without any messages.

I added what you gave me on the before update of sub_form.alt_id.

Nothing.

If I wanted to adjust the criteria to be if id = 999999 then alt_id can not = null how would that be written

Private Sub alt_id_BeforeUpdate(Cancel As Integer)
If Me!Id = 9999999 Then
If Me!alt_id Is Null Then
Cancel = True
MsgBox "Alt ID needs to be greater than 1"
End If
End If

End Sub

 

[cardgunner]

I also changed it up alittle in put in the sub_form property
as

Private Sub Form_BeforeUpdate(Cancel As Integer)
If Me.Parent!Id = 9999999 Then
      If Me!alt_id < 9 Then
         Cancel = True
         MsgBox "Alt ID needs to be greater than 9"
      End If
   End If

End Sub

But still nothing. I put in alt_id 2 and put in 12 an left it null while the Id was 999999.

Hold on let me change 9999999 to 999999

 

[boblarson]

Here you go:

Private Sub Form_BeforeUpdate(Cancel As Integer)
   If Me.Parent!ID=9999999 Then
      If IsNull(Me!alt_id) Or Me!alt_id < 2 Then
         Cancel = True
         MsgBox "Alt ID needs to be greater than 1"
      End If
   End If
End Sub

[

 

[cardgunner]

That was it 999999.

Thanks.

I'll try to figure out the null statement.

 

[cardgunner]

Thanks Bob works like a champ.