I'm having trouble with a form I'm creating to track proposals. Eventually this will be a tabbed subform within a client's record, but I'm trying to make sure it works on its own before combining it. The form is called [Proposals] and there is an integer field [Proposal_Status]. There are about a dozen other fields, but they aren't involved at the moment. I also created a table called [Prop Status Labels] included here:
Proposal_Status Definition 1 Requested, not yet submitted 2 Submitted, not yet approved 3 Approved, project number assigned 4 Rejected 5 Put on hold 6 Cancelled after approval
I want the status to be in big print at the top of each record, so I created an unbound box and put in the following formula as its control: =IIf(IsEmpty([Proposal_Status]),"No Status",DLookUp("[Definition]","[Prop Status Labels]","[Proposal_Status]=" & [Forms]![Proposals]![Proposal_Status]))
Now no matter what value I put into the form, it returns "Requested, not yet submitted". When there is no value, it returns #Error, which is annoying, but I can live with if it can deal with actual status updates correctly. Removing the Iif and IsEmpty functions did not change any of the outputs, but I included them here in case someone can fix it all in one fell swoop.