Form is allowing a partial record to be added to table when the user leaves the form

[PinkLady50]

I have a very detailed form that I have developed that controls when a record is written to the table. I have code that validates multiple fields to make sure the record is correct before it is written.



I have found several records in the table that are partial records. This happens when the user enters data on the form and exits the form before using the designated "Save" buttons.

Where and how is the best place to control this so I don't get partial records.

I would appreciate your help and guidance on this!

[RuralGuy]

Use the Before Update event of the form.

[Bulzie]

If you really want to make sure, use all unbound fields on the form, then on the Save button, check that each field has data and if so, then write the values to the record using an Insert statement. At the beginning of code on the Save button, go through each field and check for data and if not, then Set focus to that field and end the code. Add a Cancel button also that will close the form without saving anything if they want.

[RuralGuy]

I have to say, except for *very* rare circumstances, I've found no valid reason for an unbound form. Just my $0.02.

[PinkLady50]

RuralGuy
I think the Before Update is what I need and it will solve another unrelated problem now that I see how it works.

I want to clear fields any or all fields on the form that are not part of my validation code to write the record. I have provided a sample of the form.
Most of my partial records have the Analyst, Report Date, Contract Number, Task Order and CLIN as partial records. They fill this out and decide not to continue but it automatically writes the data once you change focus to a new control.

I only want the data in my controls to write to the table when I tell it to. My code knows when to write the Analyst, Report Date, Contract Number, Task Order and CLIN.
How to I use the Before Update to keep it from writing data as soon as the control has data and the focus changes to the next control?

[RuralGuy]

Access *ONLY* writes the new values when you move to a different record or close the form. If you don't want the values written then in the BeforeUpdate event either set Cancel = TRUE to return to the current record or execute a Me.Undo to clear all of the changes and exit. It does not save a value to the actual record just by moving to aa different control unless you are doing something yourself.

[orange]

Further to Allan's comments, I think in the BeforeUpdate, you check each of the required fields for valid values. If they don't complete these fields/controls, then Cancel = True etc. as he said.

[PinkLady50]

RuralGuy and Orange,
Thanks so much for your help! I just used the BeforeUpdate to validate some date fields and it works perfectly. I had the code in another Event Proc and it wasn't running but as soon as I put in the Before Update it worked.

Now I'm ready to tackle the partial records. Here is my question.... The user selects an Analyst from the drop down menu. The control is holding the value until I initiate my save sequence which validates that certain fields contain a value before I write the record. How can I use the Before Update to clear the field. It will clear it before I initiate the record validation which is multiple fields.

I apologize if I am not understanding this for the partial records. In my mind the logic works greats for something like a date validation where you don't want the value in the field unless it is a valid date.

UPDATE SOLVED: I found this code and it does what I need it to do.

Private Sub close_Click() 
Dim Answer As Integer 
If Me.Dirty = True Then    
Dim Response As Integer    
' Displays a message box with the yes and no options.    Response = MsgBox(Prompt:="Do you wish to discard data?", Buttons:=vbYesNo)    ' 
If statement to check if the yes button was selected.    If Response = vbYes Then        
DoCmd.RunCommand acCmdUndo
        DoCmd.Close        
Else        
Me.Clear.SetFocus    
End If 
Else
    ' The no button was selected.    
DoCmd.Close
End If
End Sub

Thanks again for all your help!!!!! Very much appreciated! Project is done! Moving on to the next one!

[orange]

My suggestion would be to do all the validation of all the fields that you need to be populated in the BeforeUpdate of the form event. If the entire validation is not completed successfully, then you could do the Me.undo. Another choice is to identify each of the validation errors and ask the user to fix them. Then, check that all required fields have been validated, then carry on. If any of the required fields fail the validation then, it's me.undo or identify the errors and re-ask the user to "fix" the issues.

The bottom line is ---Either the record meets your requirements or it doesn't. If it does, then save the record. If it doesn't, then don't save it. Have the user fix the issues or undo the intended record.