Class update (1 Viewer)

[drisconsult]

I have completed a school database for schools in Uganda, Tanzania and Kenya. The problem I am having is updating a class after a class has moved on to the new academic year. At the moment I am having to ask teacher's to do this manually. I'm afraid that my VB is not up to this task.

An example would be class 1A, which has 40 Students. In the next academic year this class would become 2A and so forth for all other classes.
Terence Driscoll
London

 

[Ranman256]

it is, but vb not required.
your database should have a year field on the Academic Year table.
each year would require a new student roster, (that can be copied from prev year)

 

[Pat Hartman]

I'm posting code that shows how to do this. you will obviously have to translate it to your needs. This example is a three table hierarchy. The top level is done by manipulating the form's recordset clone, the middle layer opens a separate recordset, and the bottom layer can be done using an append query. This example gives you the pattern you need regardless of how many layers you have. If you have only two, you use the top which is the recordset clone and the bottom which is the append query. I you have 4, you use the top using the recordset clone, the second using a dao recordset, the third using a dao recordset and the bottom using the append query. So the top is always the recordset clone method and the bottom is always the append query and the layers in the middle are the dao recordset method.

Private Sub cmdCopyOrder_Click()
On Error GoTo Err_Handler
'Purpose:   Duplicate the main form record and related records in the subform.
    Dim str2Sql             As String
    Dim strSQL              As String   
    Dim NewOrderID  As Long       'Primary key value of the new record.
    Dim OldOrderID    As Long       'Primary key value of the old record.
    Dim NewOrderDetailID As Long
    Dim db                      As DAO.Database
    Dim qd                      As DAO.QueryDef
    Dim FromQD          As DAO.QueryDef
    Dim FromRS           As DAO.Recordset
    Dim ToTD                As DAO.TableDef
    Dim ToRS                As DAO.Recordset

    'Save any edits first
    If Me.Dirty Then
        Me.Dirty = False
    End If

    'Make sure there is a record to duplicate.
    If Me.NewRecord Then
        MsgBox "Select the record to duplicate."
        Exit Sub
    End If    

    'Duplicate the main record: add to form's recordset clone.
    OldOrderID = Me.OrderID
    With Me.RecordsetClone
        .AddNew
        !CustID = Me.cboCustID
        !JobName = Me.JobName
        !Notes = Me.Notes
        !TermsID = Me.TermsID
        !ShippingID = Me.ShippingID
        !CustConID = Me.cboCustConID
        !CustLocID = Me.cboCustLocID
        !OrderNumber = Nz(DMax("OrderNumber", "tblOrders"), 0) + 1
        !OrderDate = Date
''        !Expires = Date + 30
        .Update

        'Save the primary key value, to use as the foreign key for the related records.
        .Bookmark = .LastModified
        NewOrderID = !OrderID
        'Display the new duplicate.
        Me.Bookmark = .LastModified      ' move form to position on the newly created record.
    End With

        'Duplicate the related Item records using DAO recordset.  Append Accessories with  append query inside loop
        Set db = CurrentDb()
        Set ToTD = db!tblOrderDetails
        Set ToRS = ToTD.OpenRecordset
        Set FromQD = db.QueryDefs!qOrderCopyDetails
                FromQD.Parameters!EnterOldOrderID = OldOrderID
        Set FromRS = FromQD.OpenRecordset(dbOpenDynaset, dbSeeChanges)
        With FromRS
            .MoveFirst
            Do Until .EOF = True
                ToRS.AddNew
                ToRS!ItemNo = !ItemNo
                ToRS!EstID = !EstID
                ToRS!ModelNo = !ModelNo
                ToRS!Description = !Description
                ToRS!Qty = !Qty
                ToRS!Price = !Price
                ToRS!AccessPrice = !AccessPrice
                ToRS!OrderID = NewOrderID
                NewOrderDetailID = ToRS!OrderDetailID
                ToRS.Update
                
                
                ''  copy accessories
                Set qd = db.QueryDefs!qCopyAppendOrderAcc
                    qd.Parameters!EnterOldOrderDetailID = !OrderDetailID
                    qd.Parameters!EnterNewOrderDetailID = NewOrderDetailID
                    qd.Execute dbFailOnError
            
                .MoveNext
            Loop
        End With
        Me.SfrmOrderDetails.Requery
        ToRS.Close
        FromRS.Close
        Set ToRS = Nothing
        Set FromRS = Nothing
        Set db = Nothing

Exit_Handler:
    Exit Sub

Err_Handler:
    Select Case Err.Number
        Case 3021, 2501   ' update cancelled
            Resume Exit_Handler
        Case Else
            MsgBox Err.Number & "--" & Err.Description
            Resume Exit_Handler
    End Select

End Sub

 

[drisconsult]

Thank you for your reply, but unfortunately that was much too involved for me. At 86 years old my brain is not working ass well as it did. However I have found a solution based on one of the Forums experts who referred to my problem previously by using a ListBox. I have included a screenshot to show my solution based on the other experts solution and it works well enough.
May I thank you all for your patience. Terence Driscoll

 

[Pat Hartman]

You're welcome.

 

[arnelgp]

You're welcome.