Validate all fields on continuous form

[LEXCERM]

    On Error Resume Next
    Dim ctl As Control
    For Each ctl In Me.Controls
    If ctl.Tag = "COMPLETE" Then
        If Len(ctl & vbNullString) = 0 Then
        MsgBox "Please complete all fields before saving this record.", _
        vbInformation, "Missing Data"
        Exit Sub
        End If
    End If
    Next ctl
 
    On Error GoTo 0
 
    confirm_pick = MsgBox("Are you sure you wish to confirm this order?", vbYesNo, "Confirm?")
    If confirm_pick = vbNo Then
    Exit Sub
    End If

Hi All,

I need to ensure that all fields on a "bound continuous form" are completed before saving.

The issue I have is that if you are entering a line of data, the continuous form creates a new blank record underneath, so the code will always record that there are null fields.

Is there a way around this?

Thanks,
Paul.
__________________

 

[pbaldy]

I guess you've given up on me, eh?

 

[LEXCERM]

LOL !! Would never give up on you Paul! Have just read your "OTHER" reply and will get back to you on Tuesday when back at work.

Thanks again,
Paul.

 

[missinglinq]

You assumption is incorrect. I'm assuming this code is in the Form_BeforeUpdate, where this type of validation code belongs. While a new line as been added it is not a new record until data has actually been inputted. Also, the controls being tested are the controls on the current record, i.e. the record you're entering data in.

You do have some problems with your code, however.

1. You really need to first ask if the user wants if they want to complete the order
   
2. You need to include a line in case they do not want to complete the order (Me.Undo)
   
3. You need to include a line to so that focus will remain on the current record until all required fields are populated. (Cancel = True)

Something like this should work for you:

Private Sub Form_BeforeUpdate(Cancel As Integer)
    confirm_pick = MsgBox("Are you sure you wish to confirm this order?", vbYesNo, "Confirm?")
    If confirm_pick = vbNo Then
    [B]Me.Undo[/B]
    Exit Sub
    End If
    
    On Error Resume Next
    Dim ctl As Control
    For Each ctl In Me.Controls
    If ctl.Tag = "COMPLETE" Then
        If Len(ctl & vbNullString) = 0 Then
        MsgBox "Please complete all fields before saving this record.", _
        vbInformation, "Missing Data"
        [B]Cancel = True[/B]
        Exit Sub
        End If
    End If
    Next ctl
End Sub

 

[LEXCERM]

Hi missinglinq and thanks for replying.

I actually have this code behind a command button. Initially a user will enter details to open the job record. Certain fields will remain blank until such time the job is being closed. It's at this point I then need to validate the record to make sure the remaining empty fields are completed.

Rgds,
Paul.

 

[gemma-the-husky]

missinglinq is still right

the form beforeupdate is the last step at which you can check and cancel the writing of the record. so in here you need ot check that all values in required fields are valid.

although you can do some of these in validation settings for fields. you will probably get a more pleasant effect if you do it here, and give your user a sensible error message.

if there are blank values that need setting (say a user name, input date/time etc) then do those in this procedure also

 

[LEXCERM]

Hi Gemma thanks for replying.

I've attached an example snapshot of my problem.

The first line of the continuous form is partially completed. This shows the job as being active. I do not require the validation at this point. Other lines may be added. Once the job is complete this is where I need to make sure that all info has been entered. The problem with the code is that it keeps recognising the last blank (New) record as data which is missing.

Sorry for being a pain!

Thanks,
Paul.

 

[gemma-the-husky]

so you have a job, and several lines of data,

and you dont want to allow the job to be marked complete, untril all the dewtails in the lines are fully completed ...

is that it?

 

[LEXCERM]

Yep, that's it!

 

[gemma-the-husky]

so in the main form, you probably have a drop down to select a changed status, or a button to do it

so either in the beforeupdate event for the dropdown, or in the click event for the button, you need to test the detail

you either need to iterate a recordset of the lines, and see if they are all completed properly

or probably better, you develop a query or queries to test the same thing - eg identify records with blank fields where they shouldnt be blank,

if the details arent ok, then refuse to update the main record to competed status.