Conditional Control Source in a Form

[PPICKENS]

I was curious if someone could help me with setting a conditional control source for a field in a form. What I mean is, I want the control source for one field to be set by my selection in another field on the form.

What I'm trying to do is,

When I select value "500" for field [ProgramNumber] of my form, I want the control source for field [Notes]to be from one table, but when I select "600" I want the control source for field [Notes] to be from another table.

Hope this makes some kind of sense to someone, because I haven't been able to figure it out.

 

[PHV]

Have a look to the IIf function.

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[PPICKENS]

PHV,

First of all, thanks for looking at my question and responding.

The Iif function makes sense to me if there were only two options, but what function is used if there are multiple options to choose from.

What I'm trying to say is:

If [ProgramNumber]="500", then control source for the form field would be [Tables]![500CMList]![Notes]
If [ProgramNumber]="600", then control source for the form field would be[Tables]![600CMList]![Notes]
If [ProgramNumber]="800", then control source for the form field would be[Tables]![800CMList]![Notes]
If [ProgramNumber]="150", then control source for the form field would be[Tables]![150CMList]![Notes]

This seems a little different than the way I understand the Iif statement works.

 

[HarleyQuinn]

You might be best off using a SELECT CASE statment.

HarleyQuinn
---------------------------------
The most overlooked advantage to owning a computer is that if they foul up there's no law against wacking them around a little. - Joe Martin

Get the most out of Tek-Tips, read FAQ222-2244 before posting.

 

[PHV]

Anyway, have a look here:

http://r937.com/relational.html

I'd use a single CMList table with fields ProgramNumber and Notes.

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[PPICKENS]

My only problem with making one CM List table is that each program CM List autonumbers its record independently of the other CM Lists.

If I was starting from scratch, I would create one overall CM List for all programs, but since the records have already begun being stored in separate tables, I'm going to have to try to figure out how to make an input form that will work for all the programs. We don't want people inputting data directly into tables (which is what has been happening up to now) because it ends up being a little more confusing to them than a form would.

I appreciate all the help and suggestions so far. It sounds like the SELECT Case statement may be the way to go from some searches I've done on this site. I'm a little new to using Access (especially code and statement writing). Thanks guys.

 

[MajP]

If I was starting from scratch, I would create one overall CM List for all programs, but since the records have already begun being stored in separate tables, I'm going to have to try to figure out how to make an input form that will work for all the programs.

You can very easily create a single table using the existing data. A couple of union queries in a make table query and your done. This will likely save a lot of pain in the long run. You will not need some rigged solution like conditional recordsources for a control.

 

[PPICKENS]

Thanks MajP. I didnt even think of doing that. That seems like a really simple solution. I'll try it out.