Solved Save filepath to table

[Momma]

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>

    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]

You have 4 fields in the INSERT INTO clause but 5 in the VALUES clause.

 

[CJ_London]

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 & """)"

 

[CJ_London]

snap!

 

[pbaldy]

Fastest fingers in the west.

 

[CJ_London]

. 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]

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.

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