Hello. I am adding file attachments to a table using the code below (and I know the consequences but need to do what is required) that is on a submit button. As you can see, I am adding different fields to the table. With what I have below only adds the Attachment field and the PropertyInspectionID field. The other 3 do not get entered into the table. I do a step thru and the info shows. The sql statement has the order of the fields in the table. I rarely work with arrays and I have a feeling that is where the problem is.
Can another set of eyes look this over and see where or what I'm missing?
Dim cn As ADODB.Connection
Dim rs As New ADODB.Recordset
Dim filepath As String: filepath = Me.txtPath
Dim sqlAttachment As String
sqlAttachment = "SELECT AttachmentTitle, AttachmentDescription, AttachmentExtension, Attachment, PropertyInspectionID " & _
"FROM tblPropertyInspectionAttachment " & _
"WHERE PropertyInspectionID = " & Me.txtPropertyInspectionID
Set cn = CurrentProject.Connection
'arrays are zero based
Dim fieldList(4) As Variant 'create array to hold list of fields to insert values into
Dim valueList(4) As Variant 'create array to hold list of values to insert values into
fieldList(0) = "AttachmentTitle"
fieldList(1) = "AttachmentDescription"
fieldList(2) = "AttachmentExtension"
fieldList(3) = "Attachment"
fieldList(4) = "PropertyInspectionID"
valueList(0) = Me.txtAttachmentTitle
valueList(1) = Me.txtAttachmentDescription
valueList(2) = Me.txtAttachmentExtension
valueList(3) = filepath
valueList(4) = Me.txtPropertyInspectionID
'Create stream to hold data
Dim mStream As New ADODB.Stream
mStream.Type = adTypeBinary
'Open recordset with just the fields we need
rs.Open sqlAttachment, cn, adOpenForwardOnly, adLockOptimistic
'add new record with our values,
rs.AddNew fieldList(4), valueList(4)
'then write our stream
rs.Fields("Attachment").Value = mStream.Read
Set mStream = Nothing
Set rs = Nothing
Set cn = Nothing