0

I have a form that selects the customer name. Once the customer is selected, the subform shows a list of plots they own. I am trying to bring up who is buried in each plot in the subform. I just need the name to show up which in a different table. I think part of the problem is that in my plot table, the same customer can own several plots. Some plots are empty and some plots are full.

I have tried nested DLookUp statements. I have tried a DLookUp with Select statements. I haven't been able to figure out how to do this.

=DLookUp("[Buried_FN] & ' ' & [Buried_MI] & ' ' & [Buried_LN]","[Burial_Info]","[Burial_Info]![BuriedID]=" & DLookUp("[Plot]![BuriedID]","[Plot]","[Plot]![OwnerID1]=" & [Forms]![Owner_Info]![SelectedCust].[Column](0)))

So I have 3 tables that I am working with in all right now. Owner_Info Plot Burial_Info

In my Plot table, I have a field for OwnerID1 which is the ID for Owner_Info. I also have a BuriedID in Plot that is the same BuriedID in Burial_Info.

What I need in my subform is to show the name from the Burial_Info table if the plot has a burial and show nothing when it is still open.

Picture of Form I'm Using

Improve this question

1 Answer 1

Reset to default
0

Here is what I came up with that seems to work.

=IIf(IsNull([BuriedID]),"",DLookUp("[Buried_FN] & ' ' & [Buried_MI] & ' ' & [Buried_LN]","[qry_BuriedName]","[qry_BuriedName]![ID]=" & [Forms]![Owner_Info]![SelectedCust].[Column](0) & " AND [qry_BuriedName]![BuriedID]=" & [BuriedID]))

In case anybody else needs help with it.

Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.