Results 1 to 8 of 8
  1. #1
    Join Date
    Apr 2003
    Location
    Washington DC area
    Posts
    1,770

    Unhappy Unanswered: Updateble recordsets

    I'm getting a message at the bottom of my form stating that" The Recordset is not updatable" when I attempted to change the field from a dropdown. Is there a rule that I dont know about? Does it matter that it's driven by a query? Does it matter that it's in a sub-form??? This is the code:
    SELECT Count(tblPartInstance.PartInstanceID) AS PartCount, tblPartInstance.PartSerialNumber AS PartSerialNumber, tblPartInstance.PartID AS PartID, tblPartInstance.PartDateReceived AS PartDateReceived, tblPartInstance.JobID AS JobID, tblPartInstance.PartCost AS PartCost, tblPartInstance.OrderID AS OrderId, tblPartInstance.PartLocation AS PartLocation, tblPartInstance.PartDateExpected AS PartDateExpected
    FROM tblPartInstance
    GROUP BY tblPartInstance.PartSerialNumber, tblPartInstance.PartID, tblPartInstance.PartDateReceived, tblPartInstance.JobID, tblPartInstance.PartCost, tblPartInstance.OrderID, tblPartInstance.PartLocation, tblPartInstance.PartDateExpected;

  2. #2
    Join Date
    Mar 2003
    Location
    London
    Posts
    40

    Re: Updateble recordsets

    Queries containing GROUP BY produce recordsets which are not updatable.

    Search MSDN (Access) on more info on updatable recordsets.

    Basically any query containing a group by query as one of the source tables in not updatable...

  3. #3
    Join Date
    Apr 2003
    Location
    Washington DC area
    Posts
    1,770

    Updateble recordset

    Is there anyway around this that you know of?

  4. #4
    Join Date
    Mar 2003
    Location
    London
    Posts
    40

    Re: Updateble recordset

    Originally posted by garrydawkins
    Is there anyway around this that you know of?

    Why do you need to update it?

    Do you need to modify info or add records?

    Generally speaking, you can post query results into a table and then use it as your recordset

  5. #5
    Join Date
    Apr 2003
    Location
    Washington DC area
    Posts
    1,770

    Unhappy Re: Updateble recordset

    Correct, I need to add a recordl

    Originally posted by yk58301
    Why do you need to update it?

    Do you need to modify info or add records?

    Generally speaking, you can post query results into a table and then use it as your recordset

  6. #6
    Join Date
    Mar 2003
    Location
    London
    Posts
    40

    Re: Updateble recordset

    Originally posted by garrydawkins
    Correct, I need to add a recordl
    I'all have to make some assumptions about what you actually want to do and about your database design.
    1) PartInstanceId is an autonumber field
    2) You want to be able to add a record to tblPartInstance with, say, default values equal to the currently selected record in the count subform.
    3) The count subform is in datasheet mode. (let's call it sfrmCount)

    I didn't think this code through much, but it should give you the general idea.

    1) Place a button cbtAddNew to your main form
    2) Add another subform to your main form called sfrmAddNew based on tblPartInstance in, say, columnar mode. Set the visible property to false. Add a button cbtUpdate to cfrmAddNew

    3) Associate the following code with cbtAddNew_Click

    dim rstCount as dao.recordset, rstDestination as dao.recordset

    set rstCount=me.sfrmCount.form.recordset
    set rstDestination=me.sfrmAddNew.form.recordset
    with rstDestination
    .AddNew
    !PartSerialNumber = rstCount!PartSerialNumber
    ... other fields here ...
    !PartDateExpected = rstCount!PartDateExpected
    .Update
    .bookmark=.lastmodified
    end with
    me.sfrmAddNew.visible=true
    set rstCount=nothing
    set rstAddDestination = nothing
    4) Associate the following code with cbtUpdate_Click
    Me.Parent.sfrmCount.SetFocus
    Me.Visible = False
    Me.Parent.sfrmCount.Requery


    Please revert if any more questions

  7. #7
    Join Date
    Apr 2003
    Location
    Washington DC area
    Posts
    1,770

    Angry That's a correct assumption. Here the code.

    'PURCHASE ORDER --- PURCHASE ORDER
    Option Compare Database
    Option Explicit

    Dim NewOrder As Boolean

    Private Sub Form_AfterDelConfirm(Status As Integer)
    Dim rs As Recordset

    frmSubPurchaseOrder.Requery
    If Status = False And DecrementOrderNumber = True Then
    Set rs = CurrentDb.OpenRecordset("OrderNumber", dbOpenDynaset)
    rs.Edit
    rs(0) = rs(0) - 1
    rs.Update
    rs.Close
    End If
    DecrementOrderNumber = False

    End Sub


    Private Sub Form_BeforeInsert(Cancel As Integer)
    Dim rs As DAO.Recordset, holder As Integer, holder_string As String, ThisYear As String, OrdNum As String

    Set rs = CurrentDb.OpenRecordset("tblOrderNumber", dbOpenDynaset)
    holder = 10000 + rs(0)
    holder_string = holder
    holder_string = Right(holder_string, 4)
    ThisYear = Right((DatePart("yyyy", Now)), 2)
    OrdNum = "PT2" & ThisYear & holder_string
    rs.Edit
    rs(0) = rs(0) + 1
    rs.Update
    rs.Close

    txtOrderNumber = OrdNum
    End Sub

    Private Sub Form_Current()
    If txtOrderPrinted = False Then
    NewOrder = True
    frmSubPurchaseOrder.Form.AllowDeletions = False
    frmSubPurchaseOrder.Form.txtPartCost.Locked = False
    Else
    NewOrder = False
    frmSubPurchaseOrder.Form.AllowDeletions = False
    frmSubPurchaseOrder.Form.txtPartCost.Locked = True
    End If
    frmSubPurchaseOrder.Requery
    cboPartInstance = Null
    txtQuantity = Null
    txtUnitCost = Null
    txtOrderETADate = Null
    End Sub


    Private Sub Command4_Click()
    MsgBox ("hi")
    End Sub


    Sub calorderDate_click()
    txtOrderDate = calOrderDate.Value
    txtOrderDate.SetFocus
    calOrderDate.Visible = False
    End Sub



    Private Sub Command6_Click()
    calOrderDate.Visible = True
    calOrderDate.SetFocus
    End Sub

    Private Sub calOrderDate_LostFocus()
    frmSubPurchaseOrder.Visible = True

    End Sub

    Private Sub cboPartInstance_NotInList(NewData As String, Response As Integer)

    If vbNo = MsgBox(NewData & " is not an existing product number. Do you wish to add it?", vbYesNo + vbQuestion) Then
    Response = acDataErrDisplay
    Else
    DoCmd.OpenForm "frmNewPart", acNormal, , , acFormAdd, acDialog, NewData
    Response = acDataErrAdded
    Me.frmSubPurchaseOrder.Form.cboPartID.Requery
    End If

    End Sub

    Private Sub cboVendor_AfterUpdate()
    cboPartInstance.Requery
    End Sub

    Private Sub cboVendor_NotInList(NewData As String, Response As Integer)
    If vbNo = MsgBox(NewData & " is not a current vendor. Would you like to add it?", vbYesNo + vbQuestion) Then
    Response = acDataErrDisplay
    Else
    DoCmd.OpenForm "frmNewVendor", acNormal, , , acFormAdd, acDialog, NewData
    'CurrentDb.Execute "insert into tblVendor (VendorName) values ('" & NewData & "');"
    Response = acDataErrAdded

    End If

    End Sub

    Private Sub cmdAddPart_Click()
    Dim Counter As Integer, QryString As String, rs As DAO.Recordset
    If NewOrder = True Then
    If (Not IsNull(cboPartInstance)) And (Not IsNull(txtQuantity)) And _
    (Not IsNull(txtOrderNumber)) And (Not IsNull(txtUnitCost)) And _
    (Not IsNull(txtOrderETADate)) And (Not IsNull(txtOrderDate)) Then
    'QryString = "insert into tblpartinstance(OrderID, PartID) values (" & Me!txtOrderID & ", " & Me!cboPartInstance & ");"
    frmSubPurchaseOrder.SetFocus
    For Counter = 1 To txtQuantity
    Set rs = CurrentDb.OpenRecordset("tblpartinstance", dbOpenDynaset)
    rs.AddNew
    rs("PartID") = Me!cboPartInstance
    rs("PartLocation") = constONORDER
    rs("OrderID") = Me!txtOrderID
    rs("PartCost") = txtUnitCost
    If Not IsNull(Me!txtOrderETADate) Then rs("PartDateExpected") = txtOrderETADate
    rs.Update
    rs.Close

    Next Counter
    cboPartInstance.SetFocus
    cboPartInstance = Null
    txtQuantity = Null
    txtUnitCost = Null
    txtOrderETADate = Null
    frmSubPurchaseOrder.Requery
    Else
    MsgBox ("You haven't entered all necessary data to add these to the order.")
    End If
    Else
    MsgBox ("You cannot add parts to previously created Purchase Orders. You must create a new order.")
    End If
    End Sub

    Private Sub cmdClose_Click()
    Dim strMSG As String, rs As DAO.Recordset, rst As DAO.Recordset

    strMSG = "SELECT * FROM qryPartDates WHERE OrderNumber = '" & Me!OrderNumber _
    & "' AND Location = 'On Order' AND (not isnull(PartDateReceived))"
    Set rs = CurrentDb.OpenRecordset(strMSG, dbOpenDynaset)
    If rs.RecordCount > 0 Then
    MsgBox ("The location of one or more parts needs to be updated to reflect its receipt")
    Else
    strMSG = "SELECT * FROM qryPartDates WHERE OrderNumber = '" & Me!OrderNumber _
    & "' AND Location <> 'On Order' "
    Set rst = CurrentDb.OpenRecordset(strMSG, dbOpenDynaset)
    If rst.RecordCount = 0 Then
    If vbNo = MsgBox("Are any of these parts that are not assigned to work orders below being purchased for open work orders?", vbYesNo + vbQuestion) Then
    DoCmd.Close acForm, "frmPurchaseOrder"
    Else
    MsgBox ("Choose the work order associated with the appropriate part before closing!")
    End If
    Else
    DoCmd.Close acForm, "frmPurchaseOrder"
    End If
    rst.Close
    End If
    rs.Close
    'DoCmd.OpenForm "frmMainMenu"

    End Sub


    Private Sub cmdPrint_Click()
    txtOrderPrinted = True
    Form_Current
    DoCmd.OpenReport "rptPurchaseOrder", acViewPreview
    End Sub

    Private Sub cmdReceipt_Click()
    Dim strMSG As String, rs As DAO.Recordset

    strMSG = "SELECT * FROM tblpartinstance WHERE OrderID = " & Me!txtOrderID & " AND (NOT isnull(PartDateReceived))"

    Set rs = CurrentDb.OpenRecordset(strMSG)
    If rs.RecordCount > 0 Then DoCmd.OpenReport "rptReceivingReport", acViewPreview Else: _
    MsgBox ("No parts have been received on this order")
    rs.Close
    End Sub


    Private Sub Form_Activate()
    'DoCmd.GoToRecord record:=acNewRec
    End Sub



    Private Sub Form_Open(Cancel As Integer)
    'DoCmd.Maximize
    Me.OrderByOn = True
    End Sub


    Private Sub Label49_Click()
    txtOrderNumber.Locked = False
    End Sub

    Private Sub Label9_Click()
    txtOrderDate.SetFocus
    txtOrderNumber.Locked = True
    txtOrderNumber.SetFocus
    End Sub


    Private Sub lblPurchaseOrder_Click()
    calOrderDate.Visible = True
    calOrderDate.Today
    calOrderDate.SetFocus
    frmSubPurchaseOrder.Visible = False

    End Sub



    Private Sub txtOrderETADate_AfterUpdate()
    Dim strSQL As String

    'strSQL = "UPDATE PartInstance_new SET PartInstance_new.PartDateExpected = #" _
    ' & Me!orderetadate & "# WHERE PartInstance_new.OrderID = " & Me!OrderID & ";"
    'Me!frmSubPurchaseOrder.SetFocus
    'MsgBox strSQL
    'If Not IsNull(Me!orderetadate) Then CurrentDb.Execute strSQL
    'Me!txtOrderReceivedDate.SetFocus
    'Me!frmSubPurchaseOrder.Requery

    End Sub



    Private Sub txtOrderReceivedDate_AfterUpdate()
    Dim strSQL As String

    strSQL = "UPDATE tblpartinstance SET tblpartinstance.PartDateReceived = #" _
    & Me!OrderReceivedDate & "# WHERE tblpartinstance.OrderID = " & Me!OrderID & ";"
    Me!frmSubPurchaseOrder.SetFocus
    CurrentDb.Execute strSQL
    Me!txtOrderReceivedDate.SetFocus
    Me!frmSubPurchaseOrder.Requery

    End Sub

    Private Sub cmdDelete_Click()
    Dim OrdNum As String, rs As Recordset, OrdNumInt As Integer
    Dim strSQL As String

    On Error GoTo Err_cmdDelete_Click
    If txtOrderPrinted = False Then
    Set rs = CurrentDb.OpenRecordset("OrderNumber", dbOpenDynaset)
    OrdNum = Right(txtOrderNumber, 4)
    OrdNumInt = OrdNum
    If OrdNumInt = rs(0) - 1 Then DecrementOrderNumber = True Else: DecrementOrderNumber = False
    rs.Close


    DoCmd.DoMenuItem acFormBar, acEditMenu, 8, , acMenuVer70
    DoCmd.DoMenuItem acFormBar, acEditMenu, 6, , acMenuVer70

    Else



    strSQL = "UPDATE tblpartinstance SET tblpartinstance.PartLocation = " _
    & constCANCELLED & ", tblpartinstance.PartDateReceived = Null WHERE tblpartinstance.OrderID = " & Me!OrderID & ";"
    Me!frmSubPurchaseOrder.SetFocus
    CurrentDb.Execute strSQL
    Me!cmdDelete.SetFocus
    Me!frmSubPurchaseOrder.Requery

    End If


    Exit_cmdDelete_Click:
    Exit Sub

    Err_cmdDelete_Click:
    MsgBox Err.Description
    Resume Exit_cmdDelete_Click
    rs.Close

    End Sub

  8. #8
    Join Date
    Mar 2003
    Location
    London
    Posts
    40

    Re: That's a correct assumption. Here the code.

    Garry,

    Unfortunately, I haven't got the time to go through your entire code.

    You can use the code I posted as a template for your future develpments.

    Regards,

    YK

Posting Permissions

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