Re: [MSAccessProfessionals] Change Value based on another value

Duane,

I will give it a try. 

 

Wow, cardiac arrest, even with all the biking you do.  I am very glad the EMT was close.

 

With your perspective I understand how this year would be special.

 

Thanks again.

 

Bill

 

Bill Singer

 

 

 

From: MSAccessProfessionals@groups.io On Behalf Of Duane Hookom
Sent: Thursday, November 23, 2023 12:46 PM
To: MSAccessProfessionals@groups.io
Subject: Re: [MSAccessProfessionals] Change Value based on another value

 

You could use code similar to what Paul suggested in the after update event. 

 

Happy Thanksgiving to you also. This year is special for me since I had a major cardiac arrest in June. I wouldn't be here if not for the ten EMTs within 50' of me when it happened. 

 

Duane

Sent from my mobile

On Nov 23, 2023, at 12:22 PM, Bill Singer <Bill.Singer@at-group.net> wrote:



Duane,

Happy Thanksgiving to you.  I have been refreshing my knowledge on DLookup.

 

Will DLookup just display the information or will it assign the value to the field CTICRUHealth.  The example I looked at has the DLookup in the Source property and I am thinking that would disconnect it from the table behind the form.

 

I am not at work now so I did not actually try this yet.

 

Thanks,

 

Bill

 

Bill Singer

 

 

 

From: MSAccessProfessionals@groups.io On Behalf Of Duane Hookom
Sent: Wednesday, November 22, 2023 3:27 PM
To: MSAccessProfessionals@groups.io
Subject: Re: [MSAccessProfessionals] Change Value based on another value

 

I would probably create a small table of ranges that allow you to easily change the upper and lower range values as well as the return value. Your after update code would then use DLookup() with the CTICOBRACount between the low and high values and return the CTICRUHealth value. I always try avoid hard-coding values. 

 

Duane

Sent from my mobile

On Nov 22, 2023, at 2:43 PM, Paul Baldy <Pbaldy@gmail.com> wrote:



Something along the lines of:

 

Me.CTICRUHealth  = IIf(Me.CTICOBRACount > 19, 1, 2)

 

In the after update event of CTICOBRACount.  It could also be done with an If/Then block, as you prefer. 

 

Paul

 

 

------ Original Message ------

From "Bill Singer" <Bill.Singer@at-group.net>

To MSAccessProfessionals@groups.io

Date 11/22/2023 10:22:41 AM

Subject [MSAccessProfessionals] Change Value based on another value

 

I am having a hard time figuring out best way to change the value of 3 fields on a form, based on the initial input in another field.

 

I have a form named         f_PremCollFormSetup

I have a field in that form called     CTICOBRACount

 

When a person enters a value into  CTICOBRACount and if that value is over 19 I want to set/change the value in the fields called  CTICRUHealth  and CTICRUDental to a value of 1

 

And if the value of CTICOBRACount is not over 19 I want to set/change the value to 2.

 

I am having a hard time figuring out where the best place to put this.  I am guessing in the AFTER UPDATE property of the CTICOBRACount field.

Do I use iff or If then… 

It has been a while since I had to add programming.

Thanks for your help.

 

Bill

 

Bill Singer

Bill.Singer@at-group.net