VBA - SQL Update Query based on filename

[BigRods]

Hi,

Hoping someone could help - I've got a form with a text box that has a filename in (taken from a table in my database) - also a button that links to a Open File dialog box. When the button is pressed, I'd like the selected file to be updated in the text box and database.
I'm just getting a problem with the syntax on the update query, when looking through the Immediates Window, it's just giving SET File_Location = ' '
My code is as follows:

strSQL = "UPDATE tbl_Dashboard SET File_Location = '" & varfile & "'"
Set objfilename = Application.FileDialog(msoFileDialogFilePicker)
objfilename.Show
With objfilename
.AllowMultiSelect = False
End With

For Each varfile In objfilename.SelectedItems
    txtFileName.Value = "" & varfile & ""
    DoCmd.SetWarnings False
    Debug.Print strSQL
    DoCmd.RunSQL (strSQL)
    DoCmd.SetWarnings True
Next

Many thanks for any help!

 

[Russell Hauf]

So if you are potentially updating multiple files (selected by the user), why update your text box? It will always end up with the last one, right? I'd think something like this should work (I'd also move the SetWarnings outside of the loop):

Set objfilename = Application.FileDialog(msoFileDialogFilePicker)
objfilename.Show
With objfilename
.AllowMultiSelect = False
End With

DoCmd.SetWarnings Falso

For Each varfile In objfilename.SelectedItems
    strSQL = "UPDATE tbl_Dashboard SET File_Location = '" & varfile & "'"
    Debug.Print strSQL
    DoCmd.RunSQL (strSQL)
Next

DoCmd.SetWarnings True

Alternatively, if you really want to update that textbox with every file as you go, ending up only showing the last one, you could do something like this (though I don't recommend it):

Set objfilename = Application.FileDialog(msoFileDialogFilePicker)
objfilename.Show
With objfilename
.AllowMultiSelect = False
End With

DoCmd.SetWarnings False

For Each varfile In objfilename.SelectedItems
    txtFileName.Value = "UPDATE tbl_Dashboard SET File_Location = '" & varfile & "'"
    Debug.Print txtFileName.Text
    DoCmd.RunSQL (txtFileName.Text)
Next

DoCmd.SetWarnings True

 

[BigRods]

Brilliant - that's worked, thanks so much (1st option). Do you know why that's worked moving the strSQL string into the loop?

The reason I had the text box is that the form is used to select an Excel dashboard that is then updated from the database - the text box displays the current file location of the dashboard file. The button is to select a different file if necessary.

 

[Russell Hauf]

Well, you had to move SQL string into the loop because varFile wasn't defined yet. The purpose of your original loop was to account for the user selecting multiple files. If you are pretty confident that the user will only select one file, and you want to update the form based on what they pick (or at least the last file they pick), then use the second option.