Results 1 to 9 of 9
  1. #1
    Star Lounger
    Join Date
    May 2006
    Posts
    51
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Add record to subform (2003)

    I have the following code which is working and adds records to the query or to the table that is underlying a subform for OrderDetails.
    There is a button on Orders that calls up a window to select parts from a combo box. This form has a button to "Add Item". The code that follows is from that button.
    ----------------------------
    Private Sub cmdAddOrderItem_Click()

    'Add Record to frmQuoteDetails
    Dim dbs As DAO.Database
    Dim rstOrderDetails As DAO.Recordset

    Set dbs = CurrentDb
    Set rstOrderDetails = dbs.OpenRecordset("qryOrderDetails", dbOpenDynaset)

    With rstOrderDetails
    .AddNew

    'Set field values
    .Fields("Line") = 14
    .Fields("OrderID") = 2
    .Fields("PartNumber") = cboPartNumber
    .Fields("Description") = txtDescription
    .Fields("UM") = txtUM
    .Fields("UnitCost") = curUnitCost
    .Fields("AddedBy") = Environ("UserName")
    .Update
    End With

    rstOrderDetails.Close
    Set rstOrderDetails = Nothing
    Set dbs = Nothing

    End Sub
    ------------------------------------
    I now have two questions:

    1. After clicking on the "Add Item" button, the record does show up in tblOrderDetails. But the open frmOrders and fsubOrderDetails do not show the new records. I tried doing a Refresh, but no success, I need to advance and retreat with navigation bar on frmOrders. What do I need to add to make the Refresh work immediately when record is added?

    2. I want to add the records as if it they were being entered directly in the subform since there is code in the fsubFormOrderDetails that performs other tasks when done manually.
    How can I modify the code to do that?

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

    Re: Add record to subform (2003)

    1) Instead of Refresh, use Requery on the subform.

    2) That depends on what the code does. Can you provide more info?

  3. #3
    Star Lounger
    Join Date
    May 2006
    Posts
    51
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Add record to subform (2003)

    Made some changes in response to your reply. You forced me to look at Events programming for fsubOrderDetails. Thanks for that.

    1. Changed to make recordset from table vs. query. tblOrderDetails only holds some PartNumber fields, some of other fields are filled in with qryOrderDetails underlying the fsubOrderDetails. So those fields cannot be passed to tblOrderDetails and do not need to be added to tblOrderDetails.
    2. OrderID actually comes from frmOrders, not manually entered as shown in earlier posting.
    3. Line comes from frmOrders also, not manually entered.
    4. I think that Events programming that were still leftover will be taken care of when doing Requery, but I have been unable to come up with the correct syntax to do that.
    I am doing Requery on fsubOrderDetails first to update some SubTotal fields and LineCount, and the Requery on the frmOrders after that to update the SubTotalsand Line value from the subform.
    Current code is shown below.
    ---------------------------------------------------------------------
    Private Sub cmdAddOrderItem_Click()

    'Add Record to frmOrderDetails
    Dim dbs As DAO.Database
    Dim rstOrderDetails As DAO.Recordset

    Set dbs = CurrentDb
    Set rstOrderDetails = dbs.OpenRecordset("tblOrderDetails", dbOpenDynaset)

    With rstOrderDetails
    .AddNew

    'Set field values
    .Fields("OrderID") = (Forms![frmOrders]![txtOrderID])
    .Fields("Line") = (Forms![frmQuotes]![txtLineCount]) + 1
    .Fields("PartNumber") = cboPartNumber
    .Fields("Quantity") = numQuantity
    .Fields("UnitCost") = curUnitCost
    .Fields("AddedBy") = Environ("UserName")
    .Update
    End With

    rstOrderDetails.Close
    Set rstOrderDetails = Nothing
    Set dbs = Nothing

    'Update forms with new records
    'None of the four lines here are working. Cannot work out syntax to do the Requery
    'Forms("fsubOrderDetails").Requery
    'Forms("frmOrder").Requery
    'Forms.Application.Forms("fsubOrderDetails").Reque ry
    'Forms.Application.Forms("frmOrders").Requery

    End Sub

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

    Re: Add record to subform (2003)

    Subforms are not part of the Forms collection, only main forms are. You must always refer to a subform as a control on the main form. The name of the subform as a control on the main form is not necessarily the same as the name of the subform in the database window. To see the control name, open the main form in design view and click once on the subform, then look at the Object dropdown in the Formatting toolbar or at the Properties window.

    Moreover, it should not be necessary to requery the main form. If you have calculated controls, you can use Recalc to update their values.

    Try:

    Me.fsubOrderDetails.Requery
    Me.Recalc

    "Me" refers to the form containing the code, in this case the main form.

  5. #5
    Star Lounger
    Join Date
    May 2006
    Posts
    51
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Add record to subform (2003)

    Thanks again HansV. It appears that I only need to do a Recalc on the frmOrders.

    Here is the final code that appears to be working:
    -----------------------------------------
    'Add Record to frmOrderDetails
    Dim dbs As DAO.Database
    Dim rstOrderDetails As DAO.Recordset

    Set dbs = CurrentDb
    Set rstOrderDetails = dbs.OpenRecordset("tblOrderDetails", dbOpenDynaset)

    With rstOrderDetails
    .AddNew

    'Set field values
    .Fields("Line") = (Forms![frmQuotes]![txtLineCount]) + 1
    .Fields("QuoteID") = (Forms![frmQuotes]![txtQuoteID])
    .Fields("PartNumber") = cboPartNumber
    .Fields("Quantity") = numQuantity
    .Fields("UnitCost") = curUnitCost
    .Fields("AddedBy") = Environ("UserName")

    .Update
    End With

    rstOrderDetails.Close
    Set rstOrderDetails = Nothing
    Set dbs = Nothing

    'Update forms with new records
    Forms("frmOrders").Recalc
    -----------------------------------------

    Thanks again, 1 down, at least 2 more to go.

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

    Re: Add record to subform (2003)

    If this code runs on frmOrders, the instruction

    Forms("frmOrders").Recalc

    can be replaced with

    Me.Recalc

    This has two advantages:
    1) Access doesn't have to look at the Forms collection, so the code is slightly more efficient.
    2) If you ever decide to rename the form, Me will still refer to it, but Forms("frmOrders") will not.

  7. #7
    Star Lounger
    Join Date
    May 2006
    Posts
    51
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Add record to subform (2003)

    Thanks HansV, but this code does not run on frmOrders.

    There is a button on frmOrders that opens a new form frmAddItems. This form has an "Add Item" cmd button that runs this code.
    That is why Me. was not used.

    And thanks for the reminder about renaming. Really an irritating issue in Access.

  8. #8
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,436
    Thanks
    1
    Thanked 34 Times in 34 Posts

    Re: Add record to subform (2003)

    As Hans noted, you should be using Requery rather than Refresh in this situation. But it is important you understand the difference.

    Refresh merely gets the most recent data for every record in your recordset. So if any records were changed, you would see the most recent data. If a record had been deleted, you will still see a line for that record in your recordset, but would see #Deleted#. But, you won't see any new records that meet your query criteria!

    A Requery actually re-runs the query that produced the recordset. Therefore it not only gets the latest data, but also gets new records and won't include deleted records.
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

  9. #9
    Star Lounger
    Join Date
    May 2006
    Posts
    51
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Add record to subform (2003)

    Thank you for your explanation Mark.

    But note, that my final code is actually using ReCalc.
    Since I am only adding records, they are showing up instantly.
    And I am using ReCalc to update the subtotals on the subform that are also recalculated on the Orders form.
    It all appears good so far, but I will keep my eyes opened as more testing is done before release.

Posting Permissions

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