How do I allow a new record to be created (by button push) on a form, while requiring the user change one value from its default?

[Papa_Bear1]

I'm very frustrated that the many years of experience with Access VBA, still leaves me challenged to do the simplest things.

I have a simple form, connected to a simple table, and I want to perform a simple process - just add a new record.
I'm trying to avoid the scenario where the user keeps adding empty new records.

I have a "New" button to create a new record, I want the focus of the form on the new record, I want the focus to be on a particular control, and I already have it set up to fill a name field/control with a default value (like "<Enter new value>" kind of thing). I want the focus to STAY on that control if the user does not change that value.

Trying to create a new record alone has been ridiculously difficult. I've found that I cannot use GotoRecord ,, acNewRec. It simply does not work - but anyway - I digress. I finally have the new record created and focus on that record.

But, at this point - I CANNOT find a way to keep the focus on that one control (if user leaves default value alone).
> I've tried On Lost Focus - followed by Me.Undo. No go.
> I've tried On Lost Focus - followed by DoCmd.CancelEvent, but Nope - doesn't work.
> I've tried AfterUpdate, but that of course can't work - because it won't catch when they didn't update it - which is what I care about.
> I thought maybe I could trick it by having it all done at the table/data layer - with a field validation 'against' the default value. Of course - that can't work - it won't even allow the value to exist in the first place.

Would very much appreciate some ideas.

 

[The_Doc_Man]

You can trap an unwanted condition by putting code in the Form_BEFOREUPDATE event, which can be canceled (thus disallowing the user to save anything you didn't want saved.) Put your code to do your testing in the BEFOREUPDATE event. The documentation says to return a value of -1 to the Cancel parameter (an INTEGER, not a LONG) but that is just because Boolean is a typecast of INTEGER.

You would of course want to include some sort of message box in the code, but the simplest part is this test to see if your field SpecialField has been changed.

Private Sub Form_BeforeUpdate( Cancel As Integer)
...
Cancel = (SpecialField.Value = SpecialField.OldValue)
If Cancel <> 0 Then
    MsgBox {write a nasty message}, vbOKOnly, {a nasty short message}
End If
...
End Sub

You can certainly do more than one test, by the way, but your text suggests you really only care about one particular control.

If you wanted more than this, provide more details.

 

[Papa_Bear1]

You can trap an unwanted condition by putting code in the Form_BEFOREUPDATE event, which can be canceled (thus disallowing the user to save anything you didn't want saved.) Put your code to do your testing in the BEFOREUPDATE event. The documentation says to return a value of -1 to the Cancel parameter (an INTEGER, not a LONG) but that is just because Boolean is a typecast of INTEGER.

You would of course want to include some sort of message box in the code, but the simplest part is this test to see if your field SpecialField has been changed.

Private Sub Form_BeforeUpdate( Cancel As Integer)
...
Cancel = (SpecialField.Value = SpecialField.OldValue)
If Cancel <> 0 Then
    MsgBox {write a nasty message}, vbOKOnly, {a nasty short message}
End If
...
End Sub

You can certainly do more than one test, by the way, but your text suggests you really only care about one particular control.

If you wanted more than this, provide more details.

OK - Excellent!
I will give that a try.
(I admit I've seldom used "BeforeUpdate" for anything before - controls or forms. I did not know that forms have this too actually - I only recall ever noticing those for controls! All this time! )

 

[Gasman]

Docmd.GotoRecord for a new record does work?
You just have to get the syntax correct.

Here is one I just knocked up to test on a test form

DoCmd.GoToRecord acDataForm, Me.Name, acNewRec

Built by referring to https://learn.microsoft.com/en-us/office/vba/api/access.docmd.gotorecord

 

[Papa_Bear1]

OK - Excellent!
I will give that a try.
(I admit I've seldom used "BeforeUpdate" for anything before - controls or forms. I did not know that forms have this too actually - I only recall ever noticing those for controls! All this time! )

So - for some reason - it doesn't trigger.
I have:

Private Sub Form_BeforeUpdate(Cancel As Integer)
  
    Cancel = (Me.txtDB_Schema = "<Enter Schema Name>")
    If Cancel <> 0 Then
        MsgBox "Please enter a name for the new schema.", vbOKOnly, "Input Required"
    End If
  
End Sub

I put a breakpoint on Cancel = and after creating the new row, and clicking away - it just never reached that event.
Did I put it in the wrong place?
Note that my goal is the ability to get the focus back to a particular control. I've been able to trap that they left the value alone (using OnLostFocus). So I intend to follow the message box with me.txtDB_Schema.SetFocus basically. That seems to just get ignored.

 

[Gasman]

Not sure BeforeUpdate will run if no Update?
Might need to use the forms Dirty property?

For a new record you could try the BeforeInsert event?

 

[Papa_Bear1]

Docmd.GotoRecord for a new record does work?
You just have to get the syntax correct.

Well - I've certainly used the GoToRecord successfully before - but today - it is not working. It simply would not work. I didn't type actual code/syntax in there - I was just referring to it in general.

My guess is that it may have to do with me using a Split form. I didn't want to mention that because I didn't want all the criticisms for using it. Ha... It's just a very nice way to present a lot of information tabularly - while still offering dynamically enabled/disabled buttons on a per record basis.

I suppose it is possible that my inability to set focus back to the control I want may also be related to weaknesses in Access split form business. I know it is a sort of 'side' capability and not very well supported - so I won't be too surprised or annoyed if I simply can't do it - but it just seems so simple - on the surface at least.

 

[Pat Hartman]

OK - Excellent!
I will give that a try.
(I admit I've seldom used "BeforeUpdate" for anything before - controls or forms. I did not know that forms have this too actually - I only recall ever noticing those for controls! All this time! )

The Form's BeforeUpdate event is the most important event of all the form's events. As Doc has explained, it is the event you use when you want Access to not save a record with missing or invalid data. Looks like you tried every event EXCEPT the one that actually solves the problem. I once removed more than 5000 lines of code from an application where the developer used every other form level event and still couldn't stop Access from saving bad data. And then moved the remaining couple of hundred lines to the various forms' BeforeUpdate events.

Here is a couple of videos as well as a database you can practice with that will help you to understand when various form and control level events fire.

Watch at least one of the videos so you understand how the sample database works so you can follow the logic when you watch form and control events fire.

P

Thread 'Bad Data is Bad for Business #1 and #2'

Aug 11, 2022

I just added #2 --- that link is further down. It is much shorter than #1. I also added a copy of the database I used for the video. Enjoy

I created a database to help people to understand how and why to use the Form's BeforeUpdate event to validate data. @Uncle Gizmo and I made a video to talk about the example. It's about a half hour long. Please take a look if you have time and we'd love to hear your comments.

You should be able to click on play without having to create an account.

Bad Data is Bad for Business - Pat & Tony (screencast-o-matic.com)

I have a...

• Pat Hartman
• Replies: 38
• Forum: Microsoft Access Tutorials

• 

 

[Gasman]

Well - I've certainly used the GoToRecord successfully before - but today - it is not working. It simply would not work. I didn't type actual code/syntax in there - I was just referring to it in general.

My guess is that it may have to do with me using a Split form. I didn't want to mention that because I didn't want all the criticisms for using it. Ha... It's just a very nice way to present a lot of information tabularly - while still offering dynamically enabled/disabled buttons on a per record basis.

I suppose it is possible that my inability to set focus back to the control I want may also be related to weaknesses in Access split form business. I know it is a sort of 'side' capability and not very well supported - so I won't be too surprised or annoyed if I simply can't do it - but it just seems so simple - on the surface at least.

Just tried it on a new split form in it;s Load event.. No issues?

 

[Papa_Bear1]

Just tried it on a new split form in it;s Load event.. No issues?

Hmmm ---- I need to breathe some of your air apparently...

 

[Papa_Bear1]

Hmmm ---- I need to breathe some of your air apparently...

It's just a bit frustrating is all.

It seems the MS motto should be: "We make the impossible easy, and the easy impossible."

I run into this a lot. I've done MUCH more complicated and difficult things in much less time. I've spent WAY more time than should be needed to simply create a new record and enforce an input from a user. This should have been five minutes. Instead, it's turning out to be many hours. Just silly.

 

[Papa_Bear1]

The Form's BeforeUpdate event is the most important event of all the form's events. As Doc has explained, it is the event you use when you want Access to not save a record with missing or invalid data. Looks like you tried every event EXCEPT the one that actually solves the problem. I once removed more than 5000 lines of code from an application where the developer used every other form level event and still couldn't stop Access from saving bad data. And then moved the remaining couple of hundred lines to the various forms' BeforeUpdate events.

Interesting.
Sounds great. I just can't figure out why it won't fire at all. Seems simple enough - Form, BeforeUpdate, event. How can it not fire when I've created a new row in a table/form? We'll see. Thanks!

 

[Pat Hartman]

I run into this a lot. I've done MUCH more complicated and difficult things in much less time. I've spent WAY more time than should be needed to simply create a new record and enforce an input from a user. This should have been five minutes. Instead, it's turning out to be many hours. Just silly.

You have been using Access for years you said. How about trying to understand the event model? You will be a lot more productive and much less frustrated. Maybe you shouldn't blame your failure to understand events on Microsoft.

How can it not fire when I've created a new row in a table/form? We'll see. Thanks!

It fires when either you or Access decide to save the dirty record. Of course, you need to have code in the event or Access thinks you don't care about validation and you have no indication whether the event fired or not.

 

[Papa_Bear1]

You have been using Access for years you said. How about trying to understand the event model? You will be a lot more productive and much less frustrated. Maybe you shouldn't blame your failure to understand events on Microsoft.

It fires when either you or Access decide to save the dirty record. Of course, you need to have code in the event or Access thinks you don't care about validation and you have no indication whether the event fired or not.

I understand the event model sufficiently to get done what I've needed to get done - up to this point. My only criticism of MS is when things that *seem* like they should be easy - they are not. Having said that, I've found the flipside to be true as well - things that we might consider quite tricky, MS makes it easy.

I guess I was thinking that the creation of a new record would inherently result in it being dirty - until it is saved. Thus, wasn't sure why the event didn't fire anywhere along the way from nothing/start to the record being created. If that event should not fire in that timeframe - great. I've learned another aspect of event timing.

 

[Papa_Bear1]

Just to follow up in general on this - just in case anyone is trying to achieve this same kind of control over focus - with a split form.

I was never able to use the LostFocus with the control of interest - to try to pull focus back to it.
Strangely, I WAS able to put the very same logic on the GotFocus for all other controls, and send the focus *back* to the desired control.
One other tidbit on this --- if you click on the form portion - focus goes to that instance. If you click within the datasheet portion, focus goes to that instance of the desired control. I might prefer to always put it on the form - but at this point, beggars can't be choosers.

Not sure why "Pull" did not work, but "Push" did, but I don't particularly care why I guess. I got it to work as desired!

Thanks for everyone's input/insight.

 

[The_Doc_Man]

This is a side note, @Papa_Bear1 - the difference between a "push" and a "pull" is significant in several other situations as well.

For instance if you are in that situation where you must make a brand-new copy of your DB (perhaps due to corruption), there is a Database Tools ribbon option that lets you export data (implicitly, running from the old file) but it ALSO could let you import data (pulling into a new empty file). (The difference is to consider from which file you do the import or export operation.) You get better results if you PULL into the new file because if the old file is corrupted, your export operation has to get past that corruption without crashing. For some reason, the push is more susceptible to corruption problems than the pull operation.

In your case, the opposite export/import direction is how you fixed your problem. Chalk up one more case of directional sensitivity when importing or exporting.

 

[Pat Hartman]

I understand the event model sufficiently to get done what I've needed to get done - up to this point. My only criticism of MS is when things that *seem* like they should be easy - they are not.

But if you use the CORRECT event, things are easy. Events are not random. They are intended for specific types of processing. MS does not do a good job of explaining what it thinks you should be using each event for but believe me, they had a plan. Seeing when they fire by using the tool I provided a link to will help you to actually understand which event you need to use to get the result you want. You can't just put code in random places and expect to have a satisfactory result.

I guess I was thinking that the creation of a new record would inherently result in it being dirty - until it is saved. Thus, wasn't sure why the event didn't fire anywhere along the way from nothing/start to the record being created.

Because the event that fires when you first dirty a record is the on Dirty event. If the record is new, then the BeforeInsert event also fires as soon as you dirty the form.

One other tidbit on this --- if you click on the form portion - focus goes to that instance. If you click within the datasheet portion, focus goes to that instance of the desired control. I might prefer to always put it on the form - but at this point, beggars can't be choosers.

When a form has controls, focus always goes to either the control you clicked into or if you clicked on the form to the first control on the form that receives the focus.

I was never able to use the LostFocus with the control of interest - to try to pull focus back to it.

You can't because the new control has not got the focus yet when this event runs. Therefore it still has the focus so you cannot set the focus to it again.

You may have been using access for years but you don't understand events at all. Please take a look at one of my videos and play with the database that goes with the videos to help you to understand event processing.

When you use the correct event, you can achieve your objective very easily. You simply set Cancel = True to tell Access to not save the record. You display a message indicating the error, move focus back to the control, and exit the sub. I always exit after the first error. Anything else gets too confusing to the user unless you want to change all the labels of the bad controls to red of something like that. It's a nice effect but not worth the effort given that the user will rarely make more than one error once he understands how to use the form correctly.

When you use the wrong event, you almost always need code in several events AND you may still not achieve your objective as you stated originally. The Bad data was still being saved because you didn't stop it. You can stop Access from saving bad data by using ONE event - the BeforeUpdate of the form. Or by adding code to at least four other events because you need to trap all the exit paths. Your choice, one event or four events. And even that isn't 100% if the user can just clost the database.

In rare instances I use the control's BeforeUpdate event for validation. But you still need the code in the form's BeforeUpdate event because code in control events only run when the control gets the focus and in this case, is also changed. The case I use the control event for is when the field is required and must be unique AND I don't want the user to enter all the data in the form and then not be able to save it. So something like SSN. It is required, it must be unique or the record cannot be saved so don't let the user go any further.