Results 1 to 5 of 5
  1. #1
    Join Date
    May 2016
    Posts
    9
    Provided Answers: 1

    Answered: reference a table value in VBA

    So here is what i have. I have a form that allows the user to set a file location to store backups the form saves the folder location to a table. How do i reference the table in my code?

    Code:
    Const BACKUP_PATH = ?

  2. Best Answer
    Posted by xpeppyx

    "I figured it out. The constant was just part of the code i was using. But its being used further down in the code (that i didnt show)
    Code:
    Dim fso
    Set fso = CreateObject("Scripting.FileSystemObject")
    fso.CopyFile strSourcePath & strSourceFile, BACKUP_PATH & strBackupFile, True
    Set fso = Nothing
    So instead of having this part use backup_path i replaced it with strFileloc that i had backup_path set to and it works..

    Code:
    Dim fso
    Set fso = CreateObject("Scripting.FileSystemObject")
    fso.CopyFile strSourcePath & strSourceFile, strFileloc & strBackupFile, True
    Set fso = Nothing
    My apology if what i'm saying doesnt make since. I'm still new to this and figuring things out."


  3. #2
    Join Date
    May 2016
    Posts
    9
    Provided Answers: 1
    I figured it out. Used a set a variable as a dlookup and got it to work. Now if i can just figure out why when it saves it saves it 1 folder up.. If its set to C:\folder1\folder2\folder3 it saves to C:\folder1\folder2

  4. #3
    Join Date
    May 2016
    Posts
    9
    Provided Answers: 1

    Unhappy

    Ok so here is where i am at. I know the reason why it is not going to the correct folder and that is because the folder picker doesnt include a \ at the end of the file name it retrieves. So i changed the code to this.

    Code:
    Dim FolderName As String
    With Application.FileDialog(msoFileDialogFolderPicker)
      .AllowMultiSelect = False
      .Show
      On Error Resume Next
      FolderName = .SelectedItems(1) & "\"
      Err.Clear
      On Error GoTo 0
      Me.backup_loc = FolderName
    End With
    So now it shows the \ at the end of the location on my table. But when i run the backup it acts like the \ is not there.

    This is what i have for the reference on my backup code

    Code:
    Dim strFileloc As String
    
    strFileloc = DLookup("[backup_folder_loc]", "Backup_loc")
    
    Const BACKUP_PATH = "strFileloc"
    I have even tried adding the \ on this part of the code like so
    Code:
    Dim strFileloc As String
    
    strFileloc = DLookup("[backup_folder_loc]", "Backup_loc")
    
    Const BACKUP_PATH = "strFileloc" & "\"
    and still no luck. Any suggestions?

  5. #4
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    so what is the value of BACKUP_PATH ?

    put a break point iont he code and examine the value

    where are you setting this CONSTANT?

    Code:
    Const BACKUP_PATH = "strFileloc" & "\"
    I expect will resolve to strFileloc\

    As the value is coming from a dblookup why decalre a constant?
    I'd rather be riding on the Tiger 800 or the Norton

  6. #5
    Join Date
    May 2016
    Posts
    9
    Provided Answers: 1
    I figured it out. The constant was just part of the code i was using. But its being used further down in the code (that i didnt show)
    Code:
    Dim fso
    Set fso = CreateObject("Scripting.FileSystemObject")
    fso.CopyFile strSourcePath & strSourceFile, BACKUP_PATH & strBackupFile, True
    Set fso = Nothing
    So instead of having this part use backup_path i replaced it with strFileloc that i had backup_path set to and it works..

    Code:
    Dim fso
    Set fso = CreateObject("Scripting.FileSystemObject")
    fso.CopyFile strSourcePath & strSourceFile, strFileloc & strBackupFile, True
    Set fso = Nothing
    My apology if what i'm saying doesnt make since. I'm still new to this and figuring things out.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •