UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
2 Pages V  1 2 >  (Go to first unread post)
   Reply to this topicStart new topic
> Executing A Module On Form Load?    
 
   
JV63
post Jan 13 2017, 09:32 AM
Post#1



Posts: 183
Joined: 16-August 12



Hi. I have a module I want to have run when my form loads. I thought just doing a call would work but I get a popup saying "reserved error" which I don't know what it means.

This is my form load code and I'm trying to have module called BackupOnOpen() execute when the user opens the form. Interestingly if I do something like Call BackupOnOpen() in the form_load section my () after BackupOnOpen disappear when I go to the next line.

CODE
Private Sub Form_Load()

    LblMenuVer.Caption = "Version " & App.Major & "." & App.Minor & "." & App.Revision
    
'   Open Database Connection
    Set cnims13 = New ADODB.Connection
    cnims13.Open MSJetVersion & _
        "Data Source=" & strPathDatabase & strDatabaseName & ";"

End Sub


Thanks
Go to the top of the page
 
theDBguy
post Jan 13 2017, 10:49 AM
Post#2


Access Wiki and Forums Moderator
Posts: 71,225
Joined: 19-June 07
From: SunnySandyEggo


Hi,

Can you post the code for your module? Thanks.

--------------------
Just my 2 cents... "And if I claim to be a wise man, it surely means that I don't know" - Kansas
Microsoft Access MVP | Access Website | Access Blog | Email
Go to the top of the page
 
JV63
post Jan 13 2017, 10:58 AM
Post#3



Posts: 183
Joined: 16-August 12



Sure thing. Thanks

This works well with Access as module when I place it in AutoExec but this time trying to do it through strictly a form created in VB6.

CODE
Option Explicit
' This  function copies the Access database to a backup
' location when the database is opened.
' It does this only once in any given day.

Public Function BackupOnOpen()

' ***  CHANGE THE FOLLOWING LINE TO MATCH YOUR BACKUP DESTINATION
' Ensure you have a \ on the end of the pathname.
Const BACKUP_PATH = "\\cdtvnas13\pricingtool\"

On Error GoTo BackupOnOpen_Err

If DCount("BackupDate", "tblBackupDetails", "BackupDate = date()") <> 0 Then
Exit Function
End If

Dim strSourcePath As String
Dim strSourceFile As String
Dim strBackupFile As String
'strSourcePath = GetFileName(CurrentDb.NAME, False)   '  false means we want pathname
'strSourceFile = GetFileName(CurrentDb.NAME, True)    '  true means we want filename
strSourcePath = GetFileName("\\plyvnas02\ops\priceconversion\pricingtool\", False)   '  false means we want pathname
strSourceFile = GetFileName("\\plyvnas02\ops\priceconversion\pricingtool\pricetest.accdb", True)    '  true means we want filename
strBackupFile = "BackupDB-" & Format(Date, "yyyy-mm-dd") _
& "_" & Format(Time, "hhmmss") & "-" & strSourceFile

Dim fso
Set fso = CreateObject("Scripting.FileSystemObject")
fso.CopyFile strSourcePath & strSourceFile, BACKUP_PATH & strBackupFile, True
Set fso = Nothing
DoCmd.SetWarnings False
Dim SQL As String
SQL = "INSERT INTO tblBackupDetails " _
  & "(BackupDate, ComputerName, BackupFolder, Filename) " _
  & "VALUES ('" & Date & "', '" & Environ("COMPUTERNAME") _
  & "', '" & BACKUP_PATH & "', '" & strBackupFile & "');"
DoCmd.RunSQL SQL
SQL = "DELETE * FROM tblBackupDetails WHERE BackupDate < date() - 10;"
DoCmd.RunSQL SQL
DoCmd.SetWarnings True
BackupOnOpen_Exit:
Exit Function
BackupOnOpen_Err:
MsgBox Err.Description, , "BackupOnOpen()"
Resume BackupOnOpen_Exit
End Function
                    
' This  function is given a filename complete with path, and
' a boolean value.
' If the  boolean is true, this function returns only the
' filename (without the path).
' If the  boolean is false, this function returns only the
' path (without the filename).
'  Author:   Mike Perris - mikeperris.com
'  Date:     13-Mar-2011
'  Version:  1.1
' Revision  History:
' Rev       Date(yyyy/mm/dd)        Description
' 1.1       13/3/2011               Added the boolean bit and  extended code to
'                                   extract path  as well as filename.
Function GetFileName(FullPath As String, IsFile As Boolean)
Dim icount  As Integer
icount = Len(FullPath)
Do Until Mid(FullPath, icount, 1) = "\"
icount = icount - 1
Loop

If IsFile Then
GetFileName = Right(FullPath, Len(FullPath) - icount)
Else
GetFileName = Left(FullPath, icount)
End If
End Function
Go to the top of the page
 
theDBguy
post Jan 14 2017, 11:22 AM
Post#4


Access Wiki and Forums Moderator
Posts: 71,225
Joined: 19-June 07
From: SunnySandyEggo


Thanks. Which line gets highlighted when you get the error?

--------------------
Just my 2 cents... "And if I claim to be a wise man, it surely means that I don't know" - Kansas
Microsoft Access MVP | Access Website | Access Blog | Email
Go to the top of the page
 
JV63
post Jan 16 2017, 07:53 AM
Post#5



Posts: 183
Joined: 16-August 12



Trying it as a basic call, when I get the "reserved error" the call is highlighted.

CODE
Private Sub Form_Load()

    LblMenuVer.Caption = "Version " & App.Major & "." & App.Minor & "." & App.Revision
    
    Call BackupOnOpen
    
'   Open Database Connection
    Set cnims13 = New ADODB.Connection
    cnims13.Open MSJetVersion & _
        "Data Source=" & strPathDatabase & strDatabaseName & ";"

End Sub
Go to the top of the page
 
theDBguy
post Jan 16 2017, 10:25 AM
Post#6


Access Wiki and Forums Moderator
Posts: 71,225
Joined: 19-June 07
From: SunnySandyEggo


Do you still get an error if you don't use "Call?"

--------------------
Just my 2 cents... "And if I claim to be a wise man, it surely means that I don't know" - Kansas
Microsoft Access MVP | Access Website | Access Blog | Email
Go to the top of the page
 
JV63
post Jan 16 2017, 10:28 AM
Post#7



Posts: 183
Joined: 16-August 12



No, but then that new module doesn't execute if I don't call it in somehow. Unless there's a different way to have it execute when form opens.
Go to the top of the page
 
theDBguy
post Jan 16 2017, 10:31 AM
Post#8


Access Wiki and Forums Moderator
Posts: 71,225
Joined: 19-June 07
From: SunnySandyEggo


Hi,

Sorry for the confusion... What I meant was to change the following from this:

Call BackupOnOpen

to be simply like this:

BackupOnOpen

In other words don't use "Call." Hope it makes sense now...

--------------------
Just my 2 cents... "And if I claim to be a wise man, it surely means that I don't know" - Kansas
Microsoft Access MVP | Access Website | Access Blog | Email
Go to the top of the page
 
JV63
post Jan 16 2017, 10:44 AM
Post#9



Posts: 183
Joined: 16-August 12



If I run it like this, I get reserved error still.

CODE
Private Sub Form_Load()

    LblMenuVer.Caption = "Version " & App.Major & "." & App.Minor & "." & App.Revision
    
    BackupOnOpen
    
'   Open Database Connection
    Set cnims13 = New ADODB.Connection
    cnims13.Open MSJetVersion & _
        "Data Source=" & strPathDatabase & strDatabaseName & ";"

End Sub


If I run it like this with the (), I get "compile error, expected:="

CODE
Private Sub Form_Load()

    LblMenuVer.Caption = "Version " & App.Major & "." & App.Minor & "." & App.Revision
    
    BackupOnOpen()
    
'   Open Database Connection
    Set cnims13 = New ADODB.Connection
    cnims13.Open MSJetVersion & _
        "Data Source=" & strPathDatabase & strDatabaseName & ";"

End Sub
Go to the top of the page
 
theDBguy
post Jan 16 2017, 11:35 AM
Post#10


Access Wiki and Forums Moderator
Posts: 71,225
Joined: 19-June 07
From: SunnySandyEggo


If you hit Ctrl+G and then enter

BackupOnOpen

Do you still get the same error?

--------------------
Just my 2 cents... "And if I claim to be a wise man, it surely means that I don't know" - Kansas
Microsoft Access MVP | Access Website | Access Blog | Email
Go to the top of the page
 
JV63
post Jan 16 2017, 11:46 AM
Post#11



Posts: 183
Joined: 16-August 12



Yes, reserved error.
Go to the top of the page
 
theDBguy
post Jan 16 2017, 11:51 AM
Post#12


Access Wiki and Forums Moderator
Posts: 71,225
Joined: 19-June 07
From: SunnySandyEggo


Okay, this eliminates your first procedure, which means you'll need to direct your focus towards the BackupOnOpen function. Have you tried compiling your code?

--------------------
Just my 2 cents... "And if I claim to be a wise man, it surely means that I don't know" - Kansas
Microsoft Access MVP | Access Website | Access Blog | Email
Go to the top of the page
 
JV63
post Jan 16 2017, 11:55 AM
Post#13



Posts: 183
Joined: 16-August 12



Yes. If I compile it without doing anything with the module in terms of trying to call it in the form_load, then everything works fine. The module just doesn't do anything so I'm not getting a backup created.
Go to the top of the page
 
theDBguy
post Jan 16 2017, 12:01 PM
Post#14


Access Wiki and Forums Moderator
Posts: 71,225
Joined: 19-June 07
From: SunnySandyEggo


Okay, one last thing... Have you tried stepping through the function code?

--------------------
Just my 2 cents... "And if I claim to be a wise man, it surely means that I don't know" - Kansas
Microsoft Access MVP | Access Website | Access Blog | Email
Go to the top of the page
 
JV63
post Jan 16 2017, 12:10 PM
Post#15



Posts: 183
Joined: 16-August 12



Doing a step into takes me through the program and it's working. Not sure if that's what you mean though as the module still doesn't seem to execute.
Go to the top of the page
 
theDBguy
post Jan 16 2017, 12:13 PM
Post#16


Access Wiki and Forums Moderator
Posts: 71,225
Joined: 19-June 07
From: SunnySandyEggo


Hi,

What I meant was something like this:

1. Put a break point at the beginning of the function BackupOnLoad
2. From the Immediate Window (Ctrl+G) enter BackupOnLoad
3. Step through the code after it hits the break point

Hope it makes sense...

--------------------
Just my 2 cents... "And if I claim to be a wise man, it surely means that I don't know" - Kansas
Microsoft Access MVP | Access Website | Access Blog | Email
Go to the top of the page
 
JV63
post Jan 16 2017, 01:12 PM
Post#17



Posts: 183
Joined: 16-August 12



Thanks for clarifying. If I did that correctly I hit the reserved error in between these two lines of the new module.

MsgBox Err.Description, , "BackupOnOpen()"
Resume BackupOnOpen_Exit

It highlights MsdBox and when I step into the next line I get the reserved error.
Go to the top of the page
 
theDBguy
post Jan 16 2017, 01:20 PM
Post#18


Access Wiki and Forums Moderator
Posts: 71,225
Joined: 19-June 07
From: SunnySandyEggo


Hi,

At least we're getting somewhere now...

Try the following:

1. Comment out the line

'On Error GoTo BackupOnOpen_Err

2. Then try stepping through the code again

You should get a different line highlighted when you get the error this time.

--------------------
Just my 2 cents... "And if I claim to be a wise man, it surely means that I don't know" - Kansas
Microsoft Access MVP | Access Website | Access Blog | Email
Go to the top of the page
 
JV63
post Jan 16 2017, 01:28 PM
Post#19



Posts: 183
Joined: 16-August 12



Ok, not I got the reserved error with this line highlighted.

If DCount("BackupDate", "tblBackupDetails", "BackupDate = date()") <> 0 Then

I do have a table named tblBackupDetails in the back-end database as I should to post stats to it from the backups.
Go to the top of the page
 
theDBguy
post Jan 16 2017, 01:34 PM
Post#20


Access Wiki and Forums Moderator
Posts: 71,225
Joined: 19-June 07
From: SunnySandyEggo


Hi,

Now we're getting closer...

In the Immediate Window, try entering the following and let us know what you get:

?DCount("BackupDate", "tblBackupDetails", "BackupDate = date()")

--------------------
Just my 2 cents... "And if I claim to be a wise man, it surely means that I don't know" - Kansas
Microsoft Access MVP | Access Website | Access Blog | Email
Go to the top of the page
 
2 Pages V  1 2 >


Custom Search
RSSSearch   Top   Lo-Fi    14th December 2017 - 10:19 AM