[X]My Assistant

[JV63]

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

[theDBguy]

Hi,

Can you post the code for your module? Thanks.

[JV63]

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

[theDBguy]

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

[JV63]

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

[theDBguy]

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

[JV63]

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.

[theDBguy]

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

[JV63]

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

[theDBguy]

If you hit Ctrl+G and then enter

BackupOnOpen

Do you still get the same error?

[JV63]

Yes, reserved error.

[theDBguy]

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?

[JV63]

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.

[theDBguy]

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

[JV63]

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.

[theDBguy]

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

[JV63]

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.

[theDBguy]

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.

[JV63]

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.

[theDBguy]

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()")