IIf statement on form - display field if true

[Renee R]

I am trying to get my form to display instructions using an IIf statement. However, instead of typing the instructions in the IIf statement, I want to put them in a table and have the IIf statement display anything in that particular cell. I would like to do this because if they are on a table, any user can easily edit the instructions if they change.



This is what I've done that works: =IIf([Urgency]=2, "Call Marsha at x8765", "Process as normal")

However, I want the user to be able to easily change who to call... so I've tried this:=IIf([Urgency]=2, [Templates]![UrgentInstruction]=1, "Process as normal")

This displays the #Name? error. How do I get this to work?

I should also say that eventually, I want to have it be much more complex... with AND and OR statements and nested, so that there will be much more specific instructions for various scenarios. Maybe IIf isn't the best way of doing this?

Thanks!
Renee

[June7]

Can't reference table directly like that.

Table must be in the form's RecordSource or use a DLookup function or have a combobox/listbox RowSource pull items from table.

What is the value 1 representing - record ID?

[Renee R]

Yes, I was trying to get it to put the first record in that table.

The instructions are based on two combo boxes, but they are on a different table. So, on Table [orders], I have two fields: [type] and [urgency]. Based on those two combo boxes, I want to display instructions. I can put those on my table [templates], so that a particular row would have the correct type, urgency, and instructions... but I then have no idea how to get the form to pull the correct [UrgenetInstruction] field off of the [Templates] form. I've done it in RowSource where it looks at one combobox... it takes the location and displays how they want to communicate... but I have no idea how to do this with two.

Thanks.

[June7]

Does Template table have fields for Type and Urgency as well as Instruction?

So user selects 'instruction' for display on form - is this selection saved into record?

[Renee R]

Does Template table have fields for Type and Urgency as well as Instruction?

So user selects 'instruction' for display on form - is this selection saved into record?

The Template table doesn't have them at this time. However, I can easily put them in there, if that would make this work better!

The user will select the type of case and urgency on the form, the instruction on what to do will pop up. The case type and urgency IS saved. However, there is no reason to save the instruction, unless saving it would make it easier to program.

I tried doing it with a combo box too... I tried to change bound columns to two... but it didn't work. Maybe I should go back to trying to figure out that method?

Thanks,
Renee

[Renee R]

I'm feeling sheepish. I was making this much harder than it needed to be.


I fixed it. All I had to do was put those two lookup fields in my templates table. Then, add existing fields, and the correct instructions popped up.

Thanks for all your help!

Renee

[Renee R]

I thought I had this fixed... but it isn't... I tried redoing everything, and still nothing!

Here is what I have now:

1) Table [Typelist]. This only has the [type] field. This table is used by the main table to look up the the different options for each entry's type.
2) Table [Communicationlist]. This only has the [communication] field. This table is used by the [SitesList] table to look up the options for each site's method of communication.
3) Table [Sites]. This has the field [location] in it. The main Table [main] references that to lookup the different options for each entry's location. This table also has the field [communication], which is how that location wants us to communicate. The table [main] does not look up communication.
4) Table [templates]. This has the fields [communication], which is looked up from the table [communicationlist], and [type], which is looked up from the table [typelist]. For every possible combination of [communication] and [type], I've put a field [Instruction].

I then made the form [Add New]. Add new pulls the [location] from the [main] table. It also pulls the field [communication] from the [Sites] table. Everything works perfectly.

However, as soon as I try to add the field [instruction], which is based on information in both the main table and the sites table, it stops working. How do I get the Instruction field to display properly?

Thanks,
Renee

[June7]

Looks like 3 fields/comboboxes in conditional arrangement.

Combobox listing Type items.

Combobox listing Communication items is dependent on selection in Type.

Combobox listing Instruction items is dependent on selection in Communication.

Are you saving Type record ID into CommunicationList and Communication record ID into Templates? Or are you saving actual text descriptors?

What table is form Add New bound to - where are you trying to add a new record?