VBA Insert SQL Statement

[junior6202]

Hi all,

I wrote this code that looks a particular column in an Access table called [CCS_PartsLookUp] column[Part No] and goes row by row and reads the Part No column and searches for a matching image in another Drive. after it finds a match to the part it copies the image and pastes it in another folder that I created. It works excellent but now i want to add another function to the code. In the If statement it states that if the Part No is not equal to Null to copy the image and paste it in the new path. Now i want to add if it is equal to Null then copy that part number that did not have a match to another Access table called [TestTable]. any advice is appreciated, thanks in advance.




[
Function ImageLookup()
Dim dbs As Database ' Im declaring dbs as the database
Dim rst As Recordset
Dim fs As Object
Dim oldPath As String, newPath As String

Set dbs = OpenDatabase("TestTable.[Missing Pic PartNo]") ' Im setting dbs to the table
Set rst = CurrentDb.OpenRecordset("SELECT CCS_PartsLookUp.[Part No]" _
& " FROM CCS_PartsLookUp")

Set fs = CreateObject("Scripting.FileSystemObject")
While Not rst.EOF
oldPath = "T:" 'Folder file is located in
newPath = "C:\Users\EReyes\Desktop\eReplacements pics" 'Folder to copy file to
'MsgBox rst![Part No] & ".jpg"
If Dir(oldPath & "\" & rst![Part No] & ".jpg") <> "" Then fs.CopyFile oldPath & "\" & rst![Part No] & ".jpg", newPath & "\" & rst![Part No] & ".jpg"
Else: Dir(oldPath & "\" & rst![Part No] & ".jpg") = "" Then dbs.Execute "INSERT INTO TestTable" _
& "(Missing Pic PartNo) VALUES" & (oldPath & "\" & rst![Part No]) 'This is the insert into statement where I am having issues.




Wend
Set fs = Nothing '--matching Part No.

End Function ]

[jonh]

As far as I know you can't set a database object from a table.
Your function isn't returning a value. They don't have to, but then it might as well be a sub.
Not sure what your if / else statement is doing.
If a field name contains spaces you need to surround it with square brackets. You're generally much better off using camel case for field names.
And the brackets for your values list need to be inside the quotes to form part of the sql string.

I haven't run this but it's more like I'd imagine your code should read.


Function ImageLookup(OldPath As String, NewPath As String) As Boolean
On Error GoTo err_ImageLookup
Dim dbs As DAO.Database, rst As DAO.Recordset, fs As Object
Dim strOldFile As String, strNewFile As String
Set dbs = CurrentDb
Set rst = dbs.OpenRecordset("SELECT [Part No] FROM CCS_PartsLookUp")
Set fs = CreateObject("Scripting.FileSystemObject")

If Not fs.folderexists(OldPath) Then Exit Function
If Not fs.folderexists(NewPath) Then Exit Function

While Not rst.EOF
strOldFile = OldPath & rst![Part No] & ".jpg"
strNewFile = NewPath & rst![Part No] & ".jpg"
If fs.FileExists(strOldFile) Then
fs.CopyFile strOldFile, strNewFile
Else
dbs.Execute "INSERT INTO TestTable " & _
"([Missing Pic PartNo]) VALUES (" & strOldFile & ")"
End If
Wend

ImageLookup = True

err_ImageLookup:
Select Case Err.Number
Case 0
Case Else
Debug.Print strOldFile; strNewFile
MsgBox Err.Description
End Select

On Error Resume Next
Set fs = Nothing
rst.Close: Set rst = Nothing
dbs.Close: Set dbs = Nothing
End Function