stranger problem

[vagelisr]

Hi to all......

I have a very stranger problem
I have a access 2003 form for add in a multi user enviroment (3 users)
To be absolutely sure that the user will fill all the fields i want i have make a subroutine that check 7 fields Validate_Record

After 1 month without any problem today 1 user call me and said that he has a problem.
Somehow he add a record in the table without fill all my required fields


My close form sub is

Private Sub Close_Form_Click()
On Error GoTo Err_Close_Form_Click
Dim canVal As Integer
If Parm_Open_Form = 3 Then
DoCmd.Close
Exit Sub
End If

If Me.Dirty = True Then
canVal = MsgBox("Do you want to save the changes.", vbYesNo + vbExclamation, "Close Form")
If canVal = vbNo Then
Me.Undo
Else
Error_Flag = False
Call Validate_Record
If Error_Flag = True Then Exit Sub
End If
End If
DoCmd.Close
Call Forms("customer").Requery_customer

Exit_Close_Form_Click:
Exit Sub
Err_Close_Form_Click:
MsgBox Err.Description
Resume Exit_Close_Form_Click

End Sub

Anyone has any idea why this happend???

Thanks and regards and sorry for my English.....

 

[Kowalski]

Hi
Instead of doing your checks with VBA code, it is better so set validation rules on the database. Therefore the database will force the rule regardless of how people are entering the data.

 

[missinglinq]

Hi
Instead of doing your checks with VBA code, it is better so set validation rules on the database.

Not true! The vast majority of experienced developers do this kind of Validation at the Form level, using VBA. Not even sure what you mean by "it is better so set validation rules on the database!"

I suspect that the problem here is that the OP is trying to change the way Access inherently functions! They shouldn't be using Validation code in a Command Button that closes the Form. I expect that the user in question either moved to another Record or closed the Form using one of Access' own 'close' buttons/menu items, such as the big X in the upper right-hand corner. Doing so would bypass the Validation code here and allow a partial Record to be saved.

This type of code belongs in the Form_BeforeUpdate event, which fires immediately prior to a Record being Saved. Simple, generic code for this kind of thing would be something like

Private Sub Form_BeforeUpdate(Cancel As Integer)
 If IsNull(Me.Control1) Then
   MsgBox "Control1 Must Not Be Left Blank!"
   Cancel = True
   Control1.SetFocus
   Exit Sub
 End If
 If IsNull(Me.Control2) Then
   MsgBox "Control2 Must Not Be Left Blank!"
   Cancel = True
   Control2.SetFocus
   Exit Sub
 End If
End Sub

As I said, this is very basic code, delineating the Controls to be checked. You could also Loop thru all Controls and check them, or particular Controls (using the Tag Property) without having to write separate code for each Control.

Linq ;0)>

 

[vagelisr]

Hi again.
First of all thanks for your answers.....
My friend missinglinq the red X in the right corner is disabled for this reason.
Also disable is the navigation records button.
To close the form the user has to press the Close_Form Button.

Thanks and regards......

 

[vagelisr]

I have made a HUGE mistake......
My friend missinglinq has absolutely right.
When the user press the Red X to close the Access (The red X in all of my forms is disable) Access close without validating.

Now try to disable the Red X with macro code from Microsoft

Option Compare Database
Option Explicit
Private Declare Function GetSystemMenu Lib "user32" (ByVal hWnd As Long, _
ByVal bRevert As Long) As Long
Private Declare Function EnableMenuItem Lib "user32" (ByVal hMenu As _
Long, ByVal wIDEnableItem As Long, ByVal wEnable As Long) As Long
Const MF_GRAYED = &H1&
Const MF_BYCOMMAND = &H0&
Const SC_CLOSE = &HF060&
Public Function SetEnabledState(blnState As Boolean)
Call CloseButtonState(blnState)
Call ExitMenuState(blnState)
End Function
'Disable the Menu Option
Sub ExitMenuState(blnExitState As Boolean)
'Application.CommandBars("File").Controls("Exit").Enabled = blnExitState
End Sub
'Disable the Close Button Option
Sub CloseButtonState(boolClose As Boolean)
Dim hWnd As Long
Dim wFlags As Long
Dim hMenu As Long
Dim result As Long

hWnd = Application.hWndAccessApp
hMenu = GetSystemMenu(hWnd, 0)
If Not boolClose Then
wFlags = MF_BYCOMMAND Or MF_GRAYED
Else
wFlags = MF_BYCOMMAND And Not MF_GRAYED
End If

result = EnableMenuItem(hMenu, SC_CLOSE, wFlags)
End Sub

But i receive a run-time error 5 in this line
Application.CommandBars("File").Controls("Exit").Enabled = blnExitState
The problem is in "Controls("Exit") if i remove this the command run OK

Why?????