Data Validation

Hi Experts!

I have two issues here :

(1) Minor Issue

I have two forms linked to each other , that is a parent form and a sub form , now I wanted the dates for both forms to be always the same . Therefore , to implement that business rule , I have come up with a VBA code below:

Private Sub TTDate_BeforeUpdate(Cancel As interger)
If Me.TTDate <> Me.parent!Paydate Then
MsgBox " Please ensure that the date on the form above is entered here" , VbOnly, " Wrong Entry"
Cancel = True
Exit Sub
End If
End sub

The above code works but it does not give a signal beep warning! Any Idea here?

(2) Major Issue

I have some controls on both forms that need to have data or are mandatory when doing data capturing , now I'm using the built in forms validations and a message ( " Please note this control cant be blank"), well fair enough when the control is not filled the massage ( " Please note this control cant be blank") pops up , but when you say Okay there is also another  Access Standard message that pops up almost stating the same thing , this what I do not want , I just want my standard message only nothing else , how do I suppress this Access message.

I'm also think of doing away with the built in validation by replacing with mine as follows:

Dim Cancel As Interger
If me.TTDate = " " Then
MsgBox " Please note this control cant be left blank", VBOnly, " Missing Data"
Cancel = True
Exit Sub
End If
End Sub

If this okay with you , then where do I put it or which event property is appropriate for this? Or does it mean that I have paste per each control?

Regards

Chris
LVL 3
Hankwembo Christopher,FCCA,FZICA,CIA,MAAT,B.A.ScDirectorAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

MacroShadowCommented:
To make a beep, you must add the word Beep in your code.
To use data validation in VBA, you should use the before update event.
Hankwembo Christopher,FCCA,FZICA,CIA,MAAT,B.A.ScDirectorAuthor Commented:
To make a beep, you must add the word Beep in your code.

As you can see the code above Where do I put the Beep?????????????

To use data validation in VBA, you should use the before update event.

Do you mean all controls that require validation should have this code????????????
MacroShadowCommented:
>  As you can see the code above Where do I put the Beep?????????????
Private Sub TTDate_BeforeUpdate(Cancel As interger)
    If Me.TTDate <> Me.parent!Paydate Then
        Beep
        MsgBox " Please ensure that the date on the form above is entered here" , VbOnly, " Wrong Entry"
        Cancel = True
        Exit Sub
    End If
End sub

Open in new window


>  Do you mean all controls that require validation should have this code????????????
No. Use the form's BeforeUpdate event.
Hankwembo Christopher,FCCA,FZICA,CIA,MAAT,B.A.ScDirectorAuthor Commented:
Okay Thanks  for the first one that is fine!

Now the second one I have 8 controls that are mandatory is it Okay I list them like below:

Dim Cancel As Interger
If me.TTDate = " " Then
MsgBox " Please note this control cant be left blank", VBOnly, " Missing Data"
Cancel = True
Exit Sub
ElseIf me.CustomerID = " " Then
MsgBox " Please note this control cant be left blank", VBOnly, " Missing Data"
Cancel = True
Exit Sub
End If
End Sub

Under the form's before update??

I'm current not in the lab I will at 7 pm

Regards

Chris
MacroShadowCommented:
Firstly, notice that you are only checking if the control contains a space. I would use Len(control.name) = 0, which essentially means that the length of the data in the control is 0, or in other words, there is no data.

Now as for the validation checking, I would add a predetermined keyword such as "required" or any other word you'd like to the tag property of each control who's data is mandatory. Then loop thru all the controls of the form verifying the existence of data in the required fields using code like the following:

dim ctl as conrol
dim strControlsMissingData as string
for each ctl in form.controls
    if ctl.tag = "required" then
        if len(ctl.value) = 0 then 
            strControlsMissingData = strControlsMissingData & ", " & ctl.name 
        end if
    end if
next

if Len(strControlsMissingData) > 0 then
    beep
    msgbox "the following controls are missing data:" & vbcrlf & strControlsMissingData, VBCritical, " Missing Data"
end if

Open in new window

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.