Validation

[Newbie]

I'm using Access 2003 connecting to SQL Server 2k via DSN. I'm using a form
to add data to one table. When the user fill-in all the info on the form,
they press the add button and a record is added to a table. Then the focus is
set back to the first field on the form which is CboCommCode. I have the
following code in the On Exit of the CboCommCode to enforce that the field
can not be blank.

Private Sub CboCommCode_Exit(Cancel As Integer)

If IsNull(Me.CboCommCode) Or Me.CboCommCode = "" Then
MsgBox "Community Code is required! Enter community code or select from
list!"
Me.Undo
Cancel = True

End If

End Sub

I also have an Close button to close out the form and the code behind it is:

Private Sub cmdExit_Click()
On Error GoTo Err_cmdExit_Click

DoCmd.Close

Exit_cmdExit_Click:
Exit Sub

Err_cmdExit_Click:
MsgBox Err.Description
Resume Exit_cmdExit_Click

End Sub

The problem I'm facing is when the user wanting to quit and close the form,
they will have to enter/select a value into a CboCommCode in order to click
the Close button to exit out of the form.

What code do I need if the Close button click, disregard the validation rule
in the CboCommCode. Your help is greatly appreciated.

 

[Stefan Hoffmann]

hi,

The problem I'm facing is when the user wanting to quit and close the form,
they will have to enter/select a value into a CboCommCode in order to click
the Close button to exit out of the form.

Don't use the On Exit event of the combo box. Use the Before Update
event of your form to validate _all_ your values. If the validation
fails, set Cancel = 1.

mfG
--> stefan <--

 

[Newbie]

hi Stefan, Thank you for your response. The first four values on the form
are required. Community,Building,Unit and Task. I place the validation on
the On Exit event of each field to ensure that the field can not be blank.
Once the user enters a value into the Task field, the validation place on the
On Exit event of the Task field is as follow:

check to ensure Task is not empty, if it is not empty, check the combination
values of the first four fields (Community,Building,Unit,Task) to ensure that
the record is not already exist in the table.


I don't want the user to fill in all the values and realize that the record
has already been entered previously.

any idea?

Thanks

 

[Stefan Hoffmann]

hi,

I don't want the user to fill in all the values and realize that the record
has already been entered previously.

Put something like the following in the on change event of your four
required fields:

Private Sub txtCommunity_OnChange()

Dim Count As Integer
Dim Community As String
Dim Building As String
Dim Unit As String
Dim Task As String

Community = Replace(txtCommunity.Value, "'", "''")
Building = Replace(txtBuilding.Value, "'", "''")
Unit = Replace(txtUnit.Value, "'", "''")
Task = Replace(txtTask.Value, "'", "''")

Count = DCount("*", _
"Table", _
"Community = '" & Community & "' " & _
"AND Building = '" & Building & "' " & _
"AND Unit = '" & Unit & "' " & _
"AND Task = '" & Task & "'")

If Count > 0 Then
MsgBox "Found existing record."
End If

End Sub


mfG
--> stefan <--

 

[Newbie]

Stefan,

I have something very similar to what you post to check if the record is
exist and it is working fine. The issue I'm having is how can a user click
the close button to quit without have to enter or select a value for the
Community combo box. Again this happens because I place a validation On Exit
event to ensure the Community can not be empty. Thanks again for your
patience and response.

 

[AccessVandal via AccessMonster.com]

From what you said,

When the user fill-in all the info on the form,
they press the add button and a record is added to a table. Then the focus is
set back to the first field on the form which is CboCommCode. I have the
following code in the On Exit of the CboCommCode to enforce that the field
can not be blank.

The way I see it, when you close the form, the control looses focus causing
the on exit event to trigger.

If it’s possible, try to set the focus to another control.

Here is another way with a message box.

If IsNull(Me.CboCommCode) Or Me.CboCommCode = "" Then
if MsgBox ("Community Code is required! Enter community code or select
from list!" & vbCrLf _
“Click OK to Close, Click Cancel to input dataâ€, vbOKCancel, “CboCommCodeâ€)
= vbOK Then _
Exit Sub
Me.Undo
Cancel = True

End If

 

[Stefan Hoffmann]

hi,

I have something very similar to what you post to check if the record is
exist and it is working fine. The issue I'm having is how can a user click
the close button to quit without have to enter or select a value for the
Community combo box. Again this happens because I place a validation On Exit
event to ensure the Community can not be empty.

I thought, it's obvious: it can't work.

When your user clicks on another control the exit event is always fired
before the other events.


mfG
--> stefan <--