How to write a somewhat complicated conditional validation rule?

[ACCESSQs]

Hi,

I was wondering how to write a somewhat complicated validation rule. Our database has many ID components, some of them are not the primary key. For one of these IDs I do not want another field to repeat for that given ID.

So there might be 40 primary key IDs within a region (region ID) and for that region I do not want the rank (a seperate variable) to repeat. So I want to do something like

IF RegionID is the same THEN Rank cannot repeat. in the "validation rule" for the rank variable.

Anyone know how this is done?

Thanks!

Anne

[TheSmileyCoder]

I haven't used the built in validation rules much, so I wouldn't know if there is a smart way of doing this, using validation. I see 2 approaches to this:
One can be to create a unique combination index, where you make an index spanning 2 fields (your Region ID, and Rank), thus you can only have 1 combination of each.

The other can be to add some code in the forms BeforeUpdate Event:

Private Sub Form_BeforeUpdate(Cancel as Integer)
'Validate Record
  Dim strMsg as string
  If Isnull(Me.tb_RegionID) then 
    strMsg=strMsg & vbnewline & "Region ID must be filled in"
  End If
  If isNull(me.cmb_Rank) then
    strMsg=strMsg & vbnewline & "You must select a rank in the combobox"
  End If

  If Not isnull(me.Tb_RegionID) and not Isnull(me.cmb_Rank) then
  'Check that Region/rank combo doesn't exist
  If Not IsNull(Dlookup("ID_Rank","tbl_MyTable","ID_Region=" & me.tb_Region & " AND " ID_Rank=" & me.Cmb_Rank ) then
  strMsg=strMsg & vbnewline & "The rank/region combination allready exists."
  end if

  If strMsg & "" <>"" then
   'Validation Error
    Msgbox "Could not save record due to validation errors. Please correct:" & strMsg,vbOkOnly
    Cancel=true
  End If
End Sub

[NeoPa]

I would suggest a unique compound index of the two fields. That will ensure that both field values together will never be duplicated.

[ACCESSQs]

Thanks!!!

Thanks, that was very helpful!

[NeoPa]

Always happy to help of course :)

Welcome to Bytes!