Solved Updating or refreshing subform after value/s in main form is updated. (1 Viewer)

tcneo

Member
Local time
Today, 09:14
Joined
Dec 7, 2021
Messages
68
I have a form where I can select a value in a textbox and combo box (e.g. date and name of analyst)

I have a subform in the above form which is based on a query (let's call it query_for_subform) that returns a few values. Two of the criteria for this query_for_subform uses come from the above textbox and combo box.

When I open the main form in record editing mode, the subform works the way it should since the query is fed with the values present in the textbook and combo box. But if i change the values in the textbox and/or combo box, the subform does not update.

When I open the main form in new record mode, the subform shows nothing since the textbox and combo box is empty. When I fill up the values in the textbox and/or combo box, the subform does not update.

How do I fix this?
 

theDBguy

I’m here to help
Staff member
Local time
Yesterday, 18:14
Joined
Oct 29, 2018
Messages
19,200
You'll need to Requery the subform or just set it up to the right linked master fields.
 

CJ_London

Super Moderator
Staff member
Local time
Today, 02:14
Joined
Feb 19, 2013
Messages
14,740
But if i change the values in the textbox and/or combo box, the subform does not update.
are these controls the ones that determine what to show in the subform? or does the subform reference them in some way for a calculation or similar?

not really enough info to understand what your form/subform is supposed to do but sounds like you have not related the table for the main form with the table in the subform

Are you mapping relationships in the relationships window?
are the subform link child/master properties populated correctly
 

tcneo

Member
Local time
Today, 09:14
Joined
Dec 7, 2021
Messages
68
are these controls the ones that determine what to show in the subform? or does the subform reference them in some way for a calculation or similar?

not really enough info to understand what your form/subform is supposed to do but sounds like you have not related the table for the main form with the table in the subform

Are you mapping relationships in the relationships window?
are the subform link child/master properties populated correctly
the main form displays records from a table, Task_Details. 2 of the fields, Task_Detail_Date and Analytical_Method are used by the query in the subform to display a list of available staff.

I have added the names of both controls in the criteria line for the query in the subform.
 

tcneo

Member
Local time
Today, 09:14
Joined
Dec 7, 2021
Messages
68
update: i deleted the old subform and created the new one. and i see what you guys are talking about with the master/child setting.

so i linked Task_Detail_Date in the main form with Work_Date in the subform; Analytical_Method_ID with Analytical_Method_ID.

my test file is attached for you to view. Go to Main Form, click on "Assign Analyst to routine activities" button, then the Assign hyperlink to check it out.

Thanks to @theDBguy and @CJ_London for pointing out about the master/child.

Next, I notice something about the behaviour:
1) if i change the date in the main form and click on the subform, the subform will update.
2) if i change the method in the main form and click on the subform, no update.
3) if i change the method in the main form and then select the same date (again), the subform will update.

Is this expected behaviour?

Lastly, since I linked 2 fields. It seems like the field has to be in subform. If i delete it, it doesn't "filter". How can I have the Analytical_Method_ID field but not see it in the subform (cos it is only meant as a filter). If i set the Visible property of the textbox control for Analytical_Method_ID in the subform, it still appears.
 

theDBguy

I’m here to help
Staff member
Local time
Yesterday, 18:14
Joined
Oct 29, 2018
Messages
19,200
Hi. Glad to hear you are making good progress. You might consider posting a sample copy of your db, so we can assist you better. Cheers!
 

tcneo

Member
Local time
Today, 09:14
Joined
Dec 7, 2021
Messages
68
Hi. Glad to hear you are making good progress. You might consider posting a sample copy of your db, so we can assist you better. Cheers!
oops, i forgot to attach the file. just did so in the earlier post.
 

June7

AWF VIP
Local time
Yesterday, 17:14
Joined
Mar 9, 2014
Messages
4,461
Table relationships appear to be circular. This can carry over into queries. myQuery_for_Analyst_Time_Used reflects circular linking, should fix. https://www.codeproject.com/articles/38655/prevent-circular-references-in-database-design.

Analyst_Availability and Task_DetailTable are not linking on primary and foreign keys. Links should be on key fields.

Setting Master/Child Links properties forces master key(s) to be saved as foreign key(s) into new subform record. Linking two pairs of fields would be a compound key.

Exactly which form is involved in issue?
 
Last edited:

tcneo

Member
Local time
Today, 09:14
Joined
Dec 7, 2021
Messages
68
Table relationships appear to be circular. This can carry over into queries. myQuery_for_Analyst_Time_Used reflects circular linking, should fix. https://www.codeproject.com/articles/38655/prevent-circular-references-in-database-design.

Analyst_Availability and Task_DetailTable are not linking on primary and foreign keys. Links should be on key fields.

Setting Master/Child Links properties forces master key(s) to be saved as foreign key(s) into subform record. Linking two pairs of fields would be a compound key.

Exactly which form is involved in issue?
yes, you are right. myQuery_for_Analyst_Time_Used doesn't work. It is one of my earlier attempts at trying to create a complicated (to me) query.

The query used for the subform being discussed for this thread is myQuery_for_subform.

As for the form/subform, go to Main Form, click on "Assign Analyst to routine activities" button, then the Assign hyperlink to check it out.

And thanks a lot for sharing the link to the article on circular references. I learnt a lot from it.
 
Last edited:

tcneo

Member
Local time
Today, 09:14
Joined
Dec 7, 2021
Messages
68
You'll need to Requery the subform or just set it up to the right linked master fields.
Right now, what i have done (on the actual database) is that i placed a line of VBA code in both the date textbox and analytical method combobox that requeries the subform in the After Update event.

This helps to update the subform. I guess i could add a "Refresh" button with the same requery code too. haha..

the code i used is of the form: Me.[subform name].Form.Requery
 

theDBguy

I’m here to help
Staff member
Local time
Yesterday, 18:14
Joined
Oct 29, 2018
Messages
19,200
Right now, what i have done (on the actual database) is that i placed a line of VBA code in both the date textbox and analytical method combobox that requeries the subform in the After Update event.

This helps to update the subform. I guess i could add a "Refresh" button with the same requery code too. haha..

the code i used is of the form: Me.[subform name].Form.Requery
That works. Cheers!
 

June7

AWF VIP
Local time
Yesterday, 17:14
Joined
Mar 9, 2014
Messages
4,461
A form usually does data entry/edit for only one table (Task_Detail_Table in this case). This form RecordSource includes multiple tables. I presume related tables are included only to display related data (Task_Table and Product_Table). Are you allowing edit of all these fields on main form?

Main form includes Task_Detail_Table in RecordSource and subform also includes Task_Detail_Table - why? The more I look, the more bizarre this gets. Are you trying to emulate a Split form? Why do you need such complicated queries as RecordSource?
 

tcneo

Member
Local time
Today, 09:14
Joined
Dec 7, 2021
Messages
68
A form usually does data entry/edit for only one table (Task_Detail_Table in this case). This form RecordSource includes multiple tables. I presume related tables are included only to display related data (Task_Table and Product_Table). Are you allowing edit of all these fields on main form?

Main form includes Task_Detail_Table in RecordSource and subform also includes Task_Detail_Table - why? The more I look, the more bizarre this gets. Are you trying to emulate a Split form? Why do you need such complicated queries as RecordSource?
The form, Task_Detail_Interface, displays the list of tasks in a datasheet format. It has a column of "Assign" hyperlink for users to click on so as to open up a form, Task_Detail_Add_v3, to edit the details of each task.

It draws info from Product Table as the Task_Detail_Table only has a product_id, the product_name is referenced in the Product Tabe. Task_Table is a "legacy issue" (created in the beginning when i was still experimenting with Access) and will be removed in a future iteration.

The subform in Task_Detail_Add_v3 is intended to display the number of minutes available for each analyst on the day associated with the task.

The idea is as follows:
User goes to Main Menu, and clicks on Assign Analysts to Routine Activities.
Task_Detail_Interface forms opens.
The user clicks to edit a Task displayed in Task_Detail_Interface.
Task_Detail_Add_v3 form will appear.
Task_Detail_Add_v3 will display the details of the Task such as Date, Product name, Analytical method name, etc. Inside this form, the subform will display the number of minutes each analyst has available for that date associated with the selected task. This is so that the user may know who else is available and thus assign a different analyst if need be.

Background: this is being developed for a laboratory that performs tests on samples. It aims to help with the planning of tasks and assignment of lab analysts to tasks.

That is the workflow envisioned for now.

I hope that is clearer to you.

If you have suggestion on how it can be improved, I'll be glad to learn from you. I only started learning to use Access only in Oct'2021.

Thanks in advance!
 

June7

AWF VIP
Local time
Yesterday, 17:14
Joined
Mar 9, 2014
Messages
4,461
That doesn't alter comments already provided. Have you made any changes along the lines I pointed out? What exactly is the issue that needs fixing now?
 

Users who are viewing this thread

Top Bottom