Printable Version of Topic

Click here to view this topic in its original format

UtterAccess Forums _ Visual Basic 6 and Earlier _ Executing A Module On Form Load?

Posted by: JV63 Jan 13 2017, 09:32 AM

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

Posted by: theDBguy Jan 13 2017, 10:49 AM

Hi,

Can you post the code for your module? Thanks.

Posted by: JV63 Jan 13 2017, 10:58 AM

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

Posted by: theDBguy Jan 14 2017, 11:22 AM

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

Posted by: JV63 Jan 16 2017, 07:53 AM

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

Posted by: theDBguy Jan 16 2017, 10:25 AM

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

Posted by: JV63 Jan 16 2017, 10:28 AM

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.

Posted by: theDBguy Jan 16 2017, 10:31 AM

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

Posted by: JV63 Jan 16 2017, 10:44 AM

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

Posted by: theDBguy Jan 16 2017, 11:35 AM

If you hit Ctrl+G and then enter

BackupOnOpen

Do you still get the same error?

Posted by: JV63 Jan 16 2017, 11:46 AM

Yes, reserved error.

Posted by: theDBguy Jan 16 2017, 11:51 AM

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?

Posted by: JV63 Jan 16 2017, 11:55 AM

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.

Posted by: theDBguy Jan 16 2017, 12:01 PM

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

Posted by: JV63 Jan 16 2017, 12:10 PM

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.

Posted by: theDBguy Jan 16 2017, 12:13 PM

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

Posted by: JV63 Jan 16 2017, 01:12 PM

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.

Posted by: theDBguy Jan 16 2017, 01:20 PM

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.

Posted by: JV63 Jan 16 2017, 01:28 PM

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.

Posted by: theDBguy Jan 16 2017, 01:34 PM

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

Posted by: JV63 Jan 16 2017, 01:39 PM

By putting that into the immediate window I get the reserved error popup (run time 2950).

Posted by: theDBguy Jan 16 2017, 03:12 PM

Okay, so now, you'll just need to figure out a different method or approach to check if the code should run or not other than the way you're attempting to now. For example, do you even get an error with the following?

?DCount("*", "tblBackupDetails", "BackupDate=Date")

If so, can you please post all the field names in tblBackupDetails? Thanks.

Posted by: JV63 Jan 16 2017, 03:28 PM

I do get an reserved error with that immediate code also.

My backup table fields are:

ComputerName - Short Text
BackupDate - Date/Time
BackupFolder - Short Text
Filename - Short Text

Posted by: theDBguy Jan 16 2017, 03:44 PM

Okay thanks. I don't see any reserved words in the names you used for your fields. Do you get an error with this?

?DCount("*", "tblBackupDetails")

Posted by: JV63 Jan 16 2017, 03:49 PM

Yes, reserved error as well.

Posted by: theDBguy Jan 16 2017, 04:11 PM

Hi,

See if you can safely use DCount() against other tables without error. If so, then you might have to look more closely at the data in the problem table. Otherwise, check if you have any missing references preventing DCount() to work properly.

Hope it helps...

Posted by: JV63 Jan 16 2017, 04:13 PM

Ok, ,will take a look. the backup table is empty at the moment since it's a new module that's supposed to right to it but could have missing references for Dcount. Good suggestions.

Posted by: theDBguy Jan 16 2017, 04:23 PM

Even if the table is empty, DCount("*","TableName") should return a 0. So, if you can't use DCount() on other tables, then DCount() is having a problem on your database. One common cause is a missing reference.

Posted by: JV63 Jan 16 2017, 04:25 PM

By commenting out the whole Dcount I got it to create a backup database. However I don't see any entry in my tblBackupDetails table.

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

So got closer to doing what I wanted but don't know what not having Dcount not work will do as the intent is to only create a backup once a day. I'm thinking Dcount has something to do with that.

Posted by: theDBguy Jan 16 2017, 04:27 PM

Well, first, we'll need to make sure DCount() is working on your system. So, I would suggest trying it against a different table. As for its use in your function, it's merely checking if a backup was already created today. There is nothing I can see in the code you posted where you add a record to the table when a backup is created on a new day.

Posted by: JV63 Jan 16 2017, 04:38 PM

Yeah, something is up with Dcount cause I'm getting errors against other tables too.

Posted by: theDBguy Jan 16 2017, 05:29 PM

Were you missing any references?

Posted by: JV63 Jan 16 2017, 05:41 PM

Not that I'm aware of. I'll have to look deeper but it's the same code I've used for other access databases and the backup module works perfectly in both backing up the back-end database and making entries in the tbl.

Posted by: theDBguy Jan 16 2017, 07:16 PM

Hi,

If you get stuck, please consider posting a small copy of your DB, so we can give you a hand. Good luck.