Recordset in datasheet subform not updating when data is updated in formview subform

[Sandra Walsh]

Hello All -

I have a main form called f_MainTripForm_ Admin with a split view. The main form has fields relating to a trip.

The main form has 2 sub forms. The subforms have fields related to activities that occured on a trip, eg meetings. One trip can have many meetings assoicated with it:

SubForm 1 - f_ActivityDetai ls_SFC: Form with tab control view of activity-related fields for one Activity
SubForm 2 - f_ActivityDetai ls_Datasheet: datasheet view showing a list of all Activities for that trip.

The OnCurrent event for the ActivityDetails _Datasheet calls the code below.

The issue I have is that the recordset in f_ActivityDetai ls_Datasheet does not update when I edit, delete or add an Activity in ActivityDetails _Admin.

f_ActivityDetai ls_Datasheet has the following properties:
DataEntry = No
Allow Additions, Deletions, Edits and Filters = Yes

I dont need to be able to update any data in f_ActivityDetai ls_Datasheet - I just need it to reflect the changes made in ActivityDetails _Admin when I update a field or select a different record (row) in ActivityDetails _Datasheet.

I think this might have to do with the definition of the recordset is fixed somehow and I need to clear it.

Is using

Set rs = Nothing

the correct approach?

FYI -
- when the cursor is in a control in either ActivityDetails _Datasheet or ActivityDetails _Admin and I run RefreshAll from the ribbon, the data in ActivityDetails _Datasheet does not update;
- when I move to a new trip then come back to the original trip, or do RefreshAll when my cursor is in the current trip record, the udpates show in the datasheet.

Thanks!
Sandra

Sub FilterActivities()
           
Debug.Print "Start FilterActivities()"
            
Debug.Print "     Set up filter - Display record in f_ActivityDetails_Admin that corresponds to record in f_ActivityDetails_datasheet"
Dim sFilter As String
Dim rs  As DAO.recordset
Dim lRecordCount    As Long

DoCmd.Echo False

'Define the record set for the Activities associated with this Trip

Set rs = Forms!f_MainTripForm_Admin.ActivityDetailsDatasheet.Form.RecordsetClone
  
  lRecordCount = rs.RecordCount
  
    If Not Forms!f_MainTripForm_Admin.NewRecord And lRecordCount <> 0 Then
    Debug.Print "We have found Activities assoicated with this Trip"
    Debug.Print "ActivityDetailsDatasheet has: " & lRecordCount & " records(s)"

'Show me the current Activity_ID
      
    Debug.Print "  datasheet Activity_ID is: "; Forms![f_MainTripForm_Admin]![ActivityDetailsDatasheet].Form![Activity_ID]
        sFilter = "Activity_ID = " & Forms![f_MainTripForm_Admin]![ActivityDetailsDatasheet].Form![Activity_ID]

'Show me the current Filter
        Debug.Print "  Current Filter is: "; sFilter
        Forms!f_MainTripForm_Admin!f_ActivityDetails_SFC.Form.Filter = sFilter
        Forms!f_MainTripForm_Admin!f_ActivityDetails_SFC.Form.FilterOn = True
            
  Else
    'If there are  NO Activities assoicated with this Trip
    'show 0 records in the recordset
    Debug.Print "ActivityDetailsDatasheet has no records: " & lRecordCount & " records(s)"
    
  End If
    
Set rs = Nothing
            
Debug.Print "Call TabView"
Call TabView
            
Debug.Print "End FilterActivities()"

DoCmd.Echo True
End Sub

[zmbd]

Form design:
Mainform: f_MainTripForm_ Admin
Subform1: f_ActivityDetai ls_SFC - this is subform to Mainform?
Subform2: f_ActivityDetai ls_Datasheet- this is subform to Mainform?

On first read, requery is needed on subform2 when a change is made in Mainform given that when you move records on the Mainform the subforms are updated correctly. If so, then, in the Mainform's afterupdate event insert a requery on the subform. You should also check that the filter is set properly to show the changes.

[Sandra Walsh]

Hello Z

Confirming that
Subform1: f_ActivityDetai ls_SFC - is subform to Mainform
Subform2: f_ActivityDetai ls_Datasheet - is subform to Mainform

f_ActivityDetai ls_Datasheet mimics [or rather, is supposed to mimic ;)] a splitform function for the f_ActivityDetai ls_SFC

The records already update correctly when there is a change in Mainform. I need the view of the records in f_ActivityDetai ls_Datasheet to update when I make a change in f_ActivityDetai ls_SFC.

FYI:
the f_ActivityDetai ls_SFC recordsource is t_Activities
the f_ActivityDetai ls_Datasheet recordsource is a query based on t_Activites that includes the lookup values for fields that have an ID; ie company name shows as opposed to Company_ID

I tried to set a requery on f_ActivityDetai ls_Datasheet when there is a change in f_ActivityDetai ls_SFC but it did not work:

Private Sub Form_AfterUpdate()
DoCmd.Save
DoCmd.Requery
End Sub

[zmbd]

requery subform

When the change is in the record for the mainform, then the event is in the mainform.
In the mainform's after update event is where we need to do the requery
(And the naming of these things gets goofy)
In the afterupdate event of the mainform:
Me!f_ActivityDe tails_Datasheet .Form.Requery
I'm working on the premise that the subform container is named f_ActivityDetai ls_Datasheet, occasionally the name of the subform doesn't match what one would expect

It would seem to me that there would be a requery all command.

[Sandra Walsh]

Got it!

In the AfterUpdate event of f_ActivityDetai ls_SFC
(which is related to the main form as
[Forms]![f_MainTripForm_ Admin]![f_ActivityDetai ls_SFC])

Private Sub Form_AfterUpdate()

Forms![f_MainTripForm_Admin]![ActivityDetailsDatasheet].Form.Requery

End Sub

So simple - thanks Z!!

[zmbd]

YEA!!!!
Only simple once you've seen it :)