473,327 Members | 2,025 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,327 software developers and data experts.

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

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.

5 10875
zmbd
5,501 Expert Mod 4TB
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
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
5,501 Expert Mod 4TB
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
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
5,501 Expert Mod 4TB
YEA!!!!
Only simple once you've seen it :)
Oct 4 '12 #6

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

Similar topics

1
by: learner | last post by:
I made a form which as a subform on it. The subform has two columns. I want to access the column data in the subform through event control for example, OnDblclick event. If the subform displays...
0
by: Jason | last post by:
I have a primary form which is used to enter/edit data in a table named Test_Results. On this primary form there is a subform which displays site addresses. This subform is linked to the primary...
1
by: Chris | last post by:
When using the formview control it defaults to the item template and I have to add an edit button to go into the edit template. Is there any way to get it to default to the edit template. It would...
2
by: whitc26 | last post by:
Let me preface: I'm a novice, and have no programming experience. I have created an access database and have a few tables in it. I have created a form called "clients" This form opens up and...
3
by: Robert | last post by:
I have a form with two subforms on it. both subs have the same controlsource. Subform1 is a continuous form that acts as an index. The second subform is a single record that the user will use to...
17
by: lokidog | last post by:
I am trying to automatically transfer data from one textbox to another between subforms within a 'main' form. I put this code into the Gotfocus eventprocedure: Private Sub Date_GotFocus() If...
2
by: raddrummer | last post by:
I'm sure this is probably really easy but it's hanging me up so I'll go ahead and ask the question. I have an unbound form that contains a subform containing query results in tabular format. ...
7
by: Deezel | last post by:
I have a many to many relationship between tblResProjData and tblProperty, with tblCHI as junction table. I thought with the 'Enforce Refrential Integrity', and 'Cascade Update Related Fields'...
0
by: skips84s | last post by:
I have a parent form with four tabbed subforms. On subform x, selecting a value from a combo box should update a few controls on this subform, as well as a control on subform y, all done using...
5
by: NaughtyZute | last post by:
I've looked at everything I can find on the Internet for why I get the error "This recordset is not updateable" when attempting to change a data element on a form. I created the form in Access 2007...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.