Forum Discussion

Maxine14062's avatar
Maxine14062
Brass Contributor
Feb 19, 2025

Query re: OLE Object

I need to write a query to update an OLEObject field on a form (Forms!ScheduleFormCont!ColorTextBox), based on a table with the same OLEObject field (SCHEDULE.ColorTextBox). I am updating the form fi...
access
Kidd_Ip's avatar
Kidd_Ip
MVP
Feb 20, 2025

Considering this:

 

Update the Table:

UPDATE SCHEDULE 
INNER JOIN ColorsTable ON SCHEDULE.ColorText = ColorsTable.ColorName
SET SCHEDULE.ColorTextBox = [ColorsTable]![Color]
WHERE SCHEDULE.SONo = [Forms]![ScheduleFormCont]![SONo];

 

Update the Form Control:

Private Sub UpdateColorTextBox()
    ' Update the table first
    DoCmd.SetWarnings False
    DoCmd.RunSQL "UPDATE SCHEDULE INNER JOIN ColorsTable ON SCHEDULE.ColorText = ColorsTable.ColorName " & _
                 "SET SCHEDULE.ColorTextBox = [ColorsTable]![Color] " & _
                 "WHERE SCHEDULE.SONo = [Forms]![ScheduleFormCont]![SONo];"
    DoCmd.SetWarnings True
    
    ' Refresh the form control
    [Forms]![ScheduleFormCont]![ColorTextBox].OLETypeAllowed = acOLELinked
    [Forms]![ScheduleFormCont]![ColorTextBox].Class = "Acrobat.Document"
    [Forms]![ScheduleFormCont]![ColorTextBox].SourceDoc = "Path\To\Your\OLEObject"  ' Path to your OLE object file
    [Forms]![ScheduleFormCont]![ColorTextBox].Action = acOLECreateLink
    
    ' Refresh the form to display the updated OLE object
    [Forms]![ScheduleFormCont].Requery
End Sub

 

  • Maxine14062's avatar
    Maxine14062
    Brass Contributor
    Feb 20, 2025

    Thank you sooooooo much!

    • arnel_gp's avatar
      arnel_gp
      Steel Contributor
      Feb 21, 2025

      Using Update query on the same record you are Editing on the form is not a good idea.

      Because the Update query took place when the form is Dirty, therefore MS may complain that you don't have the latest record on the view (or someone ahead of you just saved an update).

       

      What you can do is add code to ColorText BeforeUpdate Event:

      Private Sub ColorText_BeforeUpdate(Cancel As Integer)
    Me.ColorTextbox = DLookup("Color", "ColorsTable", "ColorName = '" & Me.ColorText & "'")
End Sub

       

      or you can Create a Query that will link both tables and use the Query as the Recordsource of your form, in that way you can remove the ColorTextbox (ole object field) and there is no need for any update query. Plus your tables are now Normalized.

      SELECT Schedule.ColorText, Schedule.SONo, ColorsTable.Color
FROM Schedule LEFT JOIN ColorsTable 
ON Schedule.ColorText = ColorsTable.ColorName;

       

Resources