Results 1 to 4 of 4
  1. #1
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    New England (Connecticut)
    Posts
    200
    Thanks
    1
    Thanked 0 Times in 0 Posts

    updating ole with sql recordset (2003 office)

    Hi all,
    I am lost in confusion here. I am trying to update an ole object in a table using the following:

    Sub ProcesstableTARReport()

    Dim dbs As dao.Database
    Dim rst As dao.Recordset
    Dim Counter As Integer
    Dim mySQL As String

    On Error GoTo ErrHandler

    ' Reference to database
    Set dbs = CurrentDb
    ' Open recordset from table

    mySQL = "SELECT Pricing.Modified_Pricing_ID, Pricing.[Technical Report] from Pricing WHERE Pricing.Modified_Pricing_ID= '" & [Forms]![Splash Screen]![vLastPricing] & "'"

    Set rst = dbs.OpenRecordset(mySQL, dbOpenDynaset)



    ' mySQL = "UPDATE Pricing " & _
    ' "SET Pricing.[Desk_TAR_Complete] = True " & _
    ' "WHERE Pricing.Pricing_ID In (" & vPricingPackage & ")"

    ' DoCmd.RunSQL mySQL 'for the table named pricing to update TAR ID COde

    ' find number of records
    Counter = rst.RecordCount

    ' loop through records of imported table
    Do While Not rst.EOF
    ' loop through fields (starting with 3rd field)
    ' originally had For i = 2 To rstTemp.Fields.Count
    For i = 0 To Counter
    DoCmd.OpenForm "frmSelectFileXP", acNormal, , , , acDialog
    oleTechnicalReport.Class = "Excel.Sheet" ' Set class name.
    ' Specify type of object.
    oleTechnicalReport.OLETypeAllowed = acOLELinked
    ' Specify source file.
    oleTechnicalReport.SourceDoc = Forms![frmSelectFileXP]![vTARReportName]
    ' Specify data to create link to.
    'TechnicalReport.SourceItem = "R1C1:R25C15"
    ' Create linked object.
    oleTechnicalReport.Action = acOLECreateLink
    ' Adjust control size.
    oleTechnicalReport.SizeMode = acOLESizeZoom
    Next i
    rst.MoveNext
    Loop

    ExitHandler:
    ' Clean up
    On Error Resume Next
    rst.Close
    Set rst = Nothing
    Set dbs = Nothing
    Exit Sub

    ErrHandler:
    MsgBox Err.Description, vbExclamation
    Resume ExitHandler
    End Sub



    In this case, the Pricing Table is a linked table, the object is an excel spreadsheet on our network. The path of the spreadsheet is captured in the line ...DoCmd.OpenForm "frmSelectFileXP", acNormal, , , , acDialog ...

    Right now as the code exists, I am getting an object required error. This code worked when I was updating the table through a form and updating only one record at a time. I am trying to use the sql statement to generate a recordset of the records I want to update and then scroll through the records udating them in one shot.

    I put this code togehter using peices of code that worked individually before when applying the logic to a single reocrd on a form. Don't know where to go from here.

    Can someone point me in the right direction.

    Thanks
    Kevin

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: updating ole with sql recordset (2003 office)

    1) You should remove the lines

    For i = 0 To Counter

    and

    Next i

    You don't do anything with i and I see no reason to loop through the fields of the recordset (the upper bound should have been Counter - 1, by the way: the fields of a recordset start numbering at 0, not at 1).

    2) You haven't defined oleTechnicalReport anywhere in the code. Do you mean the Technical Report field of the recordset?

    3) You cannot do it this way. Properties such as Action belong to OLE controls on a form, not to OLE fields in a recordset. So unless you want to get *very* technical, you have to set the OLE field thorugh a form bound to the field.

  3. #3
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    New England (Connecticut)
    Posts
    200
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: updating ole with sql recordset (2003 office)

    thanks Hans,

    Your comments are appreciated. I zoomed in on # 3 real quick. Is there anyway to set the OLE to multiple records at the same time. In the current environment, I can only display one record on the form. I have been able to set the ole using the method in # 3. I was hoping to be able to set the same object to many records at the same time. Is there a query I can run in the back ground after setting the first one and update the other records based on the first?

    To give you an idea of what I am trying to accomplish is that when a record is processed, it might be processed by itself or it may be grouped with other records and processesd. If done by itself I am trying to assign the ole object to that record (Already done that). It is this grouped process that has messed things up. When a group is processed, the same ole object applies to the entire group.

    Any suggestions?

    Thank You
    Kevin
    Kevin

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: updating ole with sql recordset (2003 office)

    You'll have to move through the records of the form, for example by using

    RunCommand acCmdRecordsGoToNext

    in a loop.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •