Solved Checking value of all controls in a continuous form (1 Viewer)

[nashaz]

Hi all

I have a continuous form which helps me keep track of which employees have passed which course. The way db has been structured, is the data from the continuous form gets saved in a temporary table, and only gets copied to actual table when the data is all OK i.e., all records have employee name, pass checkbox ticked, and certificated date entered.

What I am looking to do now is to validate the data when the user clicks on a SaveBtn (which runs an append query to copy all data from temporary table to actual table).

*NOTE: The reason I have not used Form_BeforeUpdate is because then I get an error message everytime I enter an employee's name, before even moving on to enter Certification Date*

The issue is, as long as the first record on the continuous form has all three controls (cboEmployee, chkPass, txtCertificationDate) filled in, the code fails to detect the remaining empty controls. The error msgbox only pops up if any of the controls in the first record are empty

Here is the code I have tried to use:

Dim ctrl as Control

For Each ctrl in Me.Section(0).Controls
    
    If ctrl.tag = "Required" and Not (Me.NewRecord) Then
        
        If Len(ctrl & vbNullString) = 0 and Not (Me.NewRecord) Then
            MsgBox "Please fill " & ctrl.Name & " field before saving"
        End If
        Exit Sub
        
    End If
    
Next ctrl

Docmd.OpenQuery "AppendQ"
Docmd.RunSQL "DELETE * FROM tempT "

Thank you for your help

 

[theDBguy]

Do you only want to save the valid records or you don't to save any record at all when an invalid record in the temp table exist?

 

[nashaz]

Do you only want to save the valid records or you don't to save any record at all when an invalid record in the temp table exist?

I don't want to save any record at all, since the user will not have access to the tables and won't easily know which employee's record has been updated and which hasn't

 

[gemma-the-husky]

But a continuous form works on a single record at a time. You only need to validate the current record in the form beforeupdate event, and that will happen only if it's actually dirty.

It's somewhat perverse to try to check every record on a continuous form. There's no need to do that. A continuous form, like a datasheet is simply an alternative method of presenting the data so you say many records rather than one at a time.

 

[nashaz]

But a continuous form works on a single record at a time. You only need to validate the current record in the form beforeupdate event, and that will happen only if it's actually dirty.

It's somewhat perverse to try to check every record on a continuous form. There's no need to do that. A continuous form, like a datasheet is simply an alternative method of presenting the data so you say many records rather than one at a time.

I quickly put the following code in the before update event:

Dim ctrl as Control

For Each ctrl In Me.Controls
    If isnull(ctrl) Then
        msgbox ctrl.Name
    End If
Next ctrl

What happens is that this code generates the msg box as soon as I enter an employee in cboEmployeeID. For reference, my form looks like below:

You can see that the msg box is triggered before I even have the chance to click on checkbox or even enter the date

 

[CJ_London]

I quickly put the following code in the before update event:

which event did you actually use? ' there are several 'before updates'

 

[nashaz]

which event did you actually use? ' there are several 'before updates'

My bad. I used the form before update event. The msgbox pops up if I move away from employee name without clickin on pass or filling in Certification Date, but this may pose slight issue later down the line. That's why I am trying to validate all data when SaveBtn is clicked, before the data is actually appended to employee records.

 

[theDBguy]

I don't want to save any record at all, since the user will not have access to the tables and won't easily know which employee's record has been updated and which hasn't

It should be easy to find out if there are invalid records, so you can cancel the save action. But, did you also need to let the user know which records are invalid, or can they figure that out on their own?

 

[gemma-the-husky]

My bad. I used the form before update event. The msgbox pops up if I move away from employee name without clickin on pass or filling in Certification Date, but this may pose slight issue later down the line. That's why I am trying to validate all data when SaveBtn is clicked, before the data is actually appended to employee records.

Well you do need the form update event, but you probably need to check the tab control order, and make sure that the cycle property is set to current record, rather than all records. That way the check will only fire when you attempt to leave the current record and move to another.

 

[Mike Krailo]

As far as your issue with the before update, it is not clear which controls have the validation tag on them. If its all three controls, then that doesn't make much sense to me because, couldn't there be an employee that did not pass a course yet and therefore not have a Certification data? If you still want to validate all three controls though, see the below example as it would work on a bound form and validate each new entry as it is entered into the form. No appending necessary.

You have a checkbox control in the form which will always contain either a True (-1) or False (0). What purpose does running the function len() on that type of control??? Much better to validate each control separately outside of your control loop. Just get rid of that control loop all together and reference each control directly in it's own IF block. If all the controls you were validating were just text fields, then your original code would be close to working.

In your code for the before update, you neglected to add the line "Cancel = True" if the test fails and then to exit the sub after that. This all assumes you are using a bound form to a table. If not, then you can use the Save Button to do all your validation but much better to do the bound form if you can.

The attached database sample uses a main form and a subform configuration which makes more sense to me than using just a continuous form as it allows there to be a many to many relationship with many courses to many Employees See if this works for you or not.

Private Sub Form_BeforeUpdate(Cancel As Integer)
   ' Force an Employee name to be entered
   If Nz(Me.EmpID) = 0 Then
      MsgBox "Please fill out Employee name field before saving."
      Me.EmpID.SetFocus
      Cancel = True
      Exit Sub
   End If
   ' Force user to tick the checkbox
'   If (Me.Passed = 0) Or Not (Me.NewRecord) Then
   If (Me.Passed = 0) Then
      MsgBox "You must check the check box."
      Me.Passed.SetFocus
      Cancel = True
      Exit Sub
   End If
   ' The date field could use a lot more validation code. This is very simplistic.
   If Nz(Me.CertDate) = 0 Then
      MsgBox "You must fill out a valid date."
      Me.CertDate.SetFocus
      Cancel = True
      Exit Sub
   End If
End Sub

 

[Pat Hartman]

Validation should always be done as a record is saved. Your form should not allow invalid data to be saved. If you do allow incomplete records to be saved, then you need to run a select query ahead of the following update processes to determine if there is any invalid data.
The Select query should count invalid records. If the count is > 0 then you wouldn't run the transfer code.

The way db has been structured, is the data from the continuous form gets saved in a temporary table, and only gets copied to actual table when the data is all OK i.e., all records have employee name, pass checkbox ticked, and certificated date entered.

This is a difficult design pattern to work with, especially in cases where it really isn't necessary. Anyway:
Is this process only used to add new records or can it be used to update existing records?
1. If the process can be used to update existing processes, the problem becomes more complex. The easiest solution is to create a transaction and inside the transaction run THREE action queries:
Start Transaction
a. Delete existing rows.
b. Copy rows from temp table and append to production table.
c. Delete rows from temp table
End Transaction

Enclosing the three action queries inside a transaction ensures that ALL queries successfully complete. If there ia any failure, other updates are rolled back so that the tables are always in a consistent state.

2. If this process is only used to add rows, it is less dangerous because you won't delete any data unless the append query succeeds but you could still use a transaction.
Start Transaction
a. Copy rows from temp table and append to production table
b. Delete rows from temp table
End Transaction

DO NOT attempt to do this with a vba loop. There is no need to, plus it will be slower than running action queries. Action queries are always preferable to VBA code loops using DAO or ADO whenever possible. And for this process, the action queries are absolutely superior.

 

[Pat Hartman]

What happens is that this code generates the msg box as soon as I enter an employee in cboEmployeeID

You have other code that is forcing the record to be saved when it shouldn't be. The Form's BeforeUpdate event is the LAST event that runs before a record gets saved and the code you posted, does not save the record so it is some code we are not seeing.

 

[nashaz]

It should be easy to find out if there are invalid records, so you can cancel the save action. But, did you also need to let the user know which records are invalid, or can they figure that out on their own?

It would be preferable to let the user know which controls are empty, which I believe can happen using setfocus property and highlighting the control with a colour, for example. But the main functionality I am struggling with is to actually get the code to detect empty controls

 

[nashaz]

Well you do need the form update event, but you probably need to check the tab control order, and make sure that the cycle property is set to current record, rather than all records. That way the check will only fire when you attempt to leave the current record and move to another.

This already happens, and I was kind of hoping it wouldn't happen. So for example, if I had to enter 20 employees, I want to check if any control on any record is empty only when I press SaveBtn.

 

[gemma-the-husky]

The problem is really that the whole model you are using is awkward.

You don't want to start with a full template of names. Add them one at a time and validate each line before you save it. That way you avoid the need to try to reassess the entire table. A continuous form doesn't expose the entire table directly anyway. It's just an alternative method of presenting the data compared with a single form.

You are making it harder than it need be, by accepting data, and then at the end wanting to check and validate the data you already accepted.

The only way I can see you doing it this way is to let users load a spreadsheet perhaps, in which case you might them need check whether the spreadsheet data satisfied your integrity requirements. However I wouldn't do that by showing the data as a continuous form.

 

[nashaz]

As far as your issue with the before update, it is not clear which controls have the validation tag on them. If its all three controls, then that doesn't make much sense to me because, couldn't there be an employee that did not pass a course yet and therefore not have a Certification data? If you still want to validate all three controls though, see the below example as it would work on a bound form and validate each new entry as it is entered into the form. No appending necessary.

You have a checkbox control in the form which will always contain either a True (-1) or False (0). What purpose does running the function len() on that type of control??? Much better to validate each control separately outside of your control loop. Just get rid of that control loop all together and reference each control directly in it's own IF block. If all the controls you were validating were just text fields, then your original code would be close to working.

In your code for the before update, you neglected to add the line "Cancel = True" if the test fails and then to exit the sub after that. This all assumes you are using a bound form to a table. If not, then you can use the Save Button to do all your validation but much better to do the bound form if you can.

The attached database sample uses a main form and a subform configuration which makes more sense to me than using just a continuous form as it allows there to be a many to many relationship with many courses to many Employees See if this works for you or not.

Private Sub Form_BeforeUpdate(Cancel As Integer)
   ' Force an Employee name to be entered
   If Nz(Me.EmpID) = 0 Then
      MsgBox "Please fill out Employee name field before saving."
      Me.EmpID.SetFocus
      Cancel = True
      Exit Sub
   End If
   ' Force user to tick the checkbox
'   If (Me.Passed = 0) Or Not (Me.NewRecord) Then
   If (Me.Passed = 0) Then
      MsgBox "You must check the check box."
      Me.Passed.SetFocus
      Cancel = True
      Exit Sub
   End If
   ' The date field could use a lot more validation code. This is very simplistic.
   If Nz(Me.CertDate) = 0 Then
      MsgBox "You must fill out a valid date."
      Me.CertDate.SetFocus
      Cancel = True
      Exit Sub
   End If
End Sub

Wouldn't this have the same issue i.e., if the user moved on to new record without having filled all three controls, they'd get an error message?

Edit: If an employee fails the training, I can add another checkbox called Fail and create an append query to another table e.g., FailedT. But I would still need the code to check if all fields have been populated i.e., EployeeName, Pass/Fail, Date. That's how I am thinking, atleast

 

[nashaz]

Validation should always be done as a record is saved. Your form should not allow invalid data to be saved. If you do allow incomplete records to be saved, then you need to run a select query ahead of the following update processes to determine if there is any invalid data.
The Select query should count invalid records. If the count is > 0 then you wouldn't run the transfer code.

This is a difficult design pattern to work with, especially in cases where it really isn't necessary. Anyway:
Is this process only used to add new records or can it be used to update existing records?
1. If the process can be used to update existing processes, the problem becomes more complex. The easiest solution is to create a transaction and inside the transaction run THREE action queries:
Start Transaction
a. Delete existing rows.
b. Copy rows from temp table and append to production table.
c. Delete rows from temp table
End Transaction

Enclosing the three action queries inside a transaction ensures that ALL queries successfully complete. If there ia any failure, other updates are rolled back so that the tables are always in a consistent state.

2. If this process is only used to add rows, it is less dangerous because you won't delete any data unless the append query succeeds but you could still use a transaction.
Start Transaction
a. Copy rows from temp table and append to production table
b. Delete rows from temp table
End Transaction

DO NOT attempt to do this with a vba loop. There is no need to, plus it will be slower than running action queries. Action queries are always preferable to VBA code loops using DAO or ADO whenever possible. And for this process, the action queries are absolutely superior.

I like the idea of using select query to detect invalid record. I will give that a go. I am using a temporary table for the precise reason as to stop bad data from getting recorded in the permanent table.

The process is only used to add rows. I have not come across transactions in Access before (still relatively new) so I will play around with that and action queries and see if it makes things a bit easier.

 

[nashaz]

The problem is really that the whole model you are using is awkward.

You don't want to start with a full template of names. Add them one at a time and validate each line before you save it. That way you avoid the need to try to reassess the entire table. A continuous form doesn't expose the entire table directly anyway. It's just an alternative method of presenting the data compared with a single form.

You are making it harder than it need be, by accepting data, and then at the end wanting to check and validate the data you already accepted.

The only way I can see you doing it this way is to let users load a spreadsheet perhaps, in which case you might them need check whether the spreadsheet data satisfied your integrity requirements. However I wouldn't do that by showing the data as a continuous form.

I understand it is somewhat "unconventional" approach given how Access works. I was hoping there would be a way for a code to scan through all records in the underlying table and see if anything is not populated (). However, if it turns out to be impossible then I might have to change the approach

 

[nashaz]

You have other code that is forcing the record to be saved when it shouldn't be. The Form's BeforeUpdate event is the LAST event that runs before a record gets saved and the code you posted, does not save the record so it is some code we are not seeing.

In response to another poster, I realised I was not clear with the description so I explained that the msgbox pops up if I enter the name and click outside that record i.e., I have to fill all the fields on a record before I move on to the next record, which is expected of form_beforeupdate.

 

[Gasman]

I understand it is somewhat "unconventional" approach given how Access works. I was hoping there would be a way for a code to scan through all records in the underlying table and see if anything is not populated (). However, if it turns out to be impossible then I might have to change the approach

You are shutting the barn door after the horse has bolted.