Data Validation_Able to go to next record before problem is resolved

[SpeedThink]

Appreciate a resolution concerning the following:

Able to display a message box that indicates the correct text box that is missing data.
However, after the message box displays, the end-user is able to go to the next record which is unacceptable.
I would desire that if the missing data issue is not resolved, then the end-user cannot go to another
record.

I am calling the function in the form's before update event as displayed below:


Private Sub Form_BeforeUpdate(Cancel As Integer)
Call RequiredData(Form)
End Sub




Public Function RequiredData(ByVal TheForm As Form) As Boolean
Dim i As Integer
Dim strName As String
For i = 1 To 4
If Not IsNull(TheForm("txtAmtSubmitted" & i)) Then
If Not IsDate(TheForm("txtDateSubmitted" & i)) Then
strName = "txtDateSubmitted" & i
Exit For
End If
If Not IsDate(TheForm("txtFollowupDate" & i)) Then
strName = "txtFollowupDate" & i
Exit For
End If
End If
If Not IsNull(TheForm("txtAmtReceived" & i)) Then
If Not IsDate(TheForm("txtDateReceived" & i)) Then
strName = "txtDateReceived" & i
Exit For
End If
End If
Next
If strName <> "" Then
MsgBox "Data is required in " & strName & "," & vbCr & _
"please ensure this is entered.", _
vbInformation, "Required Data..."
RequiredData = True
Else
RequiredData = False
End If
End Function

 

[brunces]

SpeedThink,

When you say the user can go to the next record, do you mean it by clicking a button (Next)? If so, maybe this can help you.

Declare a new variable like this one:
    Public intMissData As Integer

Add this to that function of yours:
    intMissData = 0
    (For each missing data, make your function do: intMissData = intMissData + 1)

Add this to your NEXT BUTTON code:
    If intMissData > 0 Then
        DoCmd.GoToRecord , , acGoTo, Me.CurrentRecord
    Else:
        DoCmd.GoToRecord , , acNext
    End If

I'm not sure if this could work inside BeforeUpdate event, after you call your function. Give it a try.

Hope it helps.

Hugs,

Bruce

 

[SpeedThink]

Additional context;

There is a listbox at the bottom of the form that contains all accounts that have been assigned to the end-user. When the end-user highlights a account in the listbox, all of the associated controls are displayed above the listbox.

So, to go to another record, one just have to click on another account within the listbox.

 

[brunces]

Try this, then...

Declare a new variable like this one:
    Public intMissData As Integer

Add this to that function of yours:
    intMissData = 0
    (For each missing data, make your function do: intMissData = intMissData + 1)

Add this to the BeforeUpdate event for your ListBox:
    If intMissData > 0 Then
        DoCmd.GoToRecord , , acGoTo, Me.CurrentRecord
    Else:
        (Do what it's already done when you click on a ListBox item.)
    End If

Certainly, you use the AfterUpdate event for your ListBox. Delete it and put all its codes after "Else:" within the "If" procedure above.

I'm not sure if it's going to work, but let's see it.

Bruce

 

[SpeedThink]

What about using "Cancel = True" instead of "Exit For"

Consequences?

 

[RuralGuy]

How about trying:

Private Sub Form_BeforeUpdate(Cancel As Integer)
Cancel = RequiredData(Form)
End Sub

 

[SpeedThink]

Assistance/insight appreciated -

Currently, have the following that is not quite working as planned. When I attempt to add the missing information, I am prompted with something like "not using the addnew or edit..." error dialog box.

So far, I have this;

Private Sub Form_BeforeUpdate(Cancel As Integer)
Call RequiredData(Form)
'If intMissData > 0 Then 'Added on March 10 2007 Didn't work
' DoCmd.GoToRecord , , acGoTo, Me.CurrentRecord
' Else:
' '(Do what it's already done when you click on a ListBox item.)
' End If
End Sub

Public Function RequiredData(ByVal TheForm As Form) As Boolean
'Dim intMissData As Integer ' Added on March 10 2007 1st option
Dim i As Integer
Dim strName As String
'intMissData = 0 'Added on March 10 2007
For i = 1 To 4
If Not IsNull(TheForm("txtAmtSubmitted" & i)) Then
If Not IsDate(TheForm("txtDateSubmitted" & i)) Then
strName = "txtDateSubmitted" & i
Me("txtDateSubmitted" & i).SetFocus '3rd iteration - added on March 11 2007
Cancel = True '3rd iteration - added on March 11 2007
' intMissData = intMissData + 1 'Added on March 10 2007
'strName.AddNew ' 3rd iteration - added on March 11 2007 Didn't work
Exit For
End If
If Not IsDate(TheForm("txtFollowupDate" & i)) Then
strName = "txtFollowupDate" & i
' intMissData = intMissData + 1 'Added on March 10 2007
Me("txtFollowupDate" & i).SetFocus '3rd iteration - added on March 11 2007
Cancel = True '3rd iteration - added on March 11 2007
Exit For
End If
End If
If Not IsNull(TheForm("txtAmtReceived" & i)) Then
If Not IsDate(TheForm("txtDateReceived" & i)) Then
strName = "txtDateReceived" & i
' intMissData = intMissData + 1 'Added on March 10 2007
Me("txtDateReceived" & i).SetFocus '3rd iteration - added on March 11 2007
Cancel = True '3rd iteration - added on March 11 2007
Exit For
End If
End If
Next
If strName <> "" Then
MsgBox "Data is required in " & strName & "," & vbCr & _
"please ensure this is entered.", _
vbInformation, "Required Data..."
RequiredData = True

Else
RequiredData = False
End If
End Function


I will try RuralGuy's suggestion.

 

[brunces]

SpeedThink,

Could you upload your file for us to take a look at it? Maybe we could help you better.

 

[MicroE]

Did you try RuralGuy suggestion? It appears that is the only issue I see in your original Post. That would prevent the user from saving the record and moving to the next one.

Cancel = RequiredData(Form)

Validation Example:
http://www.databasedev.co.uk/validate_textbox.html

 

[SpeedThink]

Attached is the file that I am still not able to get the validation to work as planned on the "Review Accounts" page.

For example, if txtAmtSubmitted is populated, then txtDateSubmitted and txtFollowupDate must be populated. Note, there are 4 columns for data entry - txtAmtSubmitted1, txtAmtSubmitted2, txtAmtSubmitted3, txtAmtSubmitted4, txtDateSubmitted1, txtDateSubmitted2, and so on.

Another validation is that txtDateReceived must be populated if txtAmtReceived is populated.

Any ideas as to why this is not working as planned?

 

[boblarson]

First of all, in your code, I noticed that you used:

Call RequiredData(Form)

in your BeforeUpdate event of the form.

You then have:

Public Function RequiredData(ByVal TheForm As Form) As Boolean
'Dim intMissData As Integer ' Added on March 10 2007 1st option
Dim i As Integer
Dim strName As String
'intMissData = 0 'Added on March 10 2007
For i = 1 To 4
...

and this part would not be available outside of the function:

Dim intMissData As Integer

because you need to use

Public intMissData As Integer

in the declarations section of the module, in order to refer to it, and get a value, outside of the module. The way you currently have it, would only be available inside of the function.

Next, your function should return true or false, but nowhere in your BeforeUpdate event did you test for true or false.
You would want to change it to:

Private Sub Form_BeforeUpdate(Cancel As Integer)
   If Call RequiredData(Form) = True Then
     Cancel = True
   End If
End Sub

And, as an FYI, within an IF...Then...Else statement you do not use a colon after Else.

 

[SpeedThink]

Access Db

Oops!

Forgot to attach file.