How to run a VBA Code on MS Access open event

[SachAccess]

Hi,
I need a help to run a VBA code in my MS Access Database on open event.
Every time my MS Access Database opened, I need to run this code.

I have build the code but do know how to trigger it.
It is a simple VBA Sub Routine, not linked to any form.
Can anyone please help me in this.

 

[Pat Hartman]

I think the code needs to be a function to be run from a macro so you might have to change it from a sub to a function. Then, you can create an AutoExec macro to run your code and then open the main form.

"AutoExec" is a specific name. Access will run any macro with that name when the db opens.

 

[SachAccess]

I think the code needs to be a function to be run from a macro so you might have to change it from a sub to a function. Then, you can create an AutoExec macro to run your code and then open the main form.

"AutoExec" is a specific name. Access will run any macro with that name when the db opens.

Thanks a lot for the help. Have a nice day ahead.

 

[Pat Hartman]

You're welcome

 

[SachAccess]

You're welcome

It is working great! Thanks!

 

[SachAccess]

Hi,
Am facing an issue while trying run the below code on open event of MS Access database.
As soon as I open the MS Access Database file, I get a pop-up saying 'Stop All Macros'.
If I click on 'Stop All Macros' and then click to Enable Content then the macro still works.
I am not able to understand my error. Can anyone please help me in this.

Stop All Macros
Arguments: WelcomeCode()
Error Number: 2001

Option Compare Database
Option Explicit
Public NTLoginID As String
Function WelcomeCode()
    NTLoginID = Trim(UCase(Environ("UserName")))
    Call MakeEntryInLogTable
End Function

Sub MakeEntryInLogTable()
    Dim TblLog As DAO.Recordset
    Dim MyMax As Long
    
    '    DoCmd.OpenQuery "Q_MaxNmbrFrmLgTbl"
    Set TblLog = CurrentDb.OpenRecordset("SELECT * FROM [Tbl_Tool_Open_Log]")
    
    TblLog.AddNew
    On Error Resume Next
        MyMax = DMax("Serial_Number", "Tbl_Tool_Open_Log") + 1
    On Error GoTo 0
    
    If MyMax = 0 Then MyMax = 1
    TblLog![Serial_Number] = MyMax
    TblLog![User Name] = Trim(UCase(Environ("UserName")))
    TblLog![Opened Date] = Date & " - " & Time
    TblLog.Update
    TblLog.Close
    Set TblLog = Nothing
    DoCmd.Close
End Sub

 

[JMongi]

Others who know the inner workings of Access better than I can chime in....but....

I believe a macro is just an added layer on top of Visual Basic for Applications (VBA) code that makes automation easier for non-programmers.

So, you first "stop all macros" (stop code from running) and then "enable content" (allow code to run). I don't believe there is a distinction despite the differing names. If you allow code to run, I believe macros will be allowed to run as well.

 

[SachAccess]

Thanks for the help.

 

[MarkK]

You might need to tweak your Trust Center settings. Open Access Options->Trust Center->Trust Center Settings->Macro Settings->Enable all macros.

 

[JMongi]

I wanted to clarify I wasn't giving directions. I realized my choice of words could be interpreted as such.
What I was saying is that Access is doing exactly what you are telling it to do.
First, you tell it to "Don't Run Any Macros". It agrees.
Then, you tell it to "Enable Content". It agrees again.
Because you clicked on "Enable Content" last, your macros will run.

It's unclear from your original post what your intended goal is (running or not running). I am simply commenting on what is happening.

 

[SachAccess]

Thanks for the help @MarkK and @JMongi . Have a nice day ahead.