Code:
Public Sub InsertData()
Dim dbs As dao.Database, rst As Recordset, rstInsert As Recordset
Dim sSQL As String
Dim sSQLSource As String
Dim i
Set dbs = CurrentDb
sSQLSource = "SELECT Route, Account, [Date], "
sSQLSource = sSQLSource & "[Door 1].FileData, [Door 1].FileName, [Door 1].FileType, "
sSQLSource = sSQLSource & "[Door 2].FileData, [Door 2].FileName, [Door 2].FileType, "
sSQLSource = sSQLSource & "[Door 3].FileData, [Door 3].FileName, [Door 3].FileType, "
sSQLSource = sSQLSource & "[Door 4].FileData, [Door 4].FileName, [Door 4].FileType, "
sSQLSource = sSQLSource & "[Door 5].FileData, [Door 5].FileName, [Door 5].FileType, "
sSQLSource = sSQLSource & "[Door 6].FileData, [Door 6].FileName, [Door 6].FileType, "
sSQLSource = sSQLSource & "[Door 7].FileData, [Door 7].FileName, [Door 7].FileType, "
sSQLSource = sSQLSource & "[Door 8].FileData, [Door 8].FileName, [Door 8].FileType, "
sSQLSource = sSQLSource & "[Door 9].FileData, [Door 9].FileName, [Door 9].FileType, "
sSQLSource = sSQLSource & "[Door 10].FileData, [Door 10].FileName, [Door 10].FileType, "
sSQLSource = sSQLSource & "[Door 11].FileData, [Door 11].FileName, [Door 11].FileType, "
sSQLSource = sSQLSource & "[Door 12].FileData, [Door 12].FileName, [Door 12].FileType, "
sSQLSource = sSQLSource & "[Door 13].FileData, [Door 13].FileName, [Door 13].FileType, "
sSQLSource = sSQLSource & "[Door 14].FileData, [Door 14].FileName, [Door 14].FileType, "
sSQLSource = sSQLSource & "[Door 15].FileData, [Door 15].FileName, [Door 15].FileType, "
sSQLSource = sSQLSource & "Comments "
sSQLSource = sSQLSource & "FROM Submit"
Set rst = dbs.OpenRecordset(sSQLSource)
If rst.RecordCount <> 0 Then
Do While rst.EOF <> True
Debug.Print rst!route
If DCount("*", "imgdest", "[Route] = " & rst!route) = 0 Then
Debug.Print " NO EXISTING RECORD"
Debug.Print " " & rst![door 1.filename]
If Not IsNull(rst![door 1.filename]) Then
Debug.Print " Image in 1"
Set rstInsert = dbs.OpenRecordset("SELECT Route, Account, [Date], [Door 1].FileData, [Door 1].FileName, [Door 1].FileType, Comments FROM imgDest")
rstInsert.AddNew
rstInsert![route] = rst![route]
rstInsert![account] = rst![account]
rstInsert![Date] = rst![Date]
rstInsert![Door 1.Filedata] = rst![Door 1.Filedata]
rstInsert![door 1.filename] = rst![door 1.filename]
rstInsert![comments] = rst![comments]
rstInsert.Update
rstInsert.Close
End If
'REPEAT THE CODE BELOW FOR EACH OF YOUR POSSIBLE IMAGE FIELDS
If Not IsNull(rst![door 2.filename]) Then
Debug.Print " Image in 2"
Set rstInsert = dbs.OpenRecordset("SELECT Route, [Door 2].FileData, [Door 2].FileName, [Door 2].FileType FROM imgDest WHERE [route] = " & rst!route)
rstInsert.Edit
rstInsert![Door 2.Filedata] = rst![Door 2.Filedata]
rstInsert![door 2.filename] = rst![door 2.filename]
rstInsert.Update
rstInsert.Close
End If
If Not IsNull(rst![door 3.filename]) Then
Debug.Print " Image in 3"
Set rstInsert = dbs.OpenRecordset("SELECT Route, [Door 3].FileData, [Door 3].FileName, [Door 3].FileType FROM imgDest WHERE [route] = " & rst!route)
rstInsert.Edit
rstInsert![Door 3.Filedata] = rst![Door 3.Filedata]
rstInsert![door 3.filename] = rst![door 3.filename]
rstInsert.Update
rstInsert.Close
End If
End If
rst.MoveNext
Loop
Else
MsgBox "NO records to process"
End If
rst.Close
Set dbs = Nothing
End Sub
I just added door 3 to check if it would work, I added an image to door three in my first test entry in submit and it ignores it and just handles doors 1 and 2 for all entries.