MS Access Need Help Determining Which Event To Fire When Focus Is Still On Subform

I am struggling with this one. I have a Subtraction button on my subform and when I click it. It Subtracts from my Textbox quantity.
But I have a module that needs to fire off when I am done clicking. I do not want it to Fire off everytime I click it.

Say I have 10 in my text box. I want to click btnSubtract 4 times. txtqty now says 6.

I want my module to fire now If I was to click on a text box in the parent form but the subform btnSubtract still has focus also. So what do I do? Thanks for the help.
LVL 1
Dustin StanleyEntrepreneurAsked:
Who is Participating?
 
PatHartmanConnect With a Mentor Commented:
You would not normally expect the subtotal to update until you left the control so why worry about it when you are clicking the + and - buttons?  You can't have this both ways.  Either you stop forcing the record to save on every click and log the record once when the record is normally saved or you have to come up with some other technique that somehow "knows" when you want to update the log and once the AfterUpdate event for the form has run, the form is no longer dirty and the .value and .oldValue properties are identical so you can't tell which controls, if any, were changed.
0
 
crystal (strive4peace) - Microsoft MVP, AccessRemote Training and ProgrammingCommented:
hi Dustin,

don't quite understand what you want ... once you click on the main form, the subform cannot still have the focus

> "needs to fire off when I am done clicking"
there needs to be some way to know when you are done ... like another button perhaps?

have an awesome day,
crystal
0
 
Dustin StanleyEntrepreneurAuthor Commented:
In the image below you can see my main form and my subform.
If I was to click the up or down button in my subform then click in the description text box or any other text box in the main form. Then the subforms "On Exit" or "On Lost Focus" Event never fires.
there needs to be some way to know when you are done
I would like this to be when I click onto another control in the main form or something. It seems like the main form has focus AND the one record still has focus in the subform also. Thanks for the help.
Untitled.png
0
Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
crystal (strive4peace) - Microsoft MVP, AccessRemote Training and ProgrammingCommented:
hi Dustin,

how about using the Exit (called On Exit in property sheet) event of the subform control to move focus to another control? This is actually code behind the mainform.
   Me.subformControlname.Form.controlname.SetFocus

Open in new window

What are you trying to do? Perhaps the logic is better on the form AfterUpdate event and the record can be saved?

have an awesome day,
crystal
0
 
Gustav BrockCIOCommented:
I cannot replicate this. When I click on anything in the main form, the OnExit event of the subform control fires as expected.

So, I guess, something else is going on.
0
 
PatHartmanCommented:
Then the subforms "On Exit" or "On Lost Focus" Event never fires.
Certain form level events such as these, never fire if there are controls on the form to catch the event.

What do you want to happen if the user sets focus to the subform but doesn't click the up or down buttons?
What do you want to happen if the user moves to a different row in the subform?

There will never be a lost focus event for a subform that has controls, however if a record was updated, there will be an AfterUpdate event for each record.  Since we don't know what the code you want to run is to do, we don't know when it should actually run.  Should it run Before each record is saved?  Should it run After each record is saved?  You say you want it to run when a control on the main form has focus.  Only one control can have the focus so I don't understand the problem you are having.
0
 
Dustin StanleyEntrepreneurAuthor Commented:
The code I am trying to run is "Call ChangedValuesfrmSkusEntry". This is basically code to insert records into a table that keeps track of changes and who changed exactly what on a form.

In all my text box controls in the Main Form I have "Call ChangedValuesfrmSkusEntry" for the AfterUpdate and it works great. But this isn't the same for the btnAdd or btnSubtract because buttons have no AfterUpdate.

I created msgbox "Exited Now" for the OnExit for btnAdd and btnSubtract. This message box only fires if I click on btnadd or btnsubtract and then I click elsewhere in the subform only. If I click in the main form anywhere nothing fires.

Crystal
Perhaps the logic is better on the form AfterUpdate event and the record can be saved?
I completely agree but buttons do not have AfterUpdate.

Gustav
the OnExit event of the subform control fires as expected.
This is a good point and I might be able to work with this. I am trying to currently get the Button's "On Exit" to fire that is on the subform.
 

Pat
What do you want to happen if the user sets focus to the subform but doesn't click the up or down buttons?
Nothing really.

What do you want to happen if the user moves to a different row in the subform?
This is hard to say. If they clicked btnAdd or btnSubtract on the previous record then I would like for "ChangedValuesfrmSkusEntry" to fire. Else Nothing.


I know this is probably hard to understand exactly and i thank you all for the help.


I just tried the afterupdate event in the subform but this fires every time I click the btnadd or btnsubtract button. I don't want my code "ChangedValuesfrmSkusEntry" to run until the user has decided to stop clicking. Say there are 5 quantity and the user wants to add 5 more. I want the "ChangedValuesfrmSkusEntry" to fire at the end(Quantity 10).  This will show in the table the the original value was 5 and user 1 changed the value and the new value is 10. If I use the forms after update it will insert a record for every click.

Original value 5 New value 6
Original value 6 new value 7
Original value 7 new value 8
Original value 8 new value 9
Original value 9 new value 10

Here is what happens when I click the btnAdd or btnSubtract  buttons:
Private Sub btnAdd_Click()

On Error GoTo Errorhandler

 Me!QtyLoc.value = QtyLoc + 1 'Add one to the total

If Me.Dirty Then Me.Dirty = False
 [Forms]!frmSKUsEntry!sbfrmAllConditionsAllLocations.Form.Requery 'Requery the Form to show updated records
  Me!txtQtyChanged = True 'This checks the hidden box txtQtyChanged so we know to fire the update query called ChangedValuesfrmSkusEntry
   Me.Parent!txtNewValue = Me!QtyLoc
Exit Sub
     
Errorhandler:
If Err.Number = 2759 Then
 DoCmd.RunCommand acCmdSaveRecord ' Save the record
[Forms]!frmSKUsEntry!sbfrmAllConditionsAllLocations.Form.Requery 'Requery the Form to show updated records
 Me!txtQtyChanged = True 'This checks the hidden box txtQtyChanged so we know to fire the update query called ChangedValuesfrmSkusEntry
Exit Sub
End If

 MsgBox "Error " & Err.Number & ": " & Err.Description & " in " & _
   VBE.ActiveCodePane.CodeModule, vbCritical, "Error in btnAdd_Click"
 End Sub

Open in new window

0
 
crystal (strive4peace) - Microsoft MVP, AccessRemote Training and ProgrammingCommented:
hi Dustin,

>"I completely agree but buttons do not have AfterUpdate"

yes, you are right -- but buttons do have a Click event with code that changes data.  Therefore, code can also save the record, which will make the form AfterUpdate event happen. Save record:
me.dirty = false
don't need to test for Dirty (if me.dirty then)  first if you have just changed the record.

If, for some reason, this does NOT trigger the form after update event, you can call it.

sorry I don't have more time now to see what else you wrote ...

have an awesome day,
crystal
0
 
PatHartmanCommented:
I just tried the afterupdate event in the subform but this fires every time I click the btnadd or btnsubtract button.
Stop forcing the value to save after each update by removing the Me.Dirty = False code.  Then the control's AfterUpdate event will run only once when you leave the control.  OR, you can use the subform's BeforeUpdate event.
0
 
crystal (strive4peace) - Microsoft MVP, AccessRemote Training and ProgrammingCommented:
hi Dustin,

Since a user may click a button several times. Pat may be right to not save record everytime ... depends on if the user needs to see the totals update each time. Of course, the buttons will still increment or decriment whatever nz(value,0) is there

 Even though the control doesn't 'lose focus' (in the subform) to trigger an event, the subform should save when you leave it to click on something in the main form. Therefore, you can use the subform's form AfterUpdate event to do stuff.  Another event that should trigger is  On Exit for the subform control. As I said before, this will actually be in code behind the mainform.

have an awesome day,
crystal
0
 
Gustav BrockConnect With a Mentor CIOCommented:
I think you are making this far more complicated than needed.

Replace the hidden textbox with a variable, and consider updating not the form but the recordsetclone, and you can remove saving and requerying of the form.

See the attached demo, please.
TimeEntry.accdb
1
 
PatHartmanCommented:
If you only want to save the record if a value changed, use either the control's AfterUpdate event or the form's AfterUpdate event.  By the time the exit event for the subform control runs (if it even does), you will have no way of knowing if an update happened.  Events are not randomly interchangeable and studying them and what triggers them will help you to determine which events should be used for which purposes.
0
 
Dustin StanleyEntrepreneurAuthor Commented:
Thank you all for the help. I will try this Monday when I get back in. Thanks!
0
 
PatHartmanCommented:
I looked at your code again.

Me!QtyLoc.value = QtyLoc + 1 'Add one to the total

If you renamed the control as most of us recommend so that the control name is different from the bound field, the above statement will update the value but you will not see it on the form because you are updating the record buffer rather than the screen control.  If that is the case, then do this:

Me.txtQtyLoc = Me.txtQtyLoc + 1 'Add one to the total

That will eliminate your need to save after each increment.

I removed the ".value" because the value property is the default so you don't need to use it.  I changed the ! to . so that you will find reference errors at compile time rather than at run time.

ALWAYS use "Me." (or "Me!" if you insist) to qualify form/report references.  It saves Access from having to search all loaded modules to find where the variable is defined.  The "Me" tells Access that the variable is defined in the form's class module.
0
 
crystal (strive4peace) - Microsoft MVP, AccessConnect With a Mentor Remote Training and ProgrammingCommented:
hi Dustin,

responding to some of Pat's comments

>"That will eliminate your need to save after each increment."

 .. but if there is a total, such as =Sum([fieldname]) at the bottom, that may or may not also be used in other places, the record does need to be saved after each change if the totals are to be updated after each change -- but if not, then of course what I say in this message about saving won't apply.

> "renamed the control as most of us recommend so that the control name is different from the bound field"

Sorry to disagree, and I wouldn't say "most" either -- it seems to be about half and half.  For bound controls, I keep the names the same, and have for as long as I've been using Access (~ 25 years) -- it makes things a lot easier too!  Although I have heard this can cause issues, I have never had one.  The only time I found that a bound control name did have to be different than the field name was with web databases, not desktop databases such as what is being used here. I think it comes down to personal preference -- both ways of naming seem to work fine.

Agree with Pat that using dot (.) is better than bang (!), unless there is a specific reason not to (such as binding is not there until runtime) so that the compiler can catch more ... and Option Explicit should be at the top.  Also agree that Me. should always be used to preface control names and other properties when it applies. In addition to better performance, this also makes it easier to see what the code is doing.

Dustin, regarding running "ChangedValuesfrmSkusEntry" -- that should be done on the form AfterUpdate event, not when the control loses focus -- although you may want to put if me.dirty then me.dirty=false on that event to save if there are changes.  The subform record will be saved automatically when focus goes back to the mainform -- so the form AfterUpdate event will run.

or perhaps you want to use the subform's exit event to run an append query that gets everything that has been changed (you are tracking dtmEdit, right?) instead of doing them one at a time. If you want to do this, then use the subform Enter event to record Now() so you know what has been changed. If, however, your log writes the Old value, then this needs to be done one at a time since it won't be available for a batch of records unless you save it in a scratch field.

~~
Gustav, nice example! I believe in this case, however, if memory serves me correctly, that Dustin has totals at the bottom of the form that might need to reflect the changes (although maybe not each one).  I would like to know, however, how using the RecordsetClone is better, if you wouldn't mind explaining, thanks.

have an awesome day,
crystal
0
 
Dustin StanleyEntrepreneurAuthor Commented:
I thank you all for the help. I have been extremely swamped preparing for a building inspection so I have not had time to work with this code but definitely has to happen.  Hopefully over the next few days I can try these suggestions and see where I end up. All the comments are definitely educational and informative. Makes me think more about different situations.
0
 
Dustin StanleyEntrepreneurAuthor Commented:
I had some time this morning to work on it. There is so many different ways and all the answers above are great but I think I need to redevelop this form better. Such as with a pop up Quantity form that the user can add or subtract for each individual record.  This way i can control the code better. Also thank you so much for the input as thee was a ton more info I learned than just what I asked.
0
 
Dustin StanleyEntrepreneurAuthor Commented:
Thanks for the input. I am going to reform the form.
0
 
crystal (strive4peace) - Microsoft MVP, AccessRemote Training and ProgrammingCommented:
you're welcome, Dustin ~ happy to help

> " pop up Quantity form"

good idea -- that way, others won't expect totals to be updated till popup is closed ... and you can, of course, still have Up/down buttons there too -- maybe even echo what was there to begin with, if it wasn't 0 or null

~crystal
1
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.