Conditional Formatting on a lookup field list

[Jideogu]

Help needed:

My table fields are as follows:
1. Issued? (This is a lookup field with the following lists "Yes"; "No"; "NA")
2. IssueDate

I created a subform bound to the table

I wish to accomplish the following on the form:

1. If "No" is selected on the [Issued?] field, I want the [IssueDate] to gray out.
2. If "Yes" is selected, I want [IssueDate] to be required.

I set Conditional Formatting as follows:

Expression is ([Issued?]="No"), condition is disabled.

When I tested it, [IssueDate] grayed out when "No" was selected. However, when I select "Yes", it still remained grayed out.

What am I doing wrong? How can I get this to work? Please help me.

 

[madefemere]

Hello,

May be only with :

[Issued?] = False

 

[Frothingslosh]

The problem is that you're using a lookup field. Queries using lookup fields are very hit-and-miss.

Read this: http://access.mvps.org/access/lookupfields.htm

 

[Jideogu]

Thank you madefemere. The code did not work. Is there any other to do this?

Thanks for you help.

 

[Jideogu]

Thanks Frothingslosh:

The lookupfield is actually a list of values that were typed in while designing the table using the lookup wizard. It is not pulling from a table. Will I have the same problem if I use a the list combobox on the form instead?

 

[Frothingslosh]

You should really never use a lookup field. Instead, just use a Text field.

On the form, create a combo box using the wizard, select the option to manually type in your values, then type them in. Then go into the combo box properties, find 'Limit to List', and make sure that's set to Yes.

Viola - the user will select only from the word values you want, without any more fuss. Then you can use conditional formatting based on that text, and it should work. Just make sure to account for all three possible values.

Edit: You could even include a validation rule in the table itself of:

"Yes" or "No" or "N/A"

If you do that, it will reject all values that don't match one of the three, although if data entry is limited to the values in that combo box I listed, it's a non-issue.