Results 1 to 3 of 3
  1. #1
    3 Star Lounger
    Join Date
    Nov 2001
    Location
    Brisbane, Queensland, Australia
    Posts
    330
    Thanks
    10
    Thanked 0 Times in 0 Posts

    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.

    Allan
    "Heading for the deep end"

  2. #2
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts
    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.
    Wendell

  3. #3
    3 Star Lounger
    Join Date
    Nov 2001
    Location
    Brisbane, Queensland, Australia
    Posts
    330
    Thanks
    10
    Thanked 0 Times in 0 Posts
    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)

    Code:
    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)
    RS.MoveLast
    RSC.MoveLast
    While Not RSC.BOF
    RS.edit
    RS![Field1]=RSC![Field1]
    RS![Field2]=RSC![Field2]
    etc.
    RS.Update
    RS.MovePrevious
    RSC.MovePrevious
    WEnd
    RS.Close
    RSC.Close
    
    Exit Sub
    ErrorHandler:
    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?

    Regards,

    Allan
    "Heading for the deep end"

Posting Permissions

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