- Local time
- Today, 13:29
- Joined
- Feb 19, 2002
- Messages
- 43,266
The designers of the Access object model for forms were masterful. We can all be masters of our universe if we only just study the available events and use them as MS intended.
To populate a foreign key, the CORRECT event to use is the Form's BeforeInsert event. You don't need any If's to determine if you are on a new record because this event runs ONLY for a new record and it runs only once and only AFTER the user has first started typing. That means that you won't inadvertently dirty a record with your own code as you are currently doing. Since the OpenArgs is provided by a form that YOU coded, I wouldn't even bother to validate that either. Although you could if you don't trust your own code.
Also, to avoid saving incomplete records, you should always have validation code in your form's BeforeUpdate event to ensure that required fields are present and other fields are rational. For example, DOB can't be >= Date(). It also shouldn't be more than about 120 years ago or less if this is an employee database. If you want to hire people in their 80's ( and I think you should ) set a high limit and also allow the user to override the question - is this date valid? On the other hand, if you are working with an ancestry database, then obviously the DOB's could be very old but probably 1/1/202 still won't be valid so you can check that particular typo by limiting the year to something rational for your data.
To populate a foreign key, the CORRECT event to use is the Form's BeforeInsert event. You don't need any If's to determine if you are on a new record because this event runs ONLY for a new record and it runs only once and only AFTER the user has first started typing. That means that you won't inadvertently dirty a record with your own code as you are currently doing. Since the OpenArgs is provided by a form that YOU coded, I wouldn't even bother to validate that either. Although you could if you don't trust your own code.
Also, to avoid saving incomplete records, you should always have validation code in your form's BeforeUpdate event to ensure that required fields are present and other fields are rational. For example, DOB can't be >= Date(). It also shouldn't be more than about 120 years ago or less if this is an employee database. If you want to hire people in their 80's ( and I think you should ) set a high limit and also allow the user to override the question - is this date valid? On the other hand, if you are working with an ancestry database, then obviously the DOB's could be very old but probably 1/1/202 still won't be valid so you can check that particular typo by limiting the year to something rational for your data.
Code:
strOpenArgs = Split(Me.OpenArgs, ";")
Me.TbJobId = strOpenArgs(0)