Continuous main form control to reference specific record on Datasheet subform.

[Darren Bartrup]

Hi all,

I'm having some serious problems figuring out how to get this to work:

I have a database set up to record the work received by a single team and processed by the multiple members of the team. There are also different types of work that may be received and processed (identified by lProcessID) - so the team could receive 10 English Workflows and 20 Scottish Workflows which are recorded separately.

So I have a table called tblTeamData and another called tblIndividualData to record this - plus others with team member names, timesheets, process names, absenteeism, etc.

The fields in question are:

• tblTeamData.lProcessID
• tblTeamData.dDate
• tblTeamData.lReceived

-

• tblIndividualData.dDate
• tblIndividualData.lProcessID
• tblIndividualData.lAllocated
• tblIndividualData.lNotCompleted

I have a query called qryBALANCE_OSBalAtDate which calculates the Brought Forward Balance, Total Received, Total Allocated, Total Not Completed and the Outstanding Balance for each process on a particular date.

The problem:
I have a form called frmTeamData that is set as Continuous Forms and shows all processes for a single day. The user enters the number of items for each process that are received that day by the team.

Due to problems with update-able queries I'm having trouble showing the BF Bal, Total Allocated, Total Not Completed and OS Bal next to the Received amount - these need to change as the total received is entered, but I can't include them in the recordsource for the form as their inclusion like that makes the received amount non-update-able.

So.... I have placed qryBALANCE_OSBalAtDate in a subform in the form footer and set this to Datasheet view - which Access complains about, but accepts.

Now if I tell my B/F Bal control on the main form to look at the B/F Bal control on the subform it works - but I just get the first record for each instance, so all Process ID's on the main form get the value of the first Process ID on the subform. If I link the forms the subform only shows the one record.

I tried this code, which steps through the recordset and sets the value to the last Process ID (and wouldn't guarantee that the two recordsets are in sync even if it did work):

Private Sub txtReceived_AfterUpdate()    Dim rs1 As DAO.Recordset, rs2 As DAO.Recordset
    Set rs1 = Me.frmOSBalance_subform.Form.RecordsetClone
    Set rs2 = Me.Form.RecordsetClone
    rs1.MoveFirst
    rs2.MoveFirst
    Do While Not rs1.EOF
        Me.txtBFBal = rs1.Fields("BFBal")
        rs2.MoveNext
        rs1.MoveNext
    Loop
End Sub

Is there a way someone could explain to pull the correct figures for the correct records from the subform and place it in the main form?

Many thanks in advance for any help offeredn.

 

[Darren Bartrup]

I've figured it out - rather than hold the query in a subform I just load it into memory.

I have the form connected to the update-able query so I can enter the received amounts, I also have unbound controls for the BF Balance, Allocated Amount, Completed Amount and OS Balance.

At the top of the code module for the form I have:

Private rstOSBal As DAO.Recordset

In the module I have built this procedure which fires any time I need to get an up to date recordset (Load Event, After Date Update event and Received Amount Update):

'----------------------------------------------------------------------------------' Procedure : RefreshRecordSet
' Author    : Darren Bartrup-Cook
' Date      : 31/07/2014
' Purpose   : Refreshes the OS Balance recordset after a date or received amount change.
'-----------------------------------------------------------------------------------
Private Sub RefreshRecordSet()


    Dim db As DAO.Database
    Dim prm As DAO.Parameter
    Dim qdfOSBal As DAO.QueryDef
    
    On Error GoTo ERROR_HANDLER


    ''''''''''''''''''''''''''''''''''''''''''''''''''''
    'Ensure any existing recordsets are removed first. '
    ''''''''''''''''''''''''''''''''''''''''''''''''''''
    Set rstOSBal = Nothing
    Set db = CurrentDb
    
    '''''''''''''''''''''''''''''''''''''''''''
    'Resolve the date parameter in the query. '
    '''''''''''''''''''''''''''''''''''''''''''
    Set qdfOSBal = db.QueryDefs("qry_RPT_OSBalToDate")
    For Each prm In qdfOSBal.Parameters
        prm.Value = Eval(prm.Name)
    Next prm
    
    ''''''''''''''''''''''
    'Open the recordset. '
    ''''''''''''''''''''''
    Set rstOSBal = qdfOSBal.OpenRecordset
    
    Set db = Nothing
    Set qdfOSBal = Nothing


    On Error GoTo 0
    Exit Sub


ERROR_HANDLER:
    Select Case Err.Number
        
        Case Else
            MsgBox "Error " & Err.Number & vbCr & _
                " (" & Err.Description & ") in procedure RefreshRecordSet."
            Err.Clear
    End Select
    


End Sub

I have this function to filter the recordset to the relevant process ID and return the value from the required field:

'----------------------------------------------------------------------------------
' Procedure : GetBalanceField
' Author    : Darren Bartrup-Cook
' Date      : 31/07/2014
' Purpose   : Filters the OS Balance recordset and returns the value from the required field.
'-----------------------------------------------------------------------------------
Private Function GetBalanceField(lProcess As Long, sField As String) As Long


    Dim db As DAO.Database
    Dim rst As DAO.Recordset


    On Error GoTo ERROR_HANDLER


    ''''''''''''''''''''''''''''''''''''''''
    'Check the OSBalance query is present. '
    ''''''''''''''''''''''''''''''''''''''''
    If Not rstOSBal Is Nothing Then
    
        ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
        'Filter the OSBalance recordset - there will only be about 20 records, '
        'so not too inefficient.                                               '
        ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
        rstOSBal.Filter = "lProcessID = " & lProcess
        Set rst = rstOSBal.OpenRecordset
        
        ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
        'If a record is returned, capture the value for the specified field. '
        ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
        If Not rst.EOF And Not rst.BOF Then
            GetBalanceField = rst.Fields(sField).Value
        End If
    Else
    
        GetBalanceField = 0
    
    End If


    Set rst = Nothing
    Set db = Nothing


    On Error GoTo 0
    Exit Function


ERROR_HANDLER:
    Select Case Err.Number


        Case Else
            MsgBox "Error " & Err.Number & vbCr & _
                " (" & Err.Description & ") in procedure GetBalanceField."
            Err.Clear
    End Select


End Function

This function is used as the datasource for the BF Balance control on the form:

Public Function GetBFBalance(lProcess As Long) As String


    GetBFBalance = GetBalanceField(lProcess, "BFBal")
    
End Function

This code fires when the received amount is updated, and similar code if the date changes:

'----------------------------------------------------------------------------------
' Procedure : txtRcvd_AfterUpdate
' Author    : Darren Bartrup-Cook
' Date      : 31/07/2014
' Purpose   : Updates the OS Balance after received amount has been changed.
'-----------------------------------------------------------------------------------
Private Sub txtRcvd_AfterUpdate()


    ''''''''''''''''''''''''''''''
    'Force a save of the record. '
    ''''''''''''''''''''''''''''''
    On Error GoTo ERROR_HANDLER


    If Me.Dirty Then Me.Dirty = False
    
    ''''''''''''''''''''''''''
    'Update relevant fields. '
    ''''''''''''''''''''''''''
    RefreshRecordSet
    Me.txtOSBal.Requery


    On Error GoTo 0
    Exit Sub


ERROR_HANDLER:
    Select Case Err.Number
        
        Case Else
            MsgBox "Error " & Err.Number & vbCr & _
                " (" & Err.Description & ") in procedure txtRcvd_AfterUpdate."
            Err.Clear
    End Select
    


End Sub

It's working much faster than DSUM and is returning my calculated values on the same form as my data entry values.

Now, please.... someone make it run faster.