My Assistant
![]()
Custom Search
|
![]() ![]() |
![]() |
![]() Post#1 | |
Posts: 197 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 |
![]() Post#2 | |
![]() UA Moderator Posts: 76,905 Joined: 19-June 07 From: SunnySandyEggo ![]() | Hi, Can you post the code for your module? Thanks. |
![]() Post#3 | |
Posts: 197 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 |
![]() Post#4 | |
![]() UA Moderator Posts: 76,905 Joined: 19-June 07 From: SunnySandyEggo ![]() | Thanks. Which line gets highlighted when you get the error? |
![]() Post#5 | |
Posts: 197 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 |
![]() Post#6 | |
![]() UA Moderator Posts: 76,905 Joined: 19-June 07 From: SunnySandyEggo ![]() | Do you still get an error if you don't use "Call?" |
![]() Post#7 | |
Posts: 197 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. |
![]() Post#8 | |
![]() UA Moderator Posts: 76,905 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... |
![]() Post#9 | |
Posts: 197 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 |
![]() Post#10 | |
![]() UA Moderator Posts: 76,905 Joined: 19-June 07 From: SunnySandyEggo ![]() | If you hit Ctrl+G and then enter BackupOnOpen Do you still get the same error? |
![]() Post#11 | |
Posts: 197 Joined: 16-August 12 ![]() | Yes, reserved error. |
![]() Post#12 | |
![]() UA Moderator Posts: 76,905 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? |
![]() Post#13 | |
Posts: 197 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. |
![]() Post#14 | |
![]() UA Moderator Posts: 76,905 Joined: 19-June 07 From: SunnySandyEggo ![]() | Okay, one last thing... Have you tried stepping through the function code? |
![]() Post#15 | |
Posts: 197 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. |
![]() Post#16 | |
![]() UA Moderator Posts: 76,905 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... |
![]() Post#17 | |
Posts: 197 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. |
![]() Post#18 | |
![]() UA Moderator Posts: 76,905 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. |
![]() Post#19 | |
Posts: 197 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. |
![]() Post#20 | |
![]() UA Moderator Posts: 76,905 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()") |
![]()
Custom Search
|
![]() | Search Top Lo-Fi | 14th December 2019 - 04:40 PM |