The changes I made are in RED.
Note that the concatenation character you should use is the ampersand (&), not the plus (+).
My preference would be to use DAO instead of ADODB.
And I don't like turning the warnings off because you don't get error messages.
Code:
Function InsertFrames(txtCameraLibrary As String, txtCameraLibraryID As Long, Status As String, Prefix As String, NextRecNo As Long, StartNumber As Text, NumberOfFrames As Long)
'Runs when 'cmdCalc' is clicked
'calculates camera frame numbers and inserts a new record for each frame into tblCameraImage and tblDigitalImage
Stop
On Error GoTo MyError
Dim frm As Form
Dim cmd As ADODB.Command
Dim strSQL As String
Dim okFlag1 As Boolean
Dim okFlag2 As Boolean
Dim n As Integer
Dim x As Integer
Dim ExtSource As Long
Dim FileName As String
Dim lngStartNumber As Long
Set frm = Forms!frmEditCameraImageDetails
Set cmd = New ADODB.Command
cmd.ActiveConnection = CurrentProject.Connection
cmd.CommandType = adCmdText
DoCmd.SetWarnings False
okFlag1 = False
okFlag2 = False
lngStartNumber = Val(StartNumber)
'data validation for Status
If Status = "Unprocessed" Then
Status = StrConv("Unprocessed", vbProperCase)
okFlag1 = True
End If
If okFlag1 = True Then
'data validation for Prefix
If Prefix = "IMG" Then
Prefix = StrConv("IMG", vbUpperCase)
okFlag2 = True
End If
If Prefix = "DI" Then
Prefix = StrConv("DI", vbUpperCase)
okFlag2 = True
End If
If okFlag2 = True Then
For n = 0 To NumberOfFrames - 1
'add new records to tblCameraImage
strSQL = "INSERT INTO [tblCameraImage]([CameraLibraryID],[CameraImageStatus],[CameraFrame],[FrameChronoOrder])"
strSQL = strSQL & " VALUES(" & txtCameraLibraryID & ",""" & Status & """,""" & Prefix & "_" & Format(lngStartNumber + n, "0000") & """,""" & Prefix & "_" & Format(lngStartNumber + n, "0000") & """)"
cmd.CommandText = strSQL
DoCmd.RunSQL strSQL
'add new records to tblDigitalImage
DigStatus = "Unprocessed"
Rating = 1
FileName = "5001." & txtCameraLibrary & ".001." & Prefix & "_" & Format(lngStartNumber + n, "0000") & ".jpg"
Path = "C:\Users\Public\Pictures\Photo Library Database\New Images\Camera Images\Unprocessed Images\External Source\" & [FileName]
strSQL = "INSERT INTO tblDigitalImage(CameraImageID,ImageStatus,ImageRat ing,ImageFileName,FilePath)"
strSQL = strSQL & " VALUES(""" & NextRecNo + n & """, """ & DigStatus & """, """ & Rating & """, """ & FileName & """, """ & Path & """)"
cmd.CommandText = strSQL
DoCmd.RunSQL strSQL
Stop
Next n
DoCmd.SetWarnings True
x = MsgBox("You have successfully added " & [NumberOfFrames] & " new records to tables tblCameraImage & tblDigitalImage", , "Complete")
Else
x = MsgBox("Invalid Prefix", , "Data Validation Error")
End If
Else
x = MsgBox("Invalid Status", , "Data Validation Error")
End If
'refresh frmEditCameraDetails
frm.Requery
frm.Refresh
'close dialog box and return to frmEditCameraDetails
DoCmd.Close acForm, "frmFrames", acSaveNo
MyErrorExit:
Exit Function
MyError:
Dim Msg As String
Msg = "Error in Function InsertFrames" & Err.Number & ": " & Err.Description
MsgBox Msg
Resume MyErrorExit
End Function