UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Queries Not Reflecting Table Changes, Access 2016    
 
   
dg_moore
post Nov 10 2019, 06:46 PM
Post#1



Posts: 351
Joined: 22-April 11



I have a number of queries with various criteria on a table whose fields are changed through a form. The query recordsets are displayed in subforms on a second form. When a field is changed in the first form, examination of table shows the change to be correct in the table, but the query does not pick up the changed record. For example, if "Status" is changed from "A" to "D" through the first form, a query on the second form with parameter Status = "D" does not return that record. If the second form is closed and re-opened, the record is returned correctly. Requerying the second form's subforms does not pick up the missing record, but closing/reopening the form does return it. Does anybody have any suggestions?
Go to the top of the page
 
RJD
post Nov 10 2019, 08:08 PM
Post#2


UtterAccess VIP
Posts: 10,144
Joined: 25-October 10
From: Gulf South USA


Hi:

QUOTE
When a field is changed in the first form, ...

Are you including a command to save the record? If you close the form, then open it again with the record present, that suggests to me that the record has not yet been saved until you close the form, or perhaps the subform container is not being requeried. We can't see how this is all set up, but you might try saving the record (to the table) after the change and see if that solves your issue. You can try the Dirty approach or the DoCmd.SaveRecord approach.

HTH
Joe

--------------------
"Each problem that I solved became a rule, which served afterwards to solve other problems."
"You just keep pushing. You just keep pushing. I made every mistake that could be made. But I just kept pushing."

Rene Descartes 1596-1650 (Mathematician and Philosopher)
Go to the top of the page
 
dg_moore
post Nov 10 2019, 09:15 PM
Post#3



Posts: 351
Joined: 22-April 11



I tried adding a SaveRecord on the AfterUpdate of the first form, and also on the AfterUpdate of the form's field, but it made no difference. As I said, inspection of the table shows the updated value of the form field immediately upon updating the field in question. So the value evaluated by the parameter in the query is in the table before the query runs. If the second form is closed when the table field is updated, the updated value is accounted for correctly when the second form is opened, but if the second form is open when the table is updated, the new value in the table is not accounted for in the second form.
Go to the top of the page
 
RJD
post Nov 10 2019, 10:34 PM
Post#4


UtterAccess VIP
Posts: 10,144
Joined: 25-October 10
From: Gulf South USA


Hi: Are you doing a requery on the second form after saving the record on the first? Make sure the requery is for the correct form/subform.

HTH
Joe

--------------------
"Each problem that I solved became a rule, which served afterwards to solve other problems."
"You just keep pushing. You just keep pushing. I made every mistake that could be made. But I just kept pushing."

Rene Descartes 1596-1650 (Mathematician and Philosopher)
Go to the top of the page
 
dg_moore
post Nov 11 2019, 09:26 AM
Post#5



Posts: 351
Joined: 22-April 11



OK - I have it now. I put a Requery button on the second form that refreshes Form1 and requeries all of the subforms on Form 2. Requerying the subforms without refreshing Form 1 didn't work, and I couldn't identify any trappable events that would let me execute these actions automatically, so the Requery button is a simple enough fix.

Thanks for the suggestions.
This post has been edited by dg_moore: Nov 11 2019, 09:28 AM
Go to the top of the page
 
RJD
post Nov 11 2019, 03:53 PM
Post#6


UtterAccess VIP
Posts: 10,144
Joined: 25-October 10
From: Gulf South USA


Hi: No doubt there is a way to do this without button action, but we'd have to see the db to figure that out. But if the button action is satisfactory, then you are good-to-go.

Continued success with your project.

Regards
Joe

--------------------
"Each problem that I solved became a rule, which served afterwards to solve other problems."
"You just keep pushing. You just keep pushing. I made every mistake that could be made. But I just kept pushing."

Rene Descartes 1596-1650 (Mathematician and Philosopher)
Go to the top of the page
 


Custom Search


RSSSearch   Top   Lo-Fi    6th December 2019 - 07:42 AM