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 field from another table also with an OLEObject field (ColorsTable!Color). The OLEObject fields are:
SCHEDULE!ColorTextBox

ColorsTable!Color

And

[Forms]![ScheduleFormCont]![ColorTextBox] listed on the form as a Bound Object Frame

 

I then need the form to update with the new OLE Object information. I am using code:

DoCmd.SetWarnings False

DoCmd.OpenQuery "AddColor2"

DoCmd.SetWarnings True

 

The AddColor2 query is not working:

UPDATE SCHEDULE INNER JOIN ColorsTable ON SCHEDULE.ColorText = ColorsTable.ColorName

SET SCHEDULE.ColorTextBox = [ColorsTable]![Color],

[Forms]![ScheduleFormCont]![ColorTextBox] = [ColorsTable]![Color]

WHERE (((SCHEDULE.SONo)=[Forms]![ScheduleFormCont]![SONo]));

 

SCHEDULE!ColorText and ColorsTable!ColorName are ShortText fields containing the same value.

 

Can you help??



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

     

      • 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