Creating a Global code

[chippyles]

I have an On Open event procedure in VBA that I would like to make it global to every form or report open. How can I go about doing this?

 

[BobStubbs]

-- Place the VBA code in a Sub procedure in a module
-- Name this (for example) MyOpenProc
-- Call this from the On Open event of each form etc, e.g.

Private Sub Form_Open(Cancel as integer)
    MyOpenProc
End Sub

Bob Stubbs

 

[chippyles]

I did what you asked and I am now getting an error.

Compile Error:
Expected variable or procedure, not module

 

[TCARPENTER]

Hi chippyles,

I have found doing it this way works for me:

In any module, create a public function containing your precedures...

[blue]Public Function[/blue] FormProcess()
  [i]...your procedures here...[/i]
[blue]End Function[/blue]

Then, in the On Open event in the forms properties dialog box type: [purple]=FormProcess()[/purple]

HTH
Todd

 

[chippyles]

This is the code I used as a function

I got an error stating.

Compile Error:
Invalid use of Me keyword

What now??


Public Function FormProcess()
DoCmd.SetWarnings False
DoCmd.RunSQL " INSERT INTO LOG_REPORT ( [DATE], [USER], HOST ,TASK, DB )" & _
"SELECT now() AS [DATE], environ('username') AS [USER], environ('computername') AS HOST ,'" & Me.Name & "','" & CurrentDb.Name & "'"
DoCmd.SetWarnings True
'DoCmd.RunMacro "mLOCAL_USER"
End Function

 

[PHV]

Replace this:
Me.Name
By this:
Screen.ActiveForm.Name

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[TCARPENTER]

chippyles,

Try this:

[blue]Public Function[/blue] FormProcess()
  [blue]Dim[/blue] strSQL [blue]As String[/blue]
  
  DoCmd.SetWarnings [blue]False[/blue]
  
  strSQL = " INSERT INTO LOG_REPORT ( [DATE], [USER], HOST ,TASK, DB )" & _
           "SELECT now() AS [DATE], environ('username') AS [USER], environ('computername') AS HOST ,'" & _
           Me.Name & "','" & CurrentDb.Name & "'"
  
  DoCmd.RunSQL strSQL
  DoCmd.SetWarnings [blue]True[/blue]
  
[blue]End Function[/blue]

By placing the SQL string into a variable, all the other variables are resolved into their equivalencies before being passed to the SQL interpreter so for example; your variables [purple]Me.Name[/purple] and [purple]CurrentDb.Name[/purple] would now read [purple]Form1[/purple] and [purple]TestDatabase[/purple] etc etc.

HTH
Todd

 

[chippyles]

The only problem is the Screen has to be active. The screen is 99% of the time closed. When I open it I get a debug error saying the screen needs to be active.