VBA to validate multiple fields in a form beforeUpdate... I think

[fau5tu5]

I've been searching here and afar for how best to do this. I think I would prefer to have it in the VBA as opposed to simply native Access rules/design but I could be wrong. Here Goes.

I have a single form with a variety of fields (not all) that I need validated to be "Not Null" (meaning I dont want people to leave them blank)

They range from text inputs, to listboxes.

I also have a "Save Form" button as well as a "EDIT" button which allows the user to correct edited entries, (which are non-editable by default).

I would prefer not to use the "Required" function on the table for two reasons:
1. The error message isnt adaptable (to my knowledge) to tell the user what he/she failed to input as it references the Table.Field_Title and not the "Label" on the Form.
2. It does not redirect the "FocusOn" back to the field in the form that needs attention.


I'm not as fluent in VBA to likely understand if you simply give me a "hint" , but would much appreciate and actual CODE: example or a link to a good example/template/tutorial.

Again.. The Goal is that when a User fails to fill out certain fields in the FORM, he/she will not be allowed to save the form, but be redirected to fill in the mandatory fields.

Appreciation in advance

 

[pbaldy]

This should get you started:

http://www.baldyweb.com/BeforeUpdate.htm

 

[fau5tu5]

Thats Pretty much the type of thing I'm looking for. And I will try it.

Now the next question is, where do I put that bit of code?

Should it be associated with "Each" field that requires validation as an "EventProcedure"?

Or is there a way to string a bunch of these together in one place?

Or do I need to associate them with the Click() for the "Save Record" button and "Close" button?

Also is there anything else I need to add to make the suggested code run?

Thanks

 

[pbaldy]

I would use the before update event of the form, rather than each control. That's mentioned in the link, but perhaps I should make it more prominent. If you validate in each control, you run the risk that the user mouses over it or just tabs through it. In either case the update events don't fire. Whether you have a save button or not, the before update event of a bound form will fire before the record saves.

 

[fau5tu5]

Ok. So here is what I have in the VBA of the form (I've stripped it down to just this as I want to get this working properly first)

Private Sub Command210_Click()
Me.AllowEdits = False
End Sub

Private Sub Command211_Click()
Me.AllowEdits = True

End Sub

Private Sub Form_BeforeUpdate(Cancel As Integer)
If Len(Me.primary_serious & vbNullString) = 0 Then
  MsgBox "You need to fill out Primary Event Serious"
  Cancel = True
  Me.primary_serious.SetFocus
End Sub



Private Sub saverecord_Click()
If Me.Dirty Then Me.Dirty = False

End Sub

( "primary_serious" in this case is the field I am trying to force to not be allowed to be left null/blank/empty etc.)

This had no affect. I start a new record, fill some out, leave primary_serious blank, and hit save,.. and it saves.

Where am I blowing it?

(Also when I get this working, I assume I can simply string on more of them with "ElseIf" replacing "If" ? or is there more to it than that?)

 

[pbaldy]

That shouldn't even run, because there's a compile error (no End If). Is the code associated with the event (if you go into the form properties and click on the ellipsis to the right of Before Update does it take you to that code)?

In answer to your question, yes, it's as simple as adding additional If/Then blocks or ElseIF clauses to that one.

 

[fau5tu5]

Explain what you mean as to a person not overly familiar with the VBA. (as that is what I am)

Currently you are right, it isnt running.. (and I think I stripped something out I shouldnt have as my "Edit" "End Edit" buttons arent functioning either as they were. It was such that you could not edit previously entered and saved records unless you clicked "EDIT" which then you could until you saved and closed, at which point, reopening would put you back into the non-editable mode for existing records and you could only create new ones.


could you include what "End If" I should put there and how it should look?

 

[pbaldy]

It should look like the code in my link.

Did you try this:

...if you go into the form properties and click on the ellipsis to the right of Before Update does it take you to that code....

Can you post the db?

 

[fau5tu5]

I added the end if and went back 3 versions to when the edit controls last worked.. and now it does work.

Not sure what I screwed up there... but ... I will now try adding the "ElseIf"

unfortunately I cannot post the db as it currently contains confidential info.

I may need to recreate a stripped down version of it for this purpose specifically as well as just testing.

Unfortunately it was "DONE" "Finished" and then management who had been blowing it off as "whatever you want to do with it is fine" did what they always do once something is done.... The got ahold of it and decided that NOW they wanted some changes , even though they were given months to give their input on what should be included and how it should be set up...

But I'm sure you are familliar with that.

 

[pbaldy]

But I'm sure you are familliar with that.

Intimately.

It sounds like the code got disassociated with the events, which I've seen happen once in a great while. Post back if you get stuck.

 

[fau5tu5]

the ElseIf is working perfectly...

Your solution does EXACTLY what I wanted/needed..

Now I am just going to go back through the updates over the last 3 versions rather than trying to hunt down what happened.

The one last thing (ok, the one last thing... as of now.. who knows how many "one last thing"s will be added monday) is:

To be able to validate between two entries to prevent BOTH from being "YES"
Both can be "NO", either can be "Yes" but just not "BOTH YES".

Any quick solution/suggestions? (admittedly I havent scoured the forum or anywhere for the answer, yet, but you seem to have some pretty clean and straight forward solutions, so I figured I might as well ask first).

Thanks.

 

[pbaldy]

Two fields within the same record or two separate records? I assume two fields:

If Me.Control1 = "Yes" And Me.Control2 = "Yes" Then

 

[fau5tu5]

Yes. two fields in same record.

So I should likely add this to the "BeforeUpdate" as well?

Or should I associate this with one of those specific fields?

Such as:

If If Me.Control1 = "Yes" And Me.Control2 = "Yes" Then
  MsgBox "both Cotnro11 and Control2 cannont be Yes"
  Cancel = True
  Me.Control1.SetFocus
End If

 

[pbaldy]

I'd add it to the form's before update. If you put it in a control, you have to put it in both, since you can't normally be sure which one the user will complete first. If you put it behind control 2, assuming that control 1 will already be filled out, sooner or later some user is going to fill out 2 first. Users have an uncanny ability to do the unexpected.

 

[fau5tu5]

Users have an uncanny ability to do the unexpected.

I like to call that the Law of Tragedy Magnetism:

Example: Place One Post anywhere in an open field full of people whose goal is to run freely, and not only will someone eventually run into the post, but eventually the majority will end up congesting around it.

Example 2: Place a thousand items out labeled "Safe" and ONE, labled "Danger" or "Do Not Touch" and there is no question where the majority of the focus will be.

 

[JSH20120710]

I love the Laws of Tragedy Magnetism.

Now to my question. This was posted earlier:
"I'd add it to the form's before update."

Excellent. Now for another brand new barely out of the box user, how do I get to the form's beforeupdate. Please go very slowly like you are talking to your grandmother coaching her how to disarm a nuclear warhead.

 

[pbaldy]

http://www.baldyweb.com/FirstVBA.htm

 

[JSH20120710]

so do I understand that "on click" means "the form's before update"?

 

[pbaldy]

No, that just demonstrates how you find where to put code. You'd want the form's properties, one of which is Before Update.

 

[JSH20120710]

LOL
That much I knew.

I was trying to find out (what I now finally know) that I have to look at the pulldown list at the top of that panel and choose "Form".

moving forward and I thank you again.