Show field based on previous field value

[forskare]

Greetings,

HRLO is a drop down list field which will have 4 choices: Homeowner, Renter, Landlord and Other.

If HRLO = 'Renter' or IF HRLO ='Landlord' Then show Field1, else IF HRLO = 'Other', Then show Field2 What is the syntax for this?

Thank you



Ken

[CJ_London]

in vba you might have something like this on a form in the HRLO control after update event

txtField1.visible=me.hrlo="Renter" or me.hrlo="Landlord"
txtField2.visible=me.hrlo="Other"

Note the fields belong to tables, controls belong to forms. going forwar it is worthwhile getting your terminology correct.

And if you do mean fields in tables then a) the answer is no and b) you should not be using dropdowns in tables - over time they will cause you lots of problems

[forskare]

Thank you for your response. Where did I make reference to table or database? I only made reference to fields, field values and syntax. What problems would a dropdown list cause and after how many record entries? I will have as many as 500 records but within the next year, maybe 150. Since you didn't suggest an alternative to the dropdown list, the only option I can think of is multiple choice. Perhaps you know of something better?

Again, Thank you

[orange]

Ken,
Fields are in tables, controls are on Forms and Reports. You mention field1 and field2 and your info says you have Access 2016, so I am assuming you're talking database.
But you haven't told us the purpose of the database or records etc. More background and contextual info would help you get focused responses.
Good luck.

[CJ_London]

What problems would a dropdown list cause and after how many record entries?

in a table - many, mainly interpretation - see this link for an overview http://access.mvps.org/access/lookupfields.htm. in a form, none. how many - I seem to recall 65000 but would be totally unmanageable at that volume

Since you didn't suggest an alternative to the dropdown list

on a form, why would I? its a perfectly acceptable solution. Just not acceptable for tables. tables are for storing data, queries to interpret, forms/reports for presentation/data management. Access is not a bigger excel - applying excel methodology to access will lead to failure. Simplistically excel stores and presents data 'short and wide' in a single view. Access stores data 'tall and thin' in tables - presentation in queries/forms/reports. Once created, tables are not touched again except for the purposes of design changes - which if designed properly, may never happen

[forskare]

Well gentlemen, thank you for the education. What I should've said is perhaps something like this: 1 table, 12 fields - 4 are hidden.

User selects the value in control1 (Homeowner, Renter, Landlord, Other) will be stored in Field1

iif control1 = Renter or iff Control1 = Landlord, show control2, control3 and control4 (Values to be stored in fields 2, 3, 4)
else iif control1 = Other, show control5 (Value to be stored in field5)


ken

[orange]

?? Ken,
You are asking HOW to do something, but you have provided no context.
Seems to have something to do with real estate, but a few lines of plain English to tell us WHAT
the database is about would be helpful.
A 1 table database is rare.
Perhaps you could post your table design info so readers don't have to guess what you're dealing with.

Links in my signature can direct you to info on stated topics.
Good luck.

[forskare]

The database will have several tables. What is meant, in this scenario, only one table with 12 fields with 4 hidden is involved.

The user selects one value (4 choices)in control1. Depending on the value selected, I need 1 hidden field to show. If a different choice is made, 3 hidden fields show.

IIf control 1 = renter, show controls 3,4,5

Else iif Control + Other, show control 2

Endiif

Ken

[CJ_London]

OK so HRLO is a combo on a form with the options of 'Renter', 'Landlord' etc

depending on the value selected in this control, you want different controls to be visible

assuming this is correct then in your combo afterupdate event put something like

me.control1.visible=me.HRLO='Renter' or me.HRLO='landlord'
me.control2.visible=me.HRLO='Other'

[forskare]

Hello Ajax,

We're getting there.

me.control1.visible=me.HRLO='Renter' or me.HRLO='landlord'
me.control3.visible=me.HRLO='Section6' and me.control4.visible=me.HRLO='HRLODate' and me.control5.visible=me.HRLO='HRLOName'
me.control1.visible=me.HRLO='Other'
me.control2.visible=me.HRLO='DefineOther'

This is supposed to mean that if either Renter or Landlord is chosen in control1, then make controls 3,4,5 visible
else if Other is selected in control 1, than make control2 visible. I take it, using the words IIf, Else, Then are not needed, only the 4 lines of code as shown. I keep thinking there should be something between lines 2 & 3 to differentiate the process. Yes? No?

Thank you,
Ken

[CJ_London]

your meaningless naming of controls does not make it any easier. This is the revised code based on what I believe you are saying and have provided as your code and within your post

me.control1.visible=me.HRLO='Renter' or me.HRLO='landlord' or me.HRLO='Other'
me.control2.visible=me.HRLO='Other' or me.HRLO='DefineOther'
me.control3.visible=me.HRLO='Renter' or me.HRLO='landlord' or me.HRLO='Section6'
me.control4.visible=me.HRLO='Renter' or me.HRLO='landlord' or me.HRLO='HRLODate'
me.control5.visible=me.HRLO='Renter' or me.HRLO='landlord' or me.HRLO='HRLOName'


it doesn't help when you say

This is supposed to mean that if either Renter or Landlord is chosen in control1, then make controls 3,4,5 visible
else if Other is selected in control 1,than make control2 visible

I presume here you mean HRLO, not control1. No idea whether 'Other' and 'DefineOther' are the same thing or not. No idea where Section6, HRLODate or HRLOName come from

It is important when you ask questions to get your facts right. being sloppy with names and how you describe them will just waste everybody's time.

[forskare]

I apologize for being sloppy and what I say doesn't meet your standards. Obviously, that irritates you. Instead of negative criticism (which irritates me) you might have: 1) Shown an example of what makes sense to you and/or 2) Asked me a question or two such as. I don't understand what you mean by ... Please clarify and/or 3) After each line of code, explain what that line of code means/does.

I'm trying to understand the code you wrote so let’s see if I have this right.


me.control1.visible=me.HRLO='Renter' or me.HRLO='landlord' or me.HRLO='Other' This makes Control1 always visible because one of these values will always be there. Correct? Not sure why this line of code is needed because Control1 is not hidden.

me.control2.visible=me.HRLO='Other' This makes Control2 visible if Control1 (HRLO) = ‘Other’


This makes Control3, Control4 & Control5 visible if Control1 (HRLO) = ‘Renter’ or Landlord’

me.control3.visible=me.HRLO='Renter' or me.HRLO='landlord'
me.control4.visible=me.HRLO='Renter' or me.HRLO='landlord'
me.control5.visible=me.HRLO='Renter' or me.HRLO='landlord'

Since these are the controls and bit values, they are not needed, at least not where you placed them, correct? If they are needed where you placed them, I don't understand why.

or me.HRLO='DefineOther' is Control2
or me.HRLO='Section6' is Control3
or me.HRLO='HRLODate' is control4
or me.HRLO='HRLOName' is Control5

In case I’ve confused you further:

Control1 is HRLO;
Control2 is DefineOther;
Control3 is Section6;
Control4 is HRLODate;
Control5 is HRLOName;

Thus:

If Control1 = Renter or if Control1 = Landlord, then Control3(Section6) Control4 (HRLODate) and Control5 (HRLOName) become visible.
If Control1 = Other, then Control2(DefineOther) becomes visible

Ajax, we are learning from each other. Thank you for your assistance.

Ken

[CJ_London]

1) Shown an example of what makes sense to you

I did, based on the information provided

This is the revised code based on what I believe you are saying and have provided as your code and within your post

me.control1.visible=me.HRLO='Renter' or me.HRLO='landlord' or me.HRLO='Other'
me.control2.visible=me.HRLO='Other' or me.HRLO='DefineOther'
me.control3.visible=me.HRLO='Renter' or me.HRLO='landlord' or me.HRLO='Section6'
me.control4.visible=me.HRLO='Renter' or me.HRLO='landlord' or me.HRLO='HRLODate'
me.control5.visible=me.HRLO='Renter' or me.HRLO='landlord' or me.HRLO='HRLOName'

2) Asked me a question or two such as. I don't understand what you mean by ... Please clarify and/or

this is expecting a response

No idea whether 'Other' and 'DefineOther' are the same thing or not. No idea where Section6, HRLODate or HRLOName come from

3) After each line of code, explain what that line of code means/does.

sorry, didn't think it required it

either me.HRLO='Renter' is true or false, me.control1.visible can only be true or false

[davegri]

I think it would be very helpful to see a design view image of the form containing the controls in question here, as well as a design view image of the table in question.
That might clear up whether for example, a control is named control1 or actually named HRLO. If it's named HRLO, why mention control1 at all?
OP might be attempting to set properties of data names or maybe labels instead of controls. Very confusing. There has been no mention of any error messages at all.

[forskare]

That might clear up whether for example, a control is named control1 or actually named HRLO. If it's named HRLO, why mention control1 at all?

Please read my last post, towards the bottom.

OP might be attempting to set properties of data names or maybe labels instead of controls. Very confusing. There has been no mention of any error messages at all.

What is OP? I asked that same question