Define Record source in vba module public function

[hnhpak]

I have a following module for backup backend file on open front end:
------------------------------------------------

Public Function BackupOnOpen()


' Ensure you have a \ on the end of the pathname.
Const BACKUP_PATH = "D:\Database\Backup\"


On Error GoTo BackupOnOpen_Err


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


Dim strSourcePath As String
Dim strSourceFile As String
Dim strBackupFile As String


strSourcePath = "D:\Database\BackEnd Data\"
strSourceFile = "BE_FileName.accdb"



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 BackupDetails " _
& "(BackupDate, ComputerName, BackupFolder, Filename) " _
& "VALUES ('" & Date & "', '" & Environ("COMPUTERNAME") _
& "', '" & BACKUP_PATH & "', '" & strBackupFile & "');"
DoCmd.RunSQL SQL
SQL = "DELETE * FROM BackupDetails WHERE BackupDate < date() - 30;"
DoCmd.RunSQL SQL
DoCmd.SetWarnings True
BackupOnOpen_Exit:
Exit Function
BackupOnOpen_Err:
MsgBox Err.Description, , "BackupOnOpen()"
Resume BackupOnOpen_Exit
End Function

--------------------------------------------
This code works perfect, it want to make a little changes:

1. TheLine:
Const BACKUP_PATH = "E:\Database\Backup\" want to replace the line as under:
Const BACKUP_PATH = [Company Details].[BackupPath]

Whereas [Company Detail] is a table and [BackupPath] is the field where the path E:\Database\Backup\ is stored

2. strSourcePath = "D:\Database\BackEnd Data\"
strSourceFile = "BE_FileName.accdb"


Want to replace above two lines as under:
strSourcePath = [Company Details].[BE_Path]
strSourceFile = [Company Details].[BE_FileName]

Whereas [Company Detail] is a table and [BE_Path] is the field where the path D:\Database\BackEnd Data\ is stored and
[BE_FileName] is the field where the File Name BE_FileName.accdb is stored

All this I need to control and make changes of the BE Table Path and file and Backup Path through table instead of changes in module.

Module shows compile error on Table Name, As what I understand is due to the reason that the record source is not define here, this is public function module not a form or report, where the record source is define in properties. I want to know how to define record source in vba module public function.

Any help in modification the above code is highly appreciated.

Thanks in Advance

Regards

Haseeb

[CJ_London]

try - you don't need the string assignments

dim rst as dao.recordset

set rst=currentdb.openrecordset(SELECT BackupPath, BE_Path, BE_FileName FROM [Company Details]) 'assumes this is a one record table, otherwise you will need to add a criteria
if not rst.eof then
    fso.CopyFile rst!BackupPath & rst!BE_Path, rst!BackupPath & strBackupFile, True
end if
set rst=nothing

[ItsMe]

You can pass variables to your function by adding arguments to its parameters.

Public Function BackupOnOpen(strSourcePath As String, strSourceFile As String)

Because you have these variables declared in the parameters, you do not need them declared within the function. So comment out the two you do not need declared.

'Dim strSourcePath As String
 'Dim strSourceFile As String
 Dim strBackupFile As String

Now, when you call your function, you can define the parameters by adding the values to the call.
Call BackupOnOpen("D:\Database\BackEnd Data\", "BE_FileName.accdb")


Or you can pass a new variable into the call
Call BackupOnOpen(strMyPath, strMyFile)

[hnhpak]

Code reflects some compile error on the line rst=currentdb.openrecordset(SELECT

Trying to figure out the error, if you found error let me know.

[CJ_London]

sorry - missed the quotation marks - also for future reference 'some compile error' says nothing - report the error description as well

set rst=currentdb.openrecordset("SELECT BackupPath, BE_Path, BE_FileName FROM [Company Details]")

[hnhpak]

Thanks a lot Ajaz, It works.

I am now marking this as solved...