DLookup function within a form control always returning same value

[tbgh]

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.

[ItsMe]

Perhaps a subform could be dependent upon a combobox. THe combobox could have a "Limit to List" Property = yes. I am having trouble understanding your IIF. You could possibly have the combobox RowSource dependent on the main form....

[tbgh]

At the moment, it isn't a subform. That might raise more issues when I go to combine it, but at the moment it's its own separate form without any kind of combobox.

As for the Iif, basically it's supposed to be saying, if the [Proposal_Status] field is empty, return "No Status". Otherwise, do a Dlookup. And it's functioning well enough to get to the dlookup function, just the dlookup is . . . being obstinate.

[ItsMe]

IsEmpty probably is not suitable here. It seems you are trying to do validation on a bound control. Getting a bound control to display something other than what is in the respective field will not work.

Try creating a label that displays your message and name it, lbl.NoStatus. Then, in your form's current event, place an If then Else statement to change the two control's .Visible properties

If Me.BoundControl.Value <= 0 then
me.lbl.NoStatus.visible = true
Me.BoundControl.Visible = false
else
me.lbl.NoStatus.visible = false
Me.BoundControl.Visible = true
end if

[tbgh]

Thanks, that works great for the "No Status" message and I can position it overlapping with the current text box and simplify the formula.

However I still need help with the DLookup function that only returns the first value in the table.

Now the control source reads: =DLookUp("[Definition]","[Prop Status Labels]","[Proposal_Status]=" & [Forms]![Proposals]![Proposal_Status])

[tbgh]

*update
I tried making a query joining on Proposal_Status and setting that as the data source and just inserting the definition field where the formula used to be. The lookup now works great, but I can't put any new records in, it says "This Recordset is not updateable". Maybe there's a simple setting somewhere to fix that error rather than the dlookup troubles . . .

[ItsMe]

Is "Prop Status Labels" the name of a table? The Dlookup seems to be good.

What type of field is "Proposal_Status"?

Is the form, "Proposals" open and do you have the name correct?

edit: I just noticed that your Dlookup is probably returning more than one record. Do you have a PK value that you can go after? I am guessing Proposal_Status is a yes no field and you should be going to the PK to determine status of that specific record.

[tbgh]

Yes, "Prop Status Labels" is the table from the first post.

Integer, and yes it's a numeric integer field in both Proposals and Prop Status Labels.

Yes

I don't think the stuff under edit applies since Proposal_Status is an integer and should match only one value from Prop Status Labels.

[ItsMe]

Try breaking down your expression a little further then. Remove the form part. Isolate a record in the table and understand the field values. Grab a known value from the Proposal_Status field. Let's say 200 is a PK for one record and then look for the literal

=DLookUp("[Definition]","[Prop Status Labels]","[Proposal_Status]= 200"

Get each component of your aggregate expression to function and then aggregate...

[tbgh]

Tried particular values and it started returning blanks. If someone knows a solution great, if not, I compromised by posting the meanings of each of the possible statuses next to the field with the number so they can know what it means. Thanks for all your help!

[ItsMe]

It seems to me that you have isolated the problem to your Dlookup.

Not to try to complicate matters but why not include the Definition field in your form's recordsource? You already have Proposal_Status why not bind your unbound control to Definition? Then the .Visible thing you tried earlier will display the No Status when needed.