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!

 

[Pat Hartman]

The On change event runs multiple times. It runs once for each character typed so I would not use it for this purpose.
The AfterUpdate event of a CONTROL does NOT cause an infinite loop. It is the AfterUpdate event of the FORM that causes an infinite loop if you dirty the record in that event.

And finally, there is no reason to have two fields to mean the same thing. So, having a checkbox for LED and having a text field set to "LED" only leads to potential data anomalies. You must always LOCK one of those controls on the form EVERY TIME you make a new form that shows either of the fields. You must have code behind the control that populates the other field. And then there's the problem of queries.

Rules of normalization are not arbitrary. They are defined to keep you from creating a problem by having the two fields contain conflicting values. If you want to show LED whenever the checkbox is true, make the LEDtext field a calculated value in your query. You can bind it to the form and when you check the box, Access will automatically display LED in the text control because of the calculation in the form's RecordSource query.

 

[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!

 

[Pat Hartman]

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

 

[theDBguy]

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

I can't argue with that either.

 

[Pat Hartman]

 

[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.