Validate values of controls in continuous form

[DoubleD]

Hi,

I am struggling to find the right solution for my situation. I am still very new to Access, continuous forms and VBA, so excuse my ignorance

I have a continuous form in Access 2007. One of the controls on the form is a text box with an expected arrival date. This control can be edited by the user. I need to validate that this control is not blank before running my insert and/or update SQL queries.

Currently I am validating this in a button click event, but this only checks the first line on the form and when a value is entered on the first line, it continues with the insert/update commands.

I need to validate that all the expected arrival date values are entered before any SQL queries may execute.

If Me.ArrivalDate = "" Or IsNull(Me.ArrivalDate) Then
        MsgBox "You have not selected an arrival date!", vbCritical + vbOKOnly, "Empty Arrival Date!"
        Me.ArrivalDate.SetFocus
        Exit Sub
End If

[Seth Schrock]

Put this code in your form's Before Update event and replace the Exit Sub with Cancel = True.

This will run before each record gets saved before it moves to the next one, so it will be testing the current record and not let you move on without validation passing.

[DoubleD]

Hi Seth,

The form that loads gives me a set of data based on what was entered in a previous form. I need the validation to happen in the click event of a button on this form, because it needs to run a few insert, update and delete queries.

I need it to check that the arrival date for each line on the continuous form is filled in before it can continue doing everything else.

Doing the validation in the form's before update event does nothing for me.

[Seth Schrock]

Then you are going to have to loop through your recordset. Do you want it to stop at every line that has a problem or do you want it to just tell you that there are problems once it has gone through all the records?

[DoubleD]

I need it to check if the arrival date has a value and for each line that does not have a value, it must throw message box stating that no date was entered.

[Seth Schrock]

But do you want it to stop on that record so that you know which one failed, or just a general message that one failed?

[DoubleD]

Having it stop on that record will be helpful.

[Seth Schrock]

Dim blnSuccess as Boolean
 
blnSuccess = True
 
Me.Recordset.MoveFirst
Do While Not Me.Recordset.EOF
    If Me.ArrivalDate & "" = "" Then
        blnSuccess = False
        Exit Do
    End If
    Me.Recordset.MoveNext
Loop
 
If blnSuccess = True Then
    'Perform Update query
Else
    MsgBox "You have not selected an arrival date!", vbCritical + vbOKOnly, "Empty Arrival Date!"
End If
 

[DoubleD]

Great! This works exactly the way I need it to. Thank you so much Seth!