check data on leaving a RECORD- record, subforms and all

[misscrf]

I have an application that enters candidates in, who apply for a job.

I found that I can enter a candidate in, and if I dont enter at least an address type or phone type ( giving that candidate an address record, albiet a blank one (just a type of address) and a phone record) then the candidate wont have a sub record for addres or phone at all.

This is hurting my reports. They are set to pull the primary address and phone only, as we dont need to have 2-3 contact information for a candidate on a report This is how I found out that I had blank address and phone for some test records. I need to be able to check when a person does a number of things, that at least the combos for phone type and address type have been chosen as something. This will ensure that they have a primary address and phone, as I have code that takes care of all that. I tried putting the following code in the form's before update, but since it is calling to a subform, the minute I tab TO the address combo, I get the message. The form is updating before I get the chance to skip the field.


Here is the lay out. A user can enter in candidate, their address, phone, application and activities. Address, phone, applications and activities are all sub reports on a tab control. The user should be able to move freely within any record, and only be bothered when they try to leave, if important information is still empty. I dont want to control how they enter ('you must enter this first!' lol) just that they do enter it in the end.

What I want to do is check when the user is leaving a record.(closing form, going to another record, clicking the search button that opens another form etc) if either combo is empty (no primary contact info) then it gives the message and goes back to the combo that is empty.

Here is the code I have. If anyone can help me find the right place to put it, I would really appreciate it. Thank you,

If IsNull(Me.[subAddressEntry].Form![cmbAddressTypeID]) Then
   msgbox "You must enter an Address"
   Me.[subAddressEntry].Form![cmbAddressTypeID].SetFocus
   Cancel = True
ElseIf IsNull(Me.[subPhoneEntry].Form![cmbPhoneTypeID]) Then
   msgbox "You must enter a Phone"
   Me.[subPhoneEntry].Form![cmbPhoneTypeID].SetFocus
   Cancel = True
Else: Cancel = False
End If

If I can't figure this out, I am thinking of making a new address type and phone type. Call it "none chosen" and then make that a default selection. Then one would always be chosen...I think. I would like to avoid that though.

Thanks again.

 

[Pat Hartman]

Your code doesn't work because you are validating data in a subform from the mainform and attempting to prevent the mainform from being saved. Well, it was saved when you moved the pointer from the mainform to the subform originally. It is not possible using the events we have available to do what you are asking to do. In a relational database, the parent record MUST be saved before any child records are entered so Access automatically saves the mainform record when you move the cursor to a subform and vice versa. When you move the cursor from a subform back to the mainform, the subform record is automatically saved.

All required fields should be placed on a single form when you are using multiple tabs to enter data for a single record. That way you can set the required property for the fields at the table level and let Jet prevent saving the record if required fields are missing. If you have required fields in the parent record and in the child record, that poses a different problem. One solution is to use temp tables. This will allow you to add child records before parent records and validate them all prior to appending the records to the main tables.

 

[misscrf]

I see what you are saying. How about this. Is there a way to prevent the user from changing from the current record on the main form if fields are not chosen on the subform?

That way, I am not saying don't save, I am saying don't move, go to that field that needs a choice. The move that the user might try to do, is click a button to open a search form, close the form, go to a next or previous record, open a report on the current record. We would have to allow them to erase the record ( or click that button as it would be).

Does that make sense?

Thanks for your reply.

 

[Pat Hartman]

You would need to put your validation code in the mainform's Unload event. If the subform's table does not contain the data you require, you can prevent the main form from closing. This method is a little iffy though since the user can always power off. You also have the problem of network and client system freezes so you should have a field in the main table that says that it has been edited successfully. You can then use that flag to prevent you from using invalid records and also to periodically bring them to the attention of the user so they can be fixed or deleted.