Form validation sweep

[Raddy]

I have a piece of code to validate that a client doesn't already exist on a table when the user wants to add.

Private Sub ParentGuardianIDX_AfterUpdate()
If DCount("*", "TblGuardian", "[ParentGuardianID] = ParentGuardianIDX") > 0 Then
MsgBox "Guardian already exists on Guardian File!", vbOKOnly
End If
End Sub

My problem is that the message displays then the form 'ends' without the user being able to rectify the problem.

I will want to validate several fields on the form, and the same thing will happen.

I am from a procedural language background where the program would be written to sweep through the input performing necessary validation then only allowing the user to progress once all problems were 'fixed' or the user abandoned the transaction. To achieve this a global 'error' flag would be used.

My question is whether that is the technique advised in VBA code, and if so how does one deploy it?

 

[JANR]

Dont't use the AfterUpdate event to validate, use BeforeUpdate instead. This event has a Cancel option you can use.

JR

 

[Raddy]

JANR
many thanks - it works!

I added a
"Cancel= "true"
This keeps the cursor on the offending field.

I guess I will have to code similar validation for all the fields?

thanks again

 

[JANR]

I guess I will have to code similar validation for all the fields

Yes you must but to toss unvalidated out the window, you use Form_BeforeUpdate event.

Private Sub Form_BeforeUpdate(Cancel As Integer)
....ValidationCode
If Fail Then
    Cancel = True
    Me.Undo
End if
End Sub

JR

 

[missinglinq]

The general approach/event you use for validation depends on the kind of validation you're doing.

1. To validate that a given control's value falls within certain formats/parameters/values use the ControlName_Before update and use Cancel = True to stay in the offending field if the validation fails, as you've already done.
2. To validate that certain controls actually contain data you would use the Form_BeforeUpdate event.
3. To perform validation that involves multiple controls you would use the Form_BeforeUpdate event.

An example of # 3 would be if you have a StartDate and an EndDate for a project. You would use the Form_BeforeUpdate event to insure that the EndDate was after the StartDate.

 

[Raddy]

Thanks for that too missingling

I am new to VBA - I need to go and lie down - and reread my books

 

[missinglinq]

Good idea, especially since your comment would indicate that you actually read books on Access before starting to create your database! Unfortunately, all too many newbies we get here simply fire up Access and try to create a database, having absolutely no idea of what they need to do, or even what the various objects are named!

The obvious intelligent approach, the one you've wisely chosen, would be to read about Access, then try your hand at making a database, then go back to the books to fill in what you missed!

And if you still can't figure something out, and Access can be cryptic at times, come back here and ask for help. All we ask of posters is that they make an effort, on their own to solve a problem, before coming here.

Good Luck!

Linq

 

[Raddy]

Thanks

I can design database in my sleep, since that is my main skill. It is just implementing the user interface that is tricky. Most input will be cross-tab'ed to preserve integrity. It usually is when the users are setting up static (as opposed to transactional) data that the problems arise - commonly with one's own ignorance of the language.

Anyway - back to climb yet another learning curve - now where did I put those manuals