• How to add a record to a subform recordset?

    Home » Forums » AskWoody support » Productivity software by function » MS Access and database help » How to add a record to a subform recordset?


    Hi All,

    I have a command that uses the following:

    If sbfItems.Form.Recordset.RecordCount > 0 Then

    to check for existing records in a subform. I have code to go on and add a record to the underlying table if it = 0 but what I want to do now is be able to add a record to the subform’s recordset and then copy detail down to leave a blank record at the top, which will be then filled in.

    As my records are sorted by record ID this will put my “new detail” in the top position.

    Once I know how to define the recordset I am pretty sure I know how to read/write the info from each field down 1 record using movelast movenext moveprevious etc unless there is a way to copy then write the entire record at once.

    So how do I write the OpenRecordset command? Set rst = CurrentDb.OpenRecordset(sbfItems.Form.Recordset)

    Thanks in advance.


    Viewing 1 reply thread
    • #1499967

      If I understand your situation correctly, you want to add a new record to the underlying table which is being displayed on a subform, but you want to add the new record at the top of the subform, not the bottom. Unfortunately, that is not the way that Microsoft designed forms in general. If it is a continuous form (that is bound), they add at the bottom. If it is a subform that displays one record at a time, then you have to go to the last record, and then to the empty record. Programming around those constraints is a significant challenge, and many different attempts have been made by developers over the years. Most turn out to be less than successful.

      The attempts that I’ve seen that were more successful either go to a completely unbound form, or they limit the subform so it cannot be used to add a record, and then have an unbound pop-up form (or unbound controls in the header of the form) that capture the necessary info to use DAO or ADO to insert a new record in the appropriate table. But a fair bit of programming and testing/debugging/bulletproofing is required to do that and do it well. We’ve taken the approach of training users to use the navigation controls to do what needs to be done, and explain the limitation of Access forms.

    • #1499980

      Thanks Wendel for your insight.

      My situation is: This is part of my Job card procedure.
      A card is raised and items are added to the job using a subform and items table. In a perfect world the user may decide to create a multicard job and so will set an option and enter the first item as a summary line and then the items. What happens is that eg a gearbox comes in and a card is raised to book strip and inspect to, the job is quoted and items of work are added to the job. Depending on the scope, it may turn into a multi card job but may not. If it is, my multicard summary item needs to be the first item.

      I realise that any new record will be written to the bottom of the table so this is what I am looking at now after some more browsing.

      Write the existing recordset for the subform to a temporary table then add a new record to the underlying table and refresh the recordset.
      Open the temp table and .movelast
      Open recordset and .movelast
      write field data from temp to recordset
      .moveprevious on both tables
      repeat until reach top record of temp table
      .movefirst on recordset and clear fields

      top record is now clear for summary

      I hope to use the following combined code from examples off the net (adapted)

      Private Sub TestTemp() 
         On Error GoTo ErrorHandler
         Dim strSQL as String
         Dim strTable as String
         Dim RSC as recordset
         Dim RS as recordset
         strTable = “tblTempTest”
         ‘Delete the table if it exists
         DoCmd.DeleteObject  acTable, strTable
      strSQL = “Select * INTO ” & strTable & ” FROM tblCustomers ” & _   <<<< How do I write the SQL to be from me.sbfItems.Form.Recordset
      "Where CustomerState = 'ILL'"
      Currentdb.Execute strSQL
      Add a record to the underlying table and requery
      set RS = me.sbfItems.Form.Recordset
      Set RSC = currentdb.Openrecordset("tblTempTest)
      While Not RSC.BOF
      Exit Sub
      IF Err.Number = 7874 Then
      Resume Next 'Tried to delete a non-existing table, resume
      End If
      End Sub

      Is this feasible and/or advisable?



    Viewing 1 reply thread
    Reply To: How to add a record to a subform recordset?

    You can use BBCodes to format your content.
    Your account can't use all available BBCodes, they will be stripped before saving.

    Your information: