By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,294 Members | 2,708 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,294 IT Pros & Developers. It's quick & easy.

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

P: 62
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_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
  1. Sub FilterActivities()
  2.  
  3. Debug.Print "Start FilterActivities()"
  4.  
  5. Debug.Print "     Set up filter - Display record in f_ActivityDetails_Admin that corresponds to record in f_ActivityDetails_datasheet"
  6. Dim sFilter As String
  7. Dim rs  As DAO.recordset
  8. Dim lRecordCount    As Long
  9.  
  10. DoCmd.Echo False
  11.  
  12. 'Define the record set for the Activities associated with this Trip
  13.  
  14. Set rs = Forms!f_MainTripForm_Admin.ActivityDetailsDatasheet.Form.RecordsetClone
  15.  
  16.   lRecordCount = rs.RecordCount
  17.  
  18.     If Not Forms!f_MainTripForm_Admin.NewRecord And lRecordCount <> 0 Then
  19.     Debug.Print "We have found Activities assoicated with this Trip"
  20.     Debug.Print "ActivityDetailsDatasheet has: " & lRecordCount & " records(s)"
  21.  
  22. 'Show me the current Activity_ID
  23.  
  24.     Debug.Print "  datasheet Activity_ID is: "; Forms![f_MainTripForm_Admin]![ActivityDetailsDatasheet].Form![Activity_ID]
  25.         sFilter = "Activity_ID = " & Forms![f_MainTripForm_Admin]![ActivityDetailsDatasheet].Form![Activity_ID]
  26.  
  27. 'Show me the current Filter
  28.         Debug.Print "  Current Filter is: "; sFilter
  29.         Forms!f_MainTripForm_Admin!f_ActivityDetails_SFC.Form.Filter = sFilter
  30.         Forms!f_MainTripForm_Admin!f_ActivityDetails_SFC.Form.FilterOn = True
  31.  
  32.   Else
  33.     'If there are  NO Activities assoicated with this Trip
  34.     'show 0 records in the recordset
  35.     Debug.Print "ActivityDetailsDatasheet has no records: " & lRecordCount & " records(s)"
  36.  
  37.   End If
  38.  
  39. Set rs = Nothing
  40.  
  41. Debug.Print "Call TabView"
  42. Call TabView
  43.  
  44. Debug.Print "End FilterActivities()"
  45.  
  46. DoCmd.Echo True
  47. End Sub
Oct 3 '12 #1

✓ answered by zmbd

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_ActivityDetails_Datasheet.Form.Requery
I'm working on the premise that the subform container is named f_ActivityDetails_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.

Share this Question
Share on Google+
5 Replies


zmbd
Expert Mod 5K+
P: 5,397
Form design:
Mainform: f_MainTripForm_Admin
Subform1: f_ActivityDetails_SFC - this is subform to Mainform?
Subform2: f_ActivityDetails_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.
Oct 3 '12 #2

P: 62
Hello Z

Confirming that
Subform1: f_ActivityDetails_SFC - is subform to Mainform
Subform2: f_ActivityDetails_Datasheet - is subform to Mainform

f_ActivityDetails_Datasheet mimics [or rather, is supposed to mimic ;)] a splitform function for the f_ActivityDetails_SFC

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

FYI:
the f_ActivityDetails_SFC recordsource is t_Activities
the f_ActivityDetails_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_ActivityDetails_Datasheet when there is a change in f_ActivityDetails_SFC but it did not work:

Expand|Select|Wrap|Line Numbers
  1. Private Sub Form_AfterUpdate()
  2. DoCmd.Save
  3. DoCmd.Requery
  4. End Sub
Oct 3 '12 #3

zmbd
Expert Mod 5K+
P: 5,397
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_ActivityDetails_Datasheet.Form.Requery
I'm working on the premise that the subform container is named f_ActivityDetails_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.
Oct 3 '12 #4

P: 62
Got it!

In the AfterUpdate event of f_ActivityDetails_SFC
(which is related to the main form as
[Forms]![f_MainTripForm_Admin]![f_ActivityDetails_SFC])

Expand|Select|Wrap|Line Numbers
  1. Private Sub Form_AfterUpdate()
  2.  
  3. Forms![f_MainTripForm_Admin]![ActivityDetailsDatasheet].Form.Requery
  4.  
  5. End Sub
So simple - thanks Z!!
Oct 4 '12 #5

zmbd
Expert Mod 5K+
P: 5,397
YEA!!!!
Only simple once you've seen it :)
Oct 4 '12 #6

Post your reply

Sign in to post your reply or Sign up for a free account.