Solved Edit: How to call a function from multiple controls on the same form and avoid redundant code. (1 Viewer)

[autumnnew]

Edit: this thread was previously named 'Error with saving record on Unload event'. Renamed after original issue was resolved and new related question came up. See post #17.

I get the The command or action 'SaveRecord' isn't available now. error from the Unload event of a subform in my navigation subform when I close the Main form, but not when I switch from the tab to another tab. I have a function that runs perfectly when switching from that tab to another, and the first step in the function is DoCmd.RunCommand acCmdSaveRecord, but that step can't execute when closing the Main form while that tab is activated (due to the Unloading process). The rest of the function still performs in either case (it's just some update queries), just not that first saverecord step. There are buttons on this same tab to open reports but need to run the same function before opening the reports, which is why I put the saverecord step in the function.

 

[theDBguy]

If you're trying to save a Dirty record, try removing the SaveRecord command, because it should save anyway. Otherwise, maybe using the UnLoad event to save a record might be too late, that's why the error says that command is no longer available.

 

[autumnnew]

Welp. I justed tested it without the command and it works fine. Ugh! Is there a way I can delete posts like this?

I knew that about records being saved automatically, but I guess at some point in development I wasn't getting the right result, so I thought the save should be forced. I could've sworn I tested the function without the saverecord command.

Thanks for your help and quick response as always!

 

[theDBguy]

Welp. I justed tested it without the command and it works fine. Ugh! Is there a way I can delete posts like this?

I knew that about records being saved automatically, but I guess at some point in development I wasn't getting the right result, so I thought the save should be forced. I could've sworn I tested the function without the saverecord command.

Thanks for your help and quick response as always!

Hi. Glad to hear you got it sorted out. Good luck with your project.

 

[autumnnew]

Okay, I'm back with an issue related to this. Same form, too. The form is a continuous form, and each record has Yes/No toggle buttons. In the header, I have a subform to display the Yes counts for each button's field. My issue is when I click a toggle button, the respective total is not updated until the focus moves to the next record, and that's not what I want. I want it to be an instant update Onclick. So for each button, I have 2 steps coded: SaveRecord and subTotalsForm.Requery. This solution works just fine, but it's quite redundant and makes the code look cluttered. Is there a more efficient way to code this? Because one other thing, there are 28 buttons that I added this code to. [The buttons are necessary, and the form loads quickly.] I'm hoping someone will know of a way I can code this in just a couple of the right places rather than in 28 places.

The reason I have the totals displayed in a subform is because it's easier to have the counts done in a separate query, rather than unbound boxes, and the subform refreshes quicker and doesn't flicker.

I've tried all sorts of orders of events in the detail, form and on the buttons, such as beforeupdate, afterupdate, onclick, mouseup etc.

Thanks in advance!

 

[MajP]

Without seeing your code you will likely need a recalc, or refresh or a requery as you already have. However, you may be able to make a single event handler instead of 28. That will definitely unclutter your code.
See discussion here.

What You Need to Know About Access Events

There is often a lot of confusion about Access Events and often answering questions posed here is difficult because of inaccurate terminology. People often say "Event" when it is unclear if they mean the actual event that takes place, the event procedure, or the event property. This thread...

www.access-programmers.co.uk

 

[autumnnew]

Without seeing your code you will likely need a recalc, or refresh or a requery as you already have. However, you may be able to make a single event handler instead of 28. That will definitely unclutter your code.
See discussion here.

What You Need to Know About Access Events

There is often a lot of confusion about Access Events and often answering questions posed here is difficult because of inaccurate terminology. People often say "Event" when it is unclear if they mean the actual event that takes place, the event procedure, or the event property. This thread...

www.access-programmers.co.uk

Ooh, this looks good. I'll read through it. Hopefully it'll be more informative than the Microsoft page about order of events. Thanks for sharing, and for writing it!

Here is an example of the code of one of the buttons:

Private Sub btnAssignTest1_AfterUpdate()
    DoCmd.RunCommand acCmdSaveRecord
    Me!subAssignmentsTotals.Requery
End Sub

 

[The_Doc_Man]

Is there a way I can delete posts like this?

Actually, there is. I believe you can delete your own posts. However, we typically leave the posts intact, particularly if there is a workable solution inside, since others can search/find that solution.

 

[MajP]

Here is an example of the code of one of the buttons

If they all do that, then it can be done with a single event handler.

 

[gemma-the-husky]

You could try adding DoEvents in the afterupdate event, or the click code for your routine. That might allow a pending process such as a recalc to catch up..

 

[autumnnew]

If they all do that, then it can be done with a single event handler.

Okay, I can write that, and I suppose it would be placed in the after update event? of the form?

Actually, there is. I believe you can delete your own posts. However, we typically leave the posts intact, particularly if there is a workable solution inside, since others can search/find that solution.

Okay, I could never find where you can delete posts, but I'll leave it alone. I agree about leaving posts to help others. I searched a lot before posting here. Nothing I found was related to an Unload event, so maybe my post will help someone else one day.

 

[autumnnew]

Alright, I figured out the function using Me (all I had to do was enclose the Me reference in a With statement). However, the total still doesn't update because the focus is still on the button. I put the exact same code from the buttons into the function. Once I move the focus off the button or to another record, the total updates. I appreciate any help.

The function is:

Function fnAssignTest(frm As Form)
    With frm
        DoCmd.RunCommand acCmdSaveRecord
        .subAssignmentsTotals.Requery
    End With
End Function

The call is:

Call fnAssignTest(Me)

 

[MajP]

Not what I meant. In the buttons' onclick set the property to
= fnAssignTest()
Put that in form's module
Replace frm with me.
Now get rid of all old event procedures. The module handles all click events

 

[autumnnew]

Okay, that works. I was just hoping there was a way to do this without having to code 28 buttons, but putting a function in them is still better than repeating code in them. Thanks

 

[MajP]

Okay, that works. I was just hoping there was a way to do this without having to code 28 buttons, but putting a function in them is still better than repeating code in them.

I am assuming you did not read my referenced thread. See Paragraph D. No, you DO NOT code 28 buttons. You write one and only one function and it handles all 28 buttons.

 

[autumnnew]

Your paragraph says: "Then in each controls event properties, you do not put in "[Event Procedure]" you put in the name of the function"

This means write a function and add it to one of the events for each button, right? One function to handle the code, and all the buttons should call the function? That's how I have it now. Did I misunderstand your article?

If that's what you meant, maybe I used the word "code" incorrectly in my previous post. What I meant was, I was originally asking if there's a way the code can be consolidated without having to add to an event for all 28 buttons, because I originally had code (2 steps) in all 28 buttons and I knew that was redundant.

After reading your #6 post, I wrote the function and changed all the buttons to call the function (as your referenced link advises). In my previous post, I was reiterating that I was looking for a more consolidated solution, and even though I still have to see the "code" for all 28 buttons in the editor, I realized that having the function in all the buttons was the only way.. still better than having the 2 steps listed in each button.

Sorry for the confusion. I'm still a novice at this!

 

[MajP]

See Demo.

 

[autumnnew]

Ohhhhhhh, I see. You meant I should put the function in the property sheet. I didn't realize that. I wondered why you kept typing the = sign instead of Call, because obvs you can't start a function call with = in editor.

This looks MUCH cleaner!! It's just what I was looking for. Many many thanks! Now I understand your article much better.

I'll update the title of this thread to reflect a more accurate description of the outcome.
Sometimes it can be hard to know how to describe the solution you're looking for.

 

[Pat Hartman]

Function fnAssignTest(frm As Form)
    With frm
        DoCmd.RunCommand acCmdSaveRecord
        .subAssignmentsTotals.Requery
    End With
End Function

If you think that the DoCmd is saving the record from the Form referenced by frm, you are wrong. It is saving the record of the form which has the focus which may or may not be what you intend.

If you are going to do something like this and you want it to actually work regardless of what form has the focus, you might try the "trick" using the dirty property.

frm.dirty = False

I don't know that this works because I would be unlikely to ever write this kind of code but if it works, it is better than using DoCmd.