Access form command button only works the first time 1

[colinnwn]

Hi,

I have spent 30 minutes searching google and come up with nothing. I have a command button on an Access form with the following event procedure. It will work the first time I click it, but it won't work again until I click another command, and then go back to this button.

I have tried clearing the variables a bunch of different ways, but perhaps not the right way? I have tried err.clear at the end, to see if it thought the command already finished successfully. I have looked around for some kind of VBA statement that resets a routine. And I have even tried calling another bogus subroutine right before this one ends. No joy... Any help would be appreciated.

Thanks, Colin.

Private Sub Command84_Enter()
Dim stappname As String
Dim fyear As String
Dim fname As String

fyear = InputBox("Enter the desired 4 digit year", "Which year?")
fname = "msaccess.exe M:\Materials\Purchasing\Inventory\CYCLECOUNTS\ABC\" & fyear & "ABC\" & fyear & "ABC.MDB"
stappname = fname
Call Shell(stappname, 3)

End Sub

 

[TonyJollans]

Hi colinnwn,

Might this have something to do with it?...

Private Sub Command84_[red]Enter[/red]()

Enjoy,
Tony

--------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.
Excel VBA Training and more Help at VBAExpress[

 

[colinnwn]

Hi Tony,

Apparently it did, but I don't understand the nuances. I tried changing it manually to _Click() and the button entirely quit working. Then I remade the button from the properties box and cut and pasted the code into the new Subroutine header and it worked perfect.

You got it working, much thanks,
Colin.

 

[TonyJollans]

Hi Colin,

The Enter event fires only when you "enter" the button (when you first click or tab to it). It does not happen again until you exit and enter it again.

The Click event should fire whenever you click on the button, no matter where you were before, so I'm not quite sure what you may have got wrong there but the idea was right.

Anyway, glad you're working now - and thanks for the star.

Enjoy,
Tony

--------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.
Excel VBA Training and more Help at VBAExpress[