Solved registering ActiveControl (1 Viewer)

allanc

Registered User.
Local time
Today, 01:28
Joined
Nov 27, 2019
Messages
46
Hello Friends!

Happy new year to you all!

My form has a lot of fields which collect information that is used to calculate insurance premium. I designed the form for user to enter data and ideally when all data is entered, user clicks on a button that runs a macro to calculate the rate.

Everything seems to be fine except when user has the cursor on a field (e.g a date field) and then clicks on the "Calculate" button - the new entered value is not being registered - my codes are still picking up the old value.

Sorry if this is not clear. Let's say there's a date field where user initially entered 01/2004 and user goes back and updates the field to 01/2006. When the "Calculate"button is clicked, the codes are using 01/2004 instead of 01/2006. The problem only occurs when user leaves the cursor on the date field. There would be no problem if user presses Tab or move the cursor to another field (which would register the new value).

Any help would be greatly appreciated.

Thanks!
Allan
 

theDBguy

I’m here to help
Staff member
Local time
Yesterday, 22:28
Joined
Oct 29, 2018
Messages
21,358
Hi Allan. One possible approach is have the first line of the calculate button code to commit the changes made to the form. For example, you can force a record save. Just a thought...
 

allanc

Registered User.
Local time
Today, 01:28
Joined
Nov 27, 2019
Messages
46
Hi Allan. One possible approach is have the first line of the calculate button code to commit the changes made to the form. For example, you can force a record save. Just a thought...

Could you please elaborate on how that can be done?

The challenge for me right now is to pick up the "new value". Me.ActiveControl.Value returns the "old value".

Thanks for your help!
Allan
 

Micron

AWF VIP
Local time
Today, 01:28
Joined
Oct 20, 2018
Messages
3,476
What does the recalc code look like? AFAIK, Me.Recalc is for recalculating calculated controls, not for saving data. Maybe you just need to move off of the record or control, or use Me.Requery or possibly just Me.Refresh.
 
Last edited:

allanc

Registered User.
Local time
Today, 01:28
Joined
Nov 27, 2019
Messages
46
What does the recalc code look like? AFAIK, Me.Recalc is for recalculating calculated controls, not for saving data. Maybe you just need to move off of the record or control, or use Me.Requery or possibly just Me.Refresh.

Hello Friend!

Me.Requery didn't work but Me.Refresh did!

I do not know exactly what Refresh does but I will certainly check out after work today.

Thank you for your help!
Allan
 

theDBguy

I’m here to help
Staff member
Local time
Yesterday, 22:28
Joined
Oct 29, 2018
Messages
21,358
Hi. Congratulations! Sounds like you got it sorted out now. Good luck with your project.
 

Micron

AWF VIP
Local time
Today, 01:28
Joined
Oct 20, 2018
Messages
3,476
Colin just an FYI. Google book links can be problematic for some. I get told I've exhausted all my free looks at that book, so I guess I've followed a few too many. For the benefit of those that follow and are in that predicament, here's a link and text explanation from the M$ page:
The Refresh method immediately updates the records in the underlying record source for a specified form or datasheet to reflect changes made to the data by you and other users in a multiuser environment.
https://docs.microsoft.com/en-us/office/vba/api/access.form.refresh

Requery will reload the form and show changed or added records. However, I suspected that if a user had not moved off of the control, Requery wouldn't help because the field hadn't been saved yet, but I couldn't recall for certain, thus I added the Refresh comment.

This one explains in more detail (assuming it's all correct and up to date - I didn't read the whole thing)
https://www.599cd.com/tips/access/requery-v-refresh/
 

isladogs

MVP / VIP
Local time
Today, 05:28
Joined
Jan 14, 2017
Messages
18,186
Micron
Good point re books though the OP seems to have been able to view it OK.
I'd also thought about posting the article by Richard Rost

Also thanks for posting the MS link. That was the one I planned to post but couldn't find it earlier. There are of course four separate pages there.

Looks like we did a similar Google search but you were more successful

Allan
One question. What did the topic title mean as it seemed unrelated to the thread...at least to me ;)
 

Micron

AWF VIP
Local time
Today, 01:28
Joined
Oct 20, 2018
Messages
3,476
What did the topic title mean as it seemed unrelated to the thread...at least to me
I had a comment like that in my first post but I removed it so you're not alone. What I removed pointed out that 'registering' a control has a specific connotation that has nothing to do with saving data, but then I figured OK, registering in this case means 'saving data. Sometimes it's a struggle to balance playing the teacher role without coming across as being too literal, no?

Also thanks for posting the MS link.
For those just learning or trying to keep things fresh, consider bookmarking this (it's the entire Office object model + VBA). What I posted was just one small part of it.

https://docs.microsoft.com/en-us/office/vba/api/overview/

It might be daunting for novices as it will be difficult to find things if you don't know whether you're looking for a property, event, object or method. While there are coding examples, it's not the place to go if you have a general question on how to do something.
 

allanc

Registered User.
Local time
Today, 01:28
Joined
Nov 27, 2019
Messages
46
Micron
Good point re books though the OP seems to have been able to view it OK.
I'd also thought about posting the article by Richard Rost

Also thanks for posting the MS link. That was the one I planned to post but couldn't find it earlier. There are of course four separate pages there.

Looks like we did a similar Google search but you were more successful

Allan
One question. What did the topic title mean as it seemed unrelated to the thread...at least to me ;)

Haha... I couldn't put the entire post above in the title line.

Original title what I wanted to achieve based on my poor understanding when I posted :)

:D

Have a good evening everyone (if you share a same timezone with me)
 

Users who are viewing this thread

Top Bottom