Preventing wrong user input (1 Viewer)

[Sreemike]

I have a form where the user has to enter numbers in four fields. If we assume these number fields to be a, b, c and d. But I need to ensure (b-a) is higher than (d-c). So after update of the last input filed, I wrote the following vba:
Dim a,b,c,d As Double
If (d - c) > (b - a) Then
MsgBox "Check your values", vbInformation, "Oops...Wrong figure"
End If
field.SetFocus

This will do the trick. And the curser remains in the filed. But the user can still save the form after clicking the OK button from the msgbox. Is there any way I can add a code that prevent the user from save/close the form without entering the correct number.

 

[plog]

A bound form will automatically save the entered data into the table, therefore you need to make your form an unbound form. Next you need to create a 'Save Button' on it that executes VBA code which will execute an INSERT query to manually save the form's data to the table.

 

[John Big Booty]

Try the following in the form's Before Update event;

[COLOR="Green"]'Check that there are no blank fields[/COLOR]
If IsNull(Me.a) Or IsNull(Me.b) Or IsNull(Me.c) Or IsNull(Me.d) Then
     MsgBox "Please ensure that fields a,b,c,d contain numeric values"
     Cancel = True
     Exit Sub
End If

If (Me.d - Me.c) > (Me.b - Me.a) Then
     MsgBox "Check your values", vbInformation, "Oops...Wrong figure"
     Cancel = True
End If

 

[missinglinq]

The belief that Unbound Forms are necessary in order to do data validation before "committing" data to a Table is simply not true!

Any "checks" you need to do before saving the data can be done using the Form_BeforeUpdate event of a Bound Form, as JBB suggested, and Cancel = True to stop the saving of the record until missing/erroneous data is dealt with!

A big part of the reason to use Access for database development is the speed with which it can be created, using Bound Forms. Several developers I know, experienced in both Visual Basic database development and Access development, estimate that development using Unbound Forms by experienced developers takes two to three times as long as it does when using Access and Bound Forms.

If you insist on using unbound forms, you'd be far better off using a straight VB or C++ front end with a SQL Server or Oracle back end.

• You can create an EXE file which gives total protection to your code/design
• You can distribute the db to PCs without a copy of Access being on board
• Your data security is far, far better than anything you can do in Access

Linq ;0)>

 

[plog]

Thanks. I can definitely attest that it takes longer to build an unbound form, but that was the only way I knew. I will definitely try this method next time I need to do this.

 

[Sreemike]

Thanks John. That works. But the problem is the form in question is one of seven tabs form. So even if there is no need to fill in that particular tab form, on saving any of the other forms, the msgbox appears asking to fill in a,b,c & d. Any help on how to avoid this?

 

[gemma-the-husky]

you have various options

you can valudate an individual field before accepting that field's input, using the control's before_update event

you can validate everything before saving the entire record in the forms before_update event

I expect your tabs are jumping into a dfifferent form (sub-form?) which would cause the record for the container form to save, hence you get the before_update event firing.

you just have to plan your interface carefully, i think, to get the effect you want.

 

[missinglinq]

...the form in question is one of seven tabs form...

At this point, we need to know whether Dave's assumption about each of the Tabs having a Subform is correct, or if the Pages are simply being used to save space and have data from a single RecordSource. Knowing this will determine how we help you/how you need to proceed.

If having data in the a/b/c/d is not mandatory, you can simply omit the first part of John's code where you're checking these Controls for data.

Linq ;0)>

 

[Sreemike]

I did manage to use the validation rule on the filed property to get the desired effect. Thanks Dave. But can I perform the same with VBA script? I think writing code with VBA rather than altering validation rule on the property pane would prove more flexible in the database. I am at a loss as to the vba code to create a validation rule.... any help would be much appreciated.

 

[gemma-the-husky]

in one of the update events, you add whatever code you want

sub control_beforeupdate(cancel as integer)
if sometest = false then
    msgbox "validation failed"
[COLOR=red]    cancel = true[/COLOR]
    exit sub
end if

setting cancel=true is the main point, as it causes the update to be rejected. the value is not accepted until sometest evaluates as true

sometest can be another function, or a simpler test