Solved Save filepath to table (1 Viewer)

Momma

Member
Local time
Today, 18:13
Joined
Jan 22, 2022
Messages
114
Hey Guys, I have another issue. I'm trying to save the filepath to the table and I'm getting the error below. I thought it would be the same as for saving the filename. I don't understand why the field has to be mentioned twice, eg """ & Filename & """,""" & Filename & """
Thank you in advance>

1647738837141.png


Code:
    Set FD = Application.FileDialog(msoFileDialogFilePicker)
    With FD
        .InitialFileName = DogFolder
        .AllowMultiSelect = IIf(DocType = "BulkAdd", True, False)
        .Title = "Select a File"
        .Filters.Clear
        If DocType = "Entity" Then
            .Filters.Add "JPG Files", "*.jpg"
            .Filters.Add "GIF Files", "*.gif"
            .Filters.Add "PNG Files", "*.png"
        Else
            .Filters.Add "All Files", "*.*"
        End If
        If .Show = True Then
            For Each PickFile In .SelectedItems
                FilePath = PickFile
                Filename = FileNameFromPath(FilePath)
                If DocType = "Entity" Then
                    Forms!frmDogProfile!MyPicture = Filename
                    NewFilePath = DogIDFolder & "\" & Filename
                ElseIf DocType = "Document" Then
                    Forms!frmDocument!Filename = Filename
                    Forms!frmDocument!Description = Filename
                    NewFilePath = DocumentFolder & "\" & Filename
                    'Forms!frmDocument.RefreshDocImage
                Else ' Bulk Add
                    DoCmd.SetWarnings False
                    DoCmd.RunSQL "INSERT INTO tblDocument (DogID, Filename, FilePath, Description) " & _
                        "VALUES (" & DogID & ", """ & Filename & """,""" & Filename & """, """ & NewFilePath & """,""" & NewFilePath & """)"
                    DoCmd.SetWarnings True
                    NewFilePath = DocumentFolder & "\" & Filename
                End If
                FileCopy FilePath, NewFilePath
                If Not FileExists(NewFilePath) Then
                    MsgBox "ERROR: File Copy Failed. " & NewFilePath
                    Exit Sub
                End If
            Next
        End If
    End With
 

pbaldy

Wino Moderator
Staff member
Local time
Today, 00:13
Joined
Aug 30, 2003
Messages
36,115
You have 4 fields in the INSERT INTO clause but 5 in the VALUES clause.
 

CJ_London

Super Moderator
Staff member
Local time
Today, 07:13
Joined
Feb 19, 2013
Messages
16,521
message is telling you the problem - you have 4 destination fileds and 5 values

DoCmd.RunSQL "INSERT INTO tblDocument (DogID, Filename, FilePath, Description) " & _
"VALUES (" & DogID & ", """ & Filename & """,""" & Filename & """, """ & NewFilePath & """,""" & NewFilePath & """)"
 

pbaldy

Wino Moderator
Staff member
Local time
Today, 00:13
Joined
Aug 30, 2003
Messages
36,115
Fastest fingers in the west. :p
 

CJ_London

Super Moderator
Staff member
Local time
Today, 07:13
Joined
Feb 19, 2013
Messages
16,521
. I don't understand why the field has to be mentioned twice
nor do I - it's your code but seems to me you are mixing everything up. Here you are saying

Forms!frmDocument!Filename = Filename
Forms!frmDocument!Description = Filename
NewFilePath = DocumentFolder & "\" & Filename

but in the insert sql you seem to be saying that description should be newfilepath, not filename

BTW description is not a good name for a field - it is a reserved word
 

Momma

Member
Local time
Today, 18:13
Joined
Jan 22, 2022
Messages
114
I'm not very familiar yet with VBA but I see now what the problem is.
I changed it and is working now.
Thank you so much, I do appreciate your help.

Code:
       DoCmd.RunSQL "INSERT INTO tblDocument (DogID, Filename, FilePath) " & _
       "VALUES (" & DogID & ", """ & Filename & """,""" & NewFilePath & """)"
 

Users who are viewing this thread

Top Bottom