Form Validation

[Andy Roberts]

I have a form with 4 controls, 32 of which I need to make compulsory which I
seem to have managed using the controls validation rule / text properties.
I have a button which checks the validation and then opens a new form based
on a value in a txt box on the original form.

The problem I have is the validationrule flags the missing data but the new
form still opens. How do I control this?

Andy
Access 2007

 

[Linq Adams via AccessMonster.com]

I'm sorry, Andy! Maybe I'm confused, but what's wrong with this sentence?

I have a form with 4 controls, 32 of which I need to make compulsory

Also, what code are you using to do the validation?

We can't really trouble-shoot something we can't see.

 

[Andy Roberts]

Apologies for my typing.

It should read 4 controls, 3 of which I need to make compulsory

The code I'm using is

Private Sub cmdAddTendertoSystem_Click()
On Error GoTo Err_cmdAddTendertoSystem_Click

If ValidateRecord = False Then
Cancel = True
End If

Me.Visible = False

DoCmd.OpenForm "frmTenders", , , "[TenderNo] = " & Me.txtTenderNo

Exit_cmdAddTendertoSystem_Click:
Exit Sub

Err_cmdAddTendertoSystem_Click:
MsgBox Err.Description
Resume Exit_cmdAddTendertoSystem_Click

End Sub

 

[bismuth83]

How about adding the line "GoTo Exit_cmdAddTendertoSystem_Click" after
"Cancel=True" inside your If-Then conditional. It should skip over
the OpenForm command if not validated.

Apologies for my typing.

It should read 4 controls, 3 of which I need to make compulsory

The code I'm using is

Private Sub cmdAddTendertoSystem_Click()
On Error GoTo Err_cmdAddTendertoSystem_Click

 If ValidateRecord = False Then
Cancel = True
End If

    Me.Visible = False

    DoCmd.OpenForm "frmTenders", , , "[TenderNo] = " & Me.txtTenderNo

Exit_cmdAddTendertoSystem_Click:
    Exit Sub

Err_cmdAddTendertoSystem_Click:
    MsgBox Err.Description
    Resume Exit_cmdAddTendertoSystem_Click

End Sub

I'm sorry, Andy! Maybe I'm confused, but what's wrong with this sentence?

Also, what code are you using to do the validation?

We can't really trouble-shoot something we can't see.

Answers/posts based on Access 2000/2003

Message posted viahttp://www.accessmonster.com

 

[Andy Roberts]

That seems to work great for the date control where i have an IsNotNull
validation rule. However the IsNotNull wont work on cbo or txt boxes (the
cbo is bound to a number field and the txt to a text field)




How about adding the line "GoTo Exit_cmdAddTendertoSystem_Click" after
"Cancel=True" inside your If-Then conditional. It should skip over
the OpenForm command if not validated.

Apologies for my typing.

It should read 4 controls, 3 of which I need to make compulsory

The code I'm using is

Private Sub cmdAddTendertoSystem_Click()
On Error GoTo Err_cmdAddTendertoSystem_Click

If ValidateRecord = False Then
Cancel = True
End If

Me.Visible = False

DoCmd.OpenForm "frmTenders", , , "[TenderNo] = " & Me.txtTenderNo

Exit_cmdAddTendertoSystem_Click:
Exit Sub

Err_cmdAddTendertoSystem_Click:
MsgBox Err.Description
Resume Exit_cmdAddTendertoSystem_Click

End Sub

I'm sorry, Andy! Maybe I'm confused, but what's wrong with this
sentence?

Also, what code are you using to do the validation?

We can't really trouble-shoot something we can't see.

Answers/posts based on Access 2000/2003

Message posted viahttp://www.accessmonster.com

 

[bismuth83]

You'll probably want to check also for empty strings ("") in the combo
and text boxes.

You can use the Nz() function to check for null and empty strings at
once:
If Nz(Textbox1)="" then
Cancel = True
End If

That seems to work great for the date control where i have an IsNotNull
validation rule.  However the IsNotNull wont work on cbo or txt boxes (the
cbo is bound to a number field and the txt to a text field)


How about adding the line "GoTo Exit_cmdAddTendertoSystem_Click" after
"Cancel=True" inside your If-Then conditional.  It should skip over
the OpenForm command if not validated.

Apologies for my typing.

It should read 4 controls, 3 of which I need to make compulsory

The code I'm using is

Private Sub cmdAddTendertoSystem_Click()
On Error GoTo Err_cmdAddTendertoSystem_Click

If ValidateRecord = False Then
Cancel = True
End If

Me.Visible = False

DoCmd.OpenForm "frmTenders", , , "[TenderNo] = " & Me.txtTenderNo

Exit_cmdAddTendertoSystem_Click:
Exit Sub

Err_cmdAddTendertoSystem_Click:
MsgBox Err.Description
Resume Exit_cmdAddTendertoSystem_Click

End Sub

messagenews:933a53a3a59b3@uwe...

 

[tina]

Private Sub cmdAddTendertoSystem_Click()

On Error GoTo Err_cmdAddTendertoSystem_Click

If ValidateRecord = False Then
Cancel = True
End If

you can't cancel a Click event, so the line

Cancel = True

has no effect on the code at all.

instead, try

If ValidateRecord = True Then
DoCmd.OpenForm "frmTenders", , , "[TenderNo] = " & Me.txtTenderNo
End If

hth

Apologies for my typing.

It should read 4 controls, 3 of which I need to make compulsory

The code I'm using is

Private Sub cmdAddTendertoSystem_Click()
On Error GoTo Err_cmdAddTendertoSystem_Click

If ValidateRecord = False Then
Cancel = True
End If

Me.Visible = False

DoCmd.OpenForm "frmTenders", , , "[TenderNo] = " & Me.txtTenderNo

Exit_cmdAddTendertoSystem_Click:
Exit Sub

Err_cmdAddTendertoSystem_Click:
MsgBox Err.Description
Resume Exit_cmdAddTendertoSystem_Click

End Sub

I'm sorry, Andy! Maybe I'm confused, but what's wrong with this sentence?


Also, what code are you using to do the validation?

We can't really trouble-shoot something we can't see.

 

[Andy Roberts]

Now I'm Confused...

I'll re-explain as I think I'm getting two different solutions from the
group to the same problem and I think its just a case of putting the correct
syntax together.

I have a form called frmAddTender which has 5 controls, txtTenderNo,
txtReceivedDate, cboHowReceived, cboClientID and txtPostcode. The idea is
that all 5 controls get filled in and a button on the form saves the record
and opens a much larger form with loads of controls. The first 4 controls
are compulsory and when the button is clicked the form is checked to see
that the first 4 controls have been filled in (the fifth control is optional
as the user may not know the postcode at this stage).

I have used the Validation Rule & Validation Text properties of each control
which I think is wrong and the best way will be with code. What I want to
happen is when the button is clicked the form controls are checked to see if
they are blank or not and a message for each control pops up to inform the
user of the missing data e.g. "you must add a received date to continue"
The user is then taken to the txtReceivedDate control to add a date. Once
added they will click the button and the form is checked again and any other
controls highlighted. If the form is fine then it will close and frmTenders
will open.

I have had it checking the controls but also opening the form without
stopping to allow the user to enter data.

my code (which I know is wrong) looks like this at the moment (its on the
click event of the button):-

Private Sub cmdAddTendertoSystem_Click()
On Error GoTo Err_cmdAddTendertoSystem_Click

If ValidateRecord = False Then
' Cancel = True
GoTo Exit_cmdAddTendertoSystem_Click
End If
If Nz(cboHowReceived) = "" Then
Cancel = True
End If
Me.Visible = False

DoCmd.OpenForm "frmTenders", , , "[TenderNo] = " & Me.txtTenderNo

Exit_cmdAddTendertoSystem_Click:
Exit Sub

Err_cmdAddTendertoSystem_Click:
MsgBox Err.Description
Resume Exit_cmdAddTendertoSystem_Click

End Sub


Regards

Andy
Access 2007 Win XP Pro

Private Sub cmdAddTendertoSystem_Click()
On Error GoTo Err_cmdAddTendertoSystem_Click

If ValidateRecord = False Then
Cancel = True
End If

you can't cancel a Click event, so the line

Cancel = True

has no effect on the code at all.

instead, try

If ValidateRecord = True Then
DoCmd.OpenForm "frmTenders", , , "[TenderNo] = " & Me.txtTenderNo
End If

hth

Apologies for my typing.

It should read 4 controls, 3 of which I need to make compulsory

The code I'm using is

Private Sub cmdAddTendertoSystem_Click()
On Error GoTo Err_cmdAddTendertoSystem_Click

If ValidateRecord = False Then
Cancel = True
End If

Me.Visible = False

DoCmd.OpenForm "frmTenders", , , "[TenderNo] = " & Me.txtTenderNo

Exit_cmdAddTendertoSystem_Click:
Exit Sub

Err_cmdAddTendertoSystem_Click:
MsgBox Err.Description
Resume Exit_cmdAddTendertoSystem_Click

End Sub

I'm sorry, Andy! Maybe I'm confused, but what's wrong with this sentence?

I have a form with 4 controls, 32 of which I need to make compulsory

Also, what code are you using to do the validation?

We can't really trouble-shoot something we can't see.

 

[bismuth83]

I think we're just giving different ways of doing the same thing.
Considering Tina's suggestion, you can try something like this. For
clarity, I've replaced the ValidateRecord() function for each separate
control:


Private Sub cmdAddTendertoSystem_Click()
On Error GoTo Err_cmdAddTendertoSystem_Click

If Nz(txtTenderNo) <> "" Then
If Nz(txtReceivedDate) <> "" Then
If Nz(cboHowReceived) <> "" Then
If Nz(cboClientID) <> "" Then
Me.Visible = False
DoCmd.OpenForm "frmTenders", , , "[TenderNo] = " &
Me.txtTenderNo
End If
End If
End If
End If

Exit_cmdAddTendertoSystem_Click:
Exit Sub

Err_cmdAddTendertoSystem_Click:
MsgBox Err.Description
Resume Exit_cmdAddTendertoSystem_Click

End Sub