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_ActivityDetails_SFC: Form with tab control view of activity-related fields for one Activity
SubForm 2 - f_ActivityDetails_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_ActivityDetails_Datasheet does not update when I edit, delete or add an Activity in ActivityDetails_Admin.
f_ActivityDetails_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_ActivityDetails_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
Expand|Select|Wrap|Line Numbers
- 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