Form validation - stop after first failure

[garywood84]

I have a series of If statements that run Before Update on a form, to check that required fields have been filled in.

I need the code to stop running when any If statement is true (i.e. the validation fails), to give the user a chance to correct the error before the rest of the validation rules apply to the other fields.

How can I do this?

Thanks,

Gary

 

[rainman89]

put "exit sub" in the section where the statement would evaluate to true

 

[boblarson]

Gary:

I wouldn't do it that way. I would present them a list so they can avoid continuing errors.

Dim blnError As Boolean
Dim strHold As String
 
If Len(Me.textbox1 & "") = 0 Then
   blnError = True
   strHold = "TextBox1" & vbCrLf
End If
 
If Len(Me.TextBox2 & "") = 0 Then
   blnError = True
   strHold = strHold & "TextBox2" & vbCrLf
End If
 
 
If Len(Me.TextBox3 & "") = 0 Then
   blnError = True
   strHold = strHold & "TextBox3" & vbCrLf
End If
 
'.... and so on and then
 
If blnError 
   Msgbox "You missed filling in these fields: " & strHold & vbCrlf & _
"Please fill them in.", vbExclamation, "Validation Error"
   Cancel = True
End If

 

[garywood84]

Thanks rainman89 and boblarson. I've used both of these methods for different forms in my database, and they worked perfectly.

However, I'm now having a problem on one of my forms. When the validation fails, my error message asking the user to enter the details appears correctly. However, I have three fields where the values are selected from a combo box populated by other tables. When any of these fails validation, and Access error appears:

"The Microsoft Office Access database engine cannot find a record in the table '<Lookup table>' with a key matching field <Fieldname>."

Any ideas what this means and, more importantly, how to fix it?!

Thanks,

Gary

 

[boblarson]

Are you trying to have multiple tables updated on a single form? If so, you probably need to go to a main form/subform setup. Updating multiple tables from a single form is typically problematic. It can be done in some circumstances but I don't like to muddy the waters, so-to-speak.

 

[garywood84]

Are you trying to have multiple tables updated on a single form? If so, you probably need to go to a main form/subform setup. Updating multiple tables from a single form is typically problematic. It can be done in some circumstances but I don't like to muddy the waters, so-to-speak.

Yes and no! There is a subform on my main form, but it's tied to the primary key of the main form. The fields that are generating this error are just using other details to look up the values they list: they only update a single table.

 

[boblarson]

So is the same table in the main form recordsource AND the subform recordsource?

 

[garywood84]

So is the same table in the main form recordsource AND the subform recordsource?

No. Essentially, I have four (relevant) tables:

tblEvents
tblMaterials
tblCentre
tblProgramme

The main form has tblEvents as its record source. On that form, there is a combo box called "Programme" which looks up programmes from tblProgramme and stores the ProgrammeID from that table in the ProgrammeID field of tblEvents. There's also a combo box that does the same thing for centre.

What's selected in the "Programme" combo box determines what's listed in the subform, which displays records from tblMaterials. (Basically the materials required to deliver the Programme, which are identified by tblMaterials giving the ProgrammeID in one field and the material name in another).

Does this make sense?

 

[boblarson]

It makes sense a bit but I can't, from the description, give you a why the problem is happening. Any chance of an upload of the database (with bogus data of course)? That would help me because I'm very VISUAL and TACTILE and so it helps if I can see it and play with it.

 

[garywood84]

It makes sense a bit but I can't, from the description, give you a why the problem is happening. Any chance of an upload of the database (with bogus data of course)? That would help me because I'm very VISUAL and TACTILE and so it helps if I can see it and play with it.

Hi Bob,

Thanks for this. I'm attaching a zip file with a sample of my DB. I've removed some irrelevant tables, and filled the rest with dummy data. Please ignore the fact that the form isn't very pretty - it's work in progress and I'll make it look nice once it works!

Thanks again for looking at this for me,

Gary

 

[boblarson]

Just as I thought. You have 3 tables in the query which underlies the main form. You should NOT have them. Your record source for the main form should only be

Select * FROM tblEvents

with any sorting you want on it.

You don't need to include the tables which the combo's have as their row source. They already have them in their row source.

That was your problem.

 

[garywood84]

You don't need to include the tables which the combo's have as their row source. They already have them in their row source.

That was your problem.

Thanks, so much for this, Bob - much appreciated.

Just one last thing. Now that this problem is fixed, my validation isn't quite working right. My error message correctly appears if there is anything wrong with the input (i.e. it's missing or numbers are equal to 0), but the record gets updated anyway! So, the validation is pointing out the problem but then letting you continue regardless! I need the BeforeUpdate event to cancel the update it the validation fails (i.e. if any of the if statements is true). How can I do this?

 

[garywood84]

Ah - sorry - ignore my last post about the validation not preventing the update. I'd inadvertently set Cancel = True to False! It's fixed now!

Many thanks again for your help.

Gary

 

[boblarson]

Glad we could help.