I'm trying to add an attachment to a field "SDS" in my table "ChemicalLibrary" When I'm adding a completely new record to the table it works perfectly (the first half of the code below). My problem arises when I'm trying to update a record in the table and add an attachment. Below I'm trying to add the attachment to my table "ChemicalLibrary" where the primary key field "ID" =597. The SQL update queries work just fine for updating the other fields; however, I couldn't figure out how to write a SQL update query for adding the string "filepathSDS" containing the filepath for the attachment to the attachment "SDS.Filedata" field in my table so i decided to go with the recordset approach since it worked in the first half of my code for adding new records to my table. I keep getting the Error '3251' Operation is not Supported for this Data Type of Object.
The error is shown in larger Red font in the code below
Any help with be greatly appreciated.
Thanks,
Vonik
The error is shown in larger Red font in the code below
Code:
Private Sub CommandSaveAndClose_Click()
Dim dbChemicalData As DAO.Database
Set dbChemicalData = CurrentDb
If IsNull(Me.TextChemicalID) Then
Dim NewChemicalID As Long
Dim NewChemical As DAO.Recordset
Dim NewSDS As DAO.Recordset2
Set NewChemical = dbChemicalData.OpenRecordset("ChemicalLibrary")
NewChemical.AddNew
NewChemical("ChemicalName").Value = Me.ComboChemicalName
NewChemical("CommonName").Value = Me.ComboCommonName
NewChemical("Supplier").Value = Me.ComboSupplier
NewChemical("CAS").Value = Me.ComboCAS
NewChemical("Fire").Value = Me.CheckFire
NewChemical("Reactive").Value = Me.CheckReactive
NewChemical("Pressure").Value = Me.CheckPressure
NewChemical("Acute").Value = Me.CheckAcute
NewChemical("Chronic").Value = Me.CheckChronic
NewChemical("Prop65").Value = Me.CheckProp65
NewChemical("Temp").Value = Me.ComboTemp
NewChemical("SPressure").Value = Me.ComboPressure
NewChemical("DOT").Value = Me.ComboDOT
NewChemical("PhysicalState").Value = Me.FrameState
NewChemical("Active").Value = Me.FrameActive
NewChemicalID = NewChemical("ID").Value
If IsEmpty(filepathSDS) Then
Else
Set NewSDS = NewChemical.Fields("SDS").Value
NewSDS.AddNew
NewSDS.Fields("filedata").LoadFromFile filepathSDS
NewSDS.Update
MsgBox "You have added a new Chemical & SDS " & Me.ComboCommonName.Value & " to the library."
NewSDS.Close
Set NewSDS = Nothing
End If
NewChemical.Update
NewChemical.Close
dbChemicalData.Close
Set NewChemical = Nothing
Set dbChemicalData = Nothing
MsgBox "You have added a new Chemical " & Me.ComboCommonName.Value & " to the library."
Else
Dim ChemicalID As Long
ChemicalID = Me.TextChemicalID
'dbChemicalData.Execute ("UPDATE ChemicalLibrary SET ChemicalLibrary.ChemicalName = " & Chr$(34) & Forms!Addchemical.ComboChemicalName & Chr$(34) & " WHERE ChemicalLibrary.ID=" & ChemicalID)
'dbChemicalData.Execute ("UPDATE ChemicalLibrary SET ChemicalLibrary.CommonName = " & Chr$(34) & Forms!Addchemical.ComboCommonName & Chr$(34) & " WHERE ChemicalLibrary.ID=" & ChemicalID)
'dbChemicalData.Execute ("UPDATE ChemicalLibrary SET ChemicalLibrary.Supplier = " & Chr$(34) & Forms!Addchemical.ComboSupplier & Chr$(34) & " WHERE ChemicalLibrary.ID=" & ChemicalID)
'dbChemicalData.Execute ("UPDATE ChemicalLibrary SET ChemicalLibrary.CAS = " & Chr$(34) & Forms!Addchemical.ComboCAS & Chr$(34) & " WHERE ChemicalLibrary.ID=" & ChemicalID)
dbChemicalData.Execute ("UPDATE ChemicalLibrary SET ChemicalLibrary.Fire = " & Chr$(34) & Forms!Addchemical.CheckFire & Chr$(34) & " WHERE ChemicalLibrary.ID=" & ChemicalID)
dbChemicalData.Execute ("UPDATE ChemicalLibrary SET ChemicalLibrary.Reactive = " & Chr$(34) & Forms!Addchemical.CheckReactive & Chr$(34) & " WHERE ChemicalLibrary.ID=" & ChemicalID)
dbChemicalData.Execute ("UPDATE ChemicalLibrary SET ChemicalLibrary.Pressure = " & Chr$(34) & Forms!Addchemical.CheckPressure & Chr$(34) & " WHERE ChemicalLibrary.ID=" & ChemicalID)
dbChemicalData.Execute ("UPDATE ChemicalLibrary SET ChemicalLibrary.Acute = " & Chr$(34) & Forms!Addchemical.CheckAcute & Chr$(34) & " WHERE ChemicalLibrary.ID=" & ChemicalID)
dbChemicalData.Execute ("UPDATE ChemicalLibrary SET ChemicalLibrary.Chronic = " & Chr$(34) & Forms!Addchemical.CheckChronic & Chr$(34) & " WHERE ChemicalLibrary.ID=" & ChemicalID)
dbChemicalData.Execute ("UPDATE ChemicalLibrary SET ChemicalLibrary.Prop65 = " & Chr$(34) & Forms!Addchemical.CheckProp65 & Chr$(34) & " WHERE ChemicalLibrary.ID=" & ChemicalID)
dbChemicalData.Execute ("UPDATE ChemicalLibrary SET ChemicalLibrary.Temp = " & Chr$(34) & Forms!Addchemical.ComboTemp & Chr$(34) & " WHERE ChemicalLibrary.ID=" & ChemicalID)
dbChemicalData.Execute ("UPDATE ChemicalLibrary SET ChemicalLibrary.SPressure = " & Chr$(34) & Forms!Addchemical.ComboPressure & Chr$(34) & " WHERE ChemicalLibrary.ID=" & ChemicalID)
dbChemicalData.Execute ("UPDATE ChemicalLibrary SET ChemicalLibrary.DOT = " & Chr$(34) & Forms!Addchemical.ComboDOT & Chr$(34) & " WHERE ChemicalLibrary.ID=" & ChemicalID)
dbChemicalData.Execute ("UPDATE ChemicalLibrary SET ChemicalLibrary.PhysicalState = " & Chr$(34) & Forms!Addchemical.FrameState & Chr$(34) & " WHERE ChemicalLibrary.ID=" & ChemicalID)
dbChemicalData.Execute ("UPDATE ChemicalLibrary SET ChemicalLibrary.Active = " & Chr$(34) & Forms!Addchemical.FrameActive & Chr$(34) & " WHERE ChemicalLibrary.ID=" & ChemicalID)
If IsNull(DLookup("SDS.filedata", "ChemicalLibrary", "ID= " & ChemicalID)) Then
If IsEmpty(filepathSDS) Then
Else
'dbChemicalData.Execute ("UPDATE ChemicalLibrary SET ChemicalLibrary!SDS.filedata = " & filepathSDS & " WHERE ChemicalLibrary.ID=" & ChemicalID)
Dim UpdateChemical As DAO.Recordset
Dim UpdateSDS As DAO.Recordset2
Set UpdateChemical = dbChemicalData.OpenRecordset("Select * From ChemicalLibrary Where ID = 597")
UpdateChemical.Edit
Set UpdateSDS = UpdateChemical.Fields("SDS").Value
UpdateSDS.AddNew
[COLOR=#b22222][SIZE=3] UpdateSDS.Fields("filedata").LoadFromFile = filepathSDS[/SIZE][/COLOR]
UpdateSDS.Update
UpdateChemical.Update
Set UpdateSDS = Nothing
Set UpdateChemical = Nothing
Set dbChemicalData = Nothing
UpdateSDS.Close
UpdateChemical.Close
dbChemicalData.Close
End If
End If
MsgBox "Chemical " & Me.ComboCommonName.Value & "has been updated"
End If
'DoCmd.Close acForm, "AddChemical"
End Sub
Code:
Public Function SelectFile() As String
Dim f As Object
Set f = Application.FileDialog(3)
f.AllowMultiSelect = False
If f.Show = True Then
SelectFile = f.selecteditems(1)
filepathSDS = SelectFile
End If
End Function
Any help with be greatly appreciated.
Thanks,
Vonik