Get data in one subform to load before onCurrent event of other subform

[Sandra Walsh]

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 following code:

Sub FilterActivities()
 
Debug.Print "Start FilterActivities()"
Debug.Print " Set up filter - Display record in form that corresponds to record in datasheet"
 
Dim sFilter As String
If Not Forms!f_MainTripForm_Admin.NewRecord Then
sFilter = "Activity_ID = " & Forms![f_MainTripForm_Admin]![f_ActivityDetails_SFC].Form![Activity_ID]
 
Debug.Print " Display Activity_ID: "; Forms![f_MainTripForm_Admin]![f_ActivityDetails_SFC].Form![Activity_ID]
Debug.Print "Display Filter: "; sFilter
 
Forms!f_MainTripForm_Admin!f_ActivityDetails_SFC.Form.Filter = sFilter
Forms!f_MainTripForm_Admin!f_ActivityDetails_SFC.Form.FilterOn = True
 
End If
 
Call TabView
Debug.Print "End Start FilterActivities()"
 
End Sub

When the f_MainTripForm_Admin is first opened, everything loads properly.

However, when I select a different record in f_MainTripForm_Admin's split form, the data in the f_ActivityDetails_SFC form does not load, (the Activity_ID field shows (New) ) so the sFilter can not get a value for the Activity_ID called for in line 8 of the code above. I get the error:

Run-time error '3075'
Syntax error (missing operator) in query expression 'Activity_ID = "



All the related Activities are displayed in f_ActivityDetails_Datasheet.

How can I get the data in the ActivityDetails_Admin to load before the OnCurrent event kicks in for f_ActivityDetails_Datasheet?

Many thanks,
Sandra

[zmbd]

If I am reading your code correctly:
(it's late and I'm on a friend's laptop :) )O

Line 8 is calling for information from the subform that is then fed back into the same subform on line 13; however, the mainform has changed from one record to the next so the subform has no reference to the new records related between itself and the main form - hence, no information to pass.

[Sandra Walsh]

Hello Z -

I reworked the code (below) and the Activity records are now loading correctly.

The issue I have now 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 select a different record 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

Set rs = Nothing

the correct approach?

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]

Yea, For getting things working!

Respectfully:
Sandra, with the change in the scope of the question, the forum guidlines ask that the new question be started in a new thread (one question per thread).

Before we work in the new question, please repost.
I don't want to split your current post out of this thread as it contains the re-worked code that is working for you.

[Sandra Walsh]

Thanks Z -

Reposted here.