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.

 

[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!

 

[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.