Results 1 to 6 of 6
  1. #1
    Join Date
    Sep 2009
    Posts
    23

    Unanswered: Update the current record from Query Result

    Please refer to my code below,Base on query result, I wanna update the "TCallSheet.Posted" field into true. Anyone pls help...

    strCallSheet = "SELECT TCallSheet.CustName as QCustName, TCust.SalesTax as QSalesTax, TCust.Calldate as QCalldate, TCust.PONo as QPONo," & _
    " TCust.CallDay, TCallDay.Sequence, TCallSheet.SalesRep as QSalesRep, TCallSheet.Arranging, TCallSheet.Picked as QPicked," & _
    " TCallSheet.DeptNo as QDeptNo, TCallSheet.PartNo as QPartNo, Titems.JobberPrice as QJobberPrice, TCallSheet.Posted as QPosted," & _
    " TCust.CustAdd as QCustAdd, TCust.CustAdd2 as QCustAdd2, TCust.CustCity as QCity, TCust.CustState as QState, TCust.CustZip as QCustZip," & _
    " TCust.CustShippingAdd as QShippingAdd, TCust.CustShippingAdd2 as QCustShippingAdd2, TCust.CustShippingCity as QCustShippingCity, " & _
    " TCust.CustShippingState as QCustShippingState, TCust.CustShippingZip as QCustShippingZip " & _
    " FROM ((TCust INNER JOIN TCallDay ON TCust.CallDay = TCallDay.CallDay)" & _
    " INNER JOIN TCallSheet ON TCust.CustName = TCallSheet.CustName) INNER JOIN Titems ON" & _
    " (TCallSheet.DeptNo = Titems.DeptNo) AND (TCallSheet.PartNo = Titems.PartNo)" & _
    " WHERE (((TCallSheet.Posted) = False)) ORDER BY TCallDay.Sequence, TCallSheet.SalesRep, TCallSheet.Arranging;

    Set db = CurrentDb
    Set rs = db.OpenRecordset(strCallSheet, dbOpenSnapshot)
    If rs.EOF Then
    MsgBox "No Call Sheet to be posted!!!"
    Else
    Do While Not rs.EOF
    'set the "TCallSheet.Posted" to true
    rs.MoveNext
    Loop
    EndIf

  2. #2
    Join Date
    Mar 2009
    Posts
    5,441
    Provided Answers: 14
    I would use an UPDATE SQL statement to do this, not looping through a Recordset. Otherwise:
    Code:
    Function Update_TPosted()
    
        Set DB = CurrentDb
        Set rs = DB.OpenRecordset(strCallSheet, dbOpenSnapshot)
        If rs.EOF Then
            MsgBox "No Call Sheet to be posted!!!"
        Else
            Do While Not rs.EOF
                !QPosted = True
            rs.MoveNext
            Loop
        End If
      
    End Function
    And for that purpose strCallSheet can be shortened at least to:
    Code:
        strCallSheet = "SELECT TCallSheet.Posted as QPosted" & _
                       " FROM ((TCust INNER JOIN TCallDay ON TCust.CallDay = TCallDay.CallDay)" & _
                       " INNER JOIN TCallSheet ON TCust.CustName = TCallSheet.CustName) INNER JOIN Titems ON" & _
                       " (TCallSheet.DeptNo = Titems.DeptNo) AND (TCallSheet.PartNo = Titems.PartNo)" & _
                       " WHERE (((TCallSheet.Posted) = False));"
    Have a nice day!

  3. #3
    Join Date
    Sep 2009
    Posts
    23
    Thnx Sinndho.

    I tried the " !QPosted = True " but it returns an error : Run-time error '3027': Cannot update. Database or object is read-only.

    I can't shortened the strCallSheet coz I am using the other fields for calculations. After calculations,I have to update the field posted=true which means I'm done with the calculation of that record.

  4. #4
    Join Date
    Mar 2009
    Posts
    5,441
    Provided Answers: 14
    Sorry, I posted my answer too quickly and without re-reading it. It should have been:
    Code:
            Do While Not rs.EOF
                rs.Edit
                rs!QPosted = True
                rs.Update
            rs.MoveNext
            Loop
    But it's possible that the query is not updatable anyway. In that case you'll have to extract a unique identifier for TCallSheet from the query and perform the update directly on the table (or on a simpler updatable query).

    As for the need to have strCallSheet be complex, nothing prevents you from using two queries.
    Have a nice day!

  5. #5
    Join Date
    Sep 2009
    Posts
    23
    " ---In that case you'll have to extract a unique identifier for TCallSheet from the query and perform the update directly on the table (or on a simpler updatable query).---"...Thnx Sinddho, It works the way I wanted it to work and this is the solution I thought too.

    DoCmd . RunSQL "UPDATE TCallSheet SET TCallSheet.Posted = True WHERE (((TCallSheet.Arranging)= " & MeArranging & "));"

    -------
    Do While Not rs.EOF
    rs.Edit
    rs!QPosted = True
    rs.Update
    rs.MoveNext
    Loop


    I tried the code above but it returns an error : Run-time '3251': Operation is not supported for this type of object.

    "---But it's possible that the query is not updatable anyway.---".....Just for the heck of it, In what way to make the query updatable and use the code you suggested above?

    Many thnx once again...

  6. #6
    Join Date
    Mar 2009
    Posts
    5,441
    Provided Answers: 14
    Generally speaking a column would be updatable if its located into the "One" side of a "One-to-Many" query. To update a column on the "Many" side, the query must include the Identity column of the table on that side.
    Have a nice day!

Posting Permissions

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