Solved Set field value when depending on value of yes/no field

[tmyers]

I feel like an idiot for not figuring this out.
On a table I have a yes/no check box. When the box is checked, I want another field in the same table to set its value to to the text "LED".
Seems so simple and I could write the code for it, but I cant seem to use that dang macro builder for tables.

 

[theDBguy]

Are you using a BeforeUpdate Data Macro? If so, can you show it to us?

 

[tmyers]

Are you using a BeforeUpdate Data Macro? If so, can you show it to us?

Im trying to. I cant follow its logic when trying to do an If Then.

I know it would be something along the lines of

If [LED] = TRUE Then
LampType.text = "LED"

Nice and simple, but the macro builder doesnt lay it out as nicely.

 

[theDBguy]

Im trying to. I cant follow its logic when trying to do an If Then.

I know it would be something along the lines of

If [LED] = TRUE Then
LampType.text = "LED"

Nice and simple, but the macro builder doesnt lay it out as nicely.

Pardon me, I meant the "BeforeChange" event. Here's what I just tried, and it worked fine.

 

[tmyers]

Pardon me, I meant the "BeforeChange" event. Here's what I just tried, and it worked fine.

View attachment 86540

Ill give that a go.
But should it be a before change and not after update?

 

[theDBguy]

Ill give that a go.
But should it be a before change and not after update?

I would prefer using the BeforeChange event. If you are updating the same record using the AfterUpdate event, you might end up in an infinite loop.

Like I said, the above worked for me. If you want to try it out using the AfterUpdate event, let us know how it goes.

 

[tmyers]

I would prefer using the BeforeChange event. If you are updating the same record using the AfterUpdate event, you might end up in an infinite loop.

Like I said, the above worked for me. If you want to try it out using the AfterUpdate event, let us know how it goes.

It worked. I just was unsure of which event to use there.

Thanks DB. This time around the macro builder was throwing me off. It generally is easier to use.

 

[theDBguy]

It worked. I just was unsure of which event to use there.

Thanks DB. This time around the macro builder was throwing me off. It generally is easier to use.

Hi. You're welcome. Glad to hear you got it to work on your end. Good luck!

 

[theDBguy]

The AfterUpdate event of a CONTROL does NOT cause an infinite loop.

Hi @Pat Hartman. Sorry for the confusion. I was referring to the After Update Data Macro (event). Cheers!

 

[theDBguy]

That makes a difference but WHY duplicate data in any event?

I can't argue with that either.

 

[tmyers]

That makes a difference but WHY duplicate data in any event?

I struggled with this decision. Initially I was going to keep them separate. If a light fixture is LED, you check the box. If it isn't, you put in what kind of lamp it uses in the LampType field. So essentially, one would always be blank.

Per a request from others in the department, if the fixture was checked as LED, they wanted the LampType to also state LED so it would show up that way on the report. I did worry about having duplicate fields mean the same thing, but after being pressured into it via the boss, I thought there might not be any harm in it.

I don't believe I could use a calculated field in this instance, since there are times when text will be manually entered in that field.

What I probably SHOULD do, is remove the yes/no field entirely. I would then put a check box on the form itself, that when checked sets the LampType value to "LED" thus solving the issue of two fields meaning the same thing in many instances. I might actually just do that instead. I have been wanting to remove the yes/no for awhile now since I haven't been able to use it how I initially wanted to.

 

[Gasman]

Why not just have a combo with the types.?
Typing L is as easy and checking a checkbox?
You can generally report on how they want it to look and still keep the system logical and efficient.?

 

[tmyers]

Why not just have a combo with the types.?
Typing L is as easy and checking a checkbox?
You can generally report on how they want it to look and still keep the system logical and efficient.?

Couldn't use a combo for this one. There are hundreds of possible types. Different lamp bases, wattages, kelvin ratings etc. It would be something that would have to be manually put in.

I could honestly make a table with a master list of lamps. It would take awhile, as the crappy excel file we have with them is over a thousand lines long. Then make the field on the form a combo that they can just type it and find what they want.

Edit:
Just looked, there are 3500 lines of various lamps in that excel file lol.

 

[Gasman]

Perhaps think of cascading combos which reduces options as you select each combo.
Would be quite quick to enter as well?

 

[tmyers]

Perhaps think of cascading combos which reduces options as you select each combo.
Would be quite quick to enter as well?

That could very well work. Have the first combo select LED or not, then lamp base etc etc.

 

[Gasman]

Also, thinking about it, you could have the checkbox unbound, so then would not be duplicating the data if they insist they want to do it that way?

 

[tmyers]

Also, thinking about it, you could have the checkbox unbound, so then would not be duplicating the data if they insist they want to do it that way?

That is what I have been thinking about doing. Rather than a yes/no field just do an unbound checkbox that if set to true, sets the value of LampType field to LED.

 

[Gasman]

Personally I think I would go with the combos and possible a textbox if the user knows the company code for a certain lamp.?
The combination of the combos would also identify the company code for the relevant lamp.?

 

[theDBguy]

That is what I have been thinking about doing. Rather than a yes/no field just do an unbound checkbox that if set to true, sets the value of LampType field to LED.

If LED is "special," you might consider just setting it as a Default Value for your Textbox, if that field is required.

 

[tmyers]

If LED is "special," you might consider just setting it as a Default Value for your Textbox, if that field is required.

That could also work, as that would be the normal value more than not.