Access Macro To Run Only Once

[elak6]

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

[TheSmileyCoder]

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.

[NeoPa]

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.

[elak6]

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

[TheSmileyCoder]

Again, in VBA probably the easiest is to disable the button after running the code

Private sub btn_Example_Click()
  'Your code goes here
  Me.btn_Example.Enabled=False
  'Optional 
  Me.btn_Example.Visible=False
End Sub

[elak6]

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

[NeoPa]

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 :

Private sub cmdExample_Click()
    With Me
        'Your code goes here
        Call .cmdNext.SetFocus
        .cmdExample.Enabled=False
    End With
End Sub

Very similar to Smiley's suggestion of course.

[elak6]

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

Command3Next.SetFocus

[NeoPa]

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 :

Call .Command3.SetFocus

[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

Option Compare Database

Dim ranOnce As Boolean
Private Sub Button_Click()
  If ranOnce = False Then
    'run the macro
    call theMacro
  End If
  'set the flag to true
  ranOnce = True
End Sub

Private Sub Form_Load()
  'set the flag to False
  ranOnce = False
End Sub

[NeoPa]

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

[elak6]

Hey Neopa, This is an the code that i ran and it still giving me teh same error

Private Sub Command3_Click()
 With Me
'my code
Call ImportFromExcel
Call .Command3.SetFocus
    .Command3.Enabled = False
    End With
End Sub

The import from excel calls out my file dialog box and DoCmd.TransferS preadsheet codes.

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

Private sub cmdExample_Click()
    With Me
        'Your code goes here
        Call .cmdNext.SetFocus
        .cmdExample.Enabled=False
    End With
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).

[TheSmileyCoder]

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

[elak6]

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