Full Version: Validation Of Field On Multi Record Form
UtterAccess Forums > Microsoft® Access > Access Forms
paspencer
I have a form that displays every record that a user has selected from a list box. So I think that I hae multiple questions.
. How can I stop each record from saving until the user click a save button?
2. The onClick for the save button must vaildate that one field for the record is not blank. I can validate the first record using the code below but it does not cycle through each record to make sure it has been populated. Do I need to do a loop or a For...Each? Not my strong suit.
CODE
If Nz(Me.cboReason, "") = "" Then
MsgBox "Reason Cannot Be Left Blank"
cboReason.SetFocus
Exit Sub
End If

DoCmd.Close acForm, "frmPlanProcessInActive", acSaveYes

Thanks in advance.
Trice
rbianco
If your form is bound to a recordset, then as soon as your user leaves one record and navigates to another, the record losing focus attempts to save. This is normal behaviour, and it cannopt be avoided. You can make use of the BeforeUpdate event, and perform a validation at that time for each record, but I am guessing you do not want to have the user continually prompted - that you are seeking a "do all your stuff, and then I will check if you did it right".
One work around is to "stage" your records in a temp table, and then, after your user is "finished", cycle through the records for validation, and then engage a process that will insert your "verified" data into the final table. Another would be to work with arrays, but that gets trickier to explain
paspencer
Thanks for taking the time to answer. Somehow I didn't think it would be this involved. I was hoping for a simple For...Each statement.
The form is based on a query. Would it be possible to do a requery when the close button is clicked?
Thanks,
Trice
paspencer
I don't know if anyone is interested but I found a solution to my problem. By using the code below, if a user clicks on the "Save" button and there are records with the Reason field blank then will get an error message.
div class='codetop'>CODE
Private Sub cmdSave_Click()
' Check that a Reason was entered on each Record
Dim rs As Recordset
Dim PID As Long
Set rs = Me.RecordsetClone
rs.MoveFirst
Do While Not rs.EOF
If rs("InActive") = True And Nz(rs("Reason"), "") = "" Then
rs.FindFirst "PID=" & rs("PID")
Me.Bookmark = rs.Bookmark
MsgBox "Reason Cannot Be Left Blank", vbInformation, "Reason Required for Inactive Process"
cboReason.SetFocus
Exit Sub
End If
rs.MoveNext
Loop
Set rs = Nothing

DoCmd.Close acForm, "frmPlanProcessInActive", acSaveYes
Exit Sub
End Sub
rbianco
Yes, this approach will step through the records, and validate that the Inactive/Reason combination are as desired, but do realize that if the form is bound to the data set, then this validation is really occurring "after the fact" - that the data saves as each record loses focus. What happens if the system inadvertently shuts down or there is a power failure? Has an Invalid Combination of data been permitted to "stay" in the table?
paspencer
Hey, thanks for responding. Yes, I do realize that as they go along and update the field (which is a combo box so they can't answer wrong) that the record is saved. We just are trying to put a safe guard there so the user can't start setting up clients and processes and leave the task half finished. If we have a power failure the user will just have to go edit the record when the power comes back on!
This is a "lo-fi" version of UA. To view the full version with more information, formatting and images, please click here.