run code from text box

[Jen0dorf]

Hi

I have a form which is created via a query and criteria so that one record is shown.

In that record is a field called Mlink which relates to several other family records and grave inscriptions.

When this form is displayed I want to show all other inscriptions

i.e. Mink 1234 might relate to a husbands memorial and his wife at another grave

So I added an unbound text field and in the control section of the properties used this code

SELECT tblMemorial.Inscription
FROM tblMemorial
WHERE ((([tblMemorial]![Mlink])=[Forms]![frmDetail]![Mlink]));

The idea being that from table memorial the inscription field is selected subject to the criteria that the Mlink field is equal to the current displayed record Mlink.

However this does not work so my logic is flawed.

Can you not use a query as a control source for an unbound text box?


Any help as usual much appreciated

thanks



Ian

[ranman256]

querys are bound to the form.
text boxes are bound to fields (in that form query only)

you may want to use a parent-child table. 1 person, could have many inscriptions or relations to other graves.

[June7]

Answer is No.

This is what domain aggregate functions are for.

Maybe you need a DLookup().

[Jen0dorf]

Hi

thanks

off to research Dlookup() and parent child relationships

Ian

[Jen0dorf]

Hi

many thanks

it seems dlookup was exactly what I wanted.

Added

=DLookUp("Inscription","tblMemorial","Mlink='" & [MlinkDetail] & "'")

And the form appeared to behave perfectly and certainly showed exactly what I wanted.

Ran some more searches and realised that there seems to be a flaw in that when the Dlookup runs it appears to stop once is finds the first match match.
ie. I have 5 people in a grave with 5 different inscriptions

John Smith
Sam Smith
Ann Smith
Ian Smith
Fred Smith

all linked by this Mlink field.

When the code runs it seems to stop once it reached John Smith whereas I want it to continue to the end of the table and then display all five results.

Is there an instruction to do this

many thanks

Ian

[June7]

DLookup won't do that. It is behaving as it's supposed to.

If you want to see all related records, use a subform or a listbox.

[Jen0dorf]

Hi

thanks for the quick response, shame I like Dlookup;-)

time to research sub forms and list boxes

Ian