By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
455,663 Members | 1,792 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 455,663 IT Pros & Developers. It's quick & easy.

Access Macro To Run Only Once

elak6
P: 21
I have a form which acts as a dasboard for a variety of functions
I have a created a buttion which is linked to an access macr on the form
I wanted the macro to run only once or the option of having the button to be clicked ony once

Thanks
Nov 28 '11 #1

✓ answered by NeoPa

NeoPa Post #7:
In that case you simply need to ensure that the focus is moved to the next control (We'll just call it cmdNext in our example) before you disable the current one :

Expand|Select|Wrap|Line Numbers
  1. Private sub cmdExample_Click()
  2.     With Me
  3.         'Your code goes here
  4.         Call .cmdNext.SetFocus
  5.         .cmdExample.Enabled=False
  6.     End With
  7. End Sub
Very similar to Smiley's suggestion of course.
That's because you're explicitly setting the focus to the same control instead of a different one as I illustrated in my code.

Why don't you read through that bit again and do it as I have done in mine (The post also explains the different controls).

Share this Question
Share on Google+
15 Replies


TheSmileyCoder
Expert Mod 100+
P: 2,321
Im not too experienced in using macros, as I mostly use VBA.

The simplest way I believe would be to simply disable the button as the last thing your macro does.
Nov 28 '11 #2

NeoPa
Expert Mod 15k+
P: 31,712
Once ever? Once per session? Once per various other possibilities?

You need to make your question clearer.

PS. Very few advanced Access experts use macros. That should tell you something.
Nov 28 '11 #3

elak6
P: 21
Hi giuys,
Thanks for the speedy reply
I have tried again using the macros wahy and it is still proving difficult.
I know i can easily convert all my macros to vba using the database toolbar in ms access
But i still dont know how to run the macro once per session using vba either
Any help you can offer would be highly appreciated
Thanks
Nov 28 '11 #4

TheSmileyCoder
Expert Mod 100+
P: 2,321
Again, in VBA probably the easiest is to disable the button after running the code
Expand|Select|Wrap|Line Numbers
  1. Private sub btn_Example_Click()
  2.   'Your code goes here
  3.   Me.btn_Example.Enabled=False
  4.   'Optional 
  5.   Me.btn_Example.Visible=False
  6. End Sub
Nov 28 '11 #5

elak6
P: 21
Hi Smiley Coder
Thanks for the speedy reply
I just tried running the code and it gives me the error;
Run-time erroe '2164'
You can't disable a control while it has the focus


Thanks
Nov 28 '11 #6

NeoPa
Expert Mod 15k+
P: 31,712
In that case you simply need to ensure that the focus is moved to the next control (We'll just call it cmdNext in our example) before you disable the current one :

Expand|Select|Wrap|Line Numbers
  1. Private sub cmdExample_Click()
  2.     With Me
  3.         'Your code goes here
  4.         Call .cmdNext.SetFocus
  5.         .cmdExample.Enabled=False
  6.     End With
  7. End Sub
Very similar to Smiley's suggestion of course.
Nov 28 '11 #7

elak6
P: 21
Hi Neopa,
Thanks for the speedy reply

Using your method does not comply for some reason with my command button in vba

I tried running the code above and it gave me an error:
Compile error: Method or data member not found
And it highlist this section of code
Expand|Select|Wrap|Line Numbers
  1. Command3Next.SetFocus
Nov 28 '11 #8

NeoPa
Expert Mod 15k+
P: 31,712
That's probably because you haven't used the name properly. What's the actual name of the control?

PS. My guess would be simply [Command3], in which case the code for that line should read :
Expand|Select|Wrap|Line Numbers
  1. Call .Command3.SetFocus
Nov 28 '11 #9

pod
100+
P: 298
pod
have you tried ...:
  1. add a variable to your form and set to false on Load
  2. add code to check if this variable is false before running the macro, and setting the variables to true

Expand|Select|Wrap|Line Numbers
  1. Option Compare Database
  2.  
  3. Dim ranOnce As Boolean
  4. Private Sub Button_Click()
  5.   If ranOnce = False Then
  6.     'run the macro
  7.     call theMacro
  8.   End If
  9.   'set the flag to true
  10.   ranOnce = True
  11. End Sub
  12.  
  13. Private Sub Form_Load()
  14.   'set the flag to False
  15.   ranOnce = False
  16. End Sub
  17.  
  18.  
Nov 28 '11 #10

NeoPa
Expert Mod 15k+
P: 31,712
That's fundamentally the same logic as the other code Pod, except the other code is somewhat shorter and uses the control's Enabled property itself as the variable (which is more direct and doesn't require an extra variable to be set up).
Nov 28 '11 #11

elak6
P: 21
Hey Neopa, This is an the code that i ran and it still giving me teh same error
Expand|Select|Wrap|Line Numbers
  1. Private Sub Command3_Click()
  2.  With Me
  3. 'my code
  4. Call ImportFromExcel
  5. Call .Command3.SetFocus
  6.     .Command3.Enabled = False
  7.     End With
  8. End Sub
The import from excel calls out my file dialog box and DoCmd.TransferSpreadsheet codes.
Nov 28 '11 #12

NeoPa
Expert Mod 15k+
P: 31,712
NeoPa Post #7:
In that case you simply need to ensure that the focus is moved to the next control (We'll just call it cmdNext in our example) before you disable the current one :

Expand|Select|Wrap|Line Numbers
  1. Private sub cmdExample_Click()
  2.     With Me
  3.         'Your code goes here
  4.         Call .cmdNext.SetFocus
  5.         .cmdExample.Enabled=False
  6.     End With
  7. End Sub
Very similar to Smiley's suggestion of course.
That's because you're explicitly setting the focus to the same control instead of a different one as I illustrated in my code.

Why don't you read through that bit again and do it as I have done in mine (The post also explains the different controls).
Nov 28 '11 #13

TheSmileyCoder
Expert Mod 100+
P: 2,321
@POD
While your logic is fundamentally the same (and sound/good enough), the reason why I choose not to suggest such an approach is that if at any point an unhandled error occurs (regardless of which form/code it happens in), such a variable is wiped from memory, whereas a control will remember whether it was enabled or disabled.

It will work the same, provided you have error handling code everywhere!
Nov 28 '11 #14

elak6
P: 21
NeoPa,
Thanks you so much
So sorry about my foolishness
After going back and reviewing your code, did i just realised the major error on my part
Thank you everybody for all your help
I really appreciate it and all the good that the forum is doing
Nov 28 '11 #15

NeoPa
Expert Mod 15k+
P: 31,712
That's no worry Elak. We all make mistakes.

PS. I'm going to update the post you have selected to include a quote of the one with the answer in it. You may decide to reset the current one in order to set the original instead (which would make sense), but this way at least it is visible for anyone who comes looking :-)
Nov 28 '11 #16

Post your reply

Sign in to post your reply or Sign up for a free account.