I need to warn the end-user when update pulldown rank field

[chubby]

Thanks Aivars, I want to change my first idea to this: A Warning MSG BOX to the end user, once they changed the Rank pulldown field DON'T FORGET TO CHANGE THE PAYGRADE PULLDOWN TO!!!
Rank is your Job position, example ET2. PayGrade is your pay level, example E5. So a ET2 is the same same being an E5. They must match...

Private Sub Form_BeforeUpdate(Cancel As Integer)
Dim rst As Recordset

If IsNull(Me.Rank_tbl_ID) And IsNull(Me.PG_tbl_ID) Then
MsgBox "You may update..."
'or do.....
'or ignore it... and exit sub
'otherwise
Cancel = True
Exit Sub
End If
If IsNull(Me.Rank_tbl_ID) Or IsNull(Me.Rank_tbl_ID) Then
Set rst = CurrentDb.OpenRecordset("Select PG, Rank from MyTable;&quot
If IsNull(Me.PG_tbl_ID) Then
rst.FindFirst "Rank='" & Me.Rank_tbl_ID
rst.Edit
Me.PG_tbl_ID = rst!PG_tbl_ID
rst.Update
ElseIf IsNull(Me.Rank_tbl_ID) Then
rst.FindFirst "PG_tbl_ID='" & Me.PG_tbl_ID
rst.Edit
Me.PG_tbl_ID = rst!PG_tbl_ID
rst.Update
End If
rst.Close
Set rst = Nothing
End If
End Sub

 

[Aivars]

Hi again!

In cases like your I always. create special table (codificator) which contain:
MyCodificator - table name (for example)
1. Unique code (Rank) - primary key
2., 3. etc. information items or values for calculation (PayGrade, maybe Pay Value).

In the main table I create field same Unique code of MyCodificator not unique.
I relate both tables by these fields.
For updating of main table I create combobox based on MyCodificator. Accordingly in the main table are included only codes of codificator. Accordingly are excluded users' errors (cases when users forget update any field). Hereto some time occur necessity to change any value (e.g. Pay Value). Then it's easy. You may make only one cahange in the codificator. In consequence all data in main table are actualized.
Since main table include only code (Rank) all related data you can show and use for calculation via query based on both these tables.

I hope you'll understand what I mean. English is my foreign language, I started its self education short time ago, sorry.

Good luck!
Aivars