Refreshing only certain fields on a form after a subform has been updated (1 Viewer)

PaulD2019

Registered User.
Local time
Today, 21:00
Joined
Nov 23, 2019
Messages
75
[SOLVED] Refreshing only certain fields on a form after a subform has been updated

Hi all,

I've been attempting to modify the database that was originally posted here

Code:
https://www.access-programmers.co.uk/forums/showthread.php?t=176891

Code is added so the main form refreshes after the reason box is changed after a date has been added, unfortunately when the main form refreshes it leaves the employee selected in the drop down box but the data in the subform refreshes to show employee 1's holiday data.

Is there a way to stop the subform data from changing or to set the refreshing code so only the data in the text boxes (Text35, Text37, Text61, Text65 & Text63) refresh?

A copy of the database is attached if someone can kindly have a look
 

Attachments

  • testing database.accdb
    1.8 MB · Views: 90
Last edited:

theDBguy

I’m here to help
Staff member
Local time
Today, 14:00
Joined
Oct 29, 2018
Messages
21,358
Hi Paul. Unfortunately, if you refresh or requery the main form, that also affects the subform because the main form controls what's displayed in the subform. What you can do is "remember" where you were before the refresh and then go back to it using code.
 

PaulD2019

Registered User.
Local time
Today, 21:00
Joined
Nov 23, 2019
Messages
75
Hi Paul. Unfortunately, if you refresh or requery the main form, that also affects the subform because the main form controls what's displayed in the subform. What you can do is "remember" where you were before the refresh and then go back to it using code.

Thanks for the reply theDBguy, how would you make it remember with code?
 

theDBguy

I’m here to help
Staff member
Local time
Today, 14:00
Joined
Oct 29, 2018
Messages
21,358
Thanks for the reply theDBguy, how would you make it remember with code?
Well, depending on what you're doing, I usually use the ID field for it. For example (pseudocode):
Code:
Dim lngID As Long
lngID = Me.ID
'refresh the form
'then
'navigate back to "[ID]=" & lngID
 

PaulD2019

Registered User.
Local time
Today, 21:00
Joined
Nov 23, 2019
Messages
75
Those are calculated controls.

have you tried recalc method?
https://docs.microsoft.com/en-us/office/vba/api/access.form.recalc

Thank you moke123, I haven't, I am still learning with access & I haven't ever used that before

Well, depending on what you're doing, I usually use the ID field for it. For example (pseudocode):
Code:
Dim lngID As Long
lngID = Me.ID
'refresh the form
'then
'navigate back to "[ID]=" & lngID

There are ID fields on both the employee table (main form) & on the employee leave dates (subform). The text boxes on the main form count down how many "Paid Holiday" days & "Bank Holiday" days are left & count how many "Unpaid Days off", "Sick Days" & "No Show" days are recorded from the combo box on the subform.

I've got the below code on the after update event of the Reason field

Code:
Option Compare Database

Private Sub Reason_AfterUpdate()
Forms!Employees.Form.Requery
End Sub

Do I add your code into what I already have?
 

theDBguy

I’m here to help
Staff member
Local time
Today, 14:00
Joined
Oct 29, 2018
Messages
21,358
Do I add your code into what I already have?
Hi. Yes, you would. But before we go there, give Moke's suggestion a try first. Maybe it's all you need to do. Let us know how it goes...
 

PaulD2019

Registered User.
Local time
Today, 21:00
Joined
Nov 23, 2019
Messages
75
Hi. Yes, you would. But before we go there, give Moke's suggestion a try first. Maybe it's all you need to do. Let us know how it goes...

I replaced the refresh code with Recalc & it refreshes the text boxes

Thank you moke & theDBguy!!
 

Users who are viewing this thread

Top Bottom