DLookup function - need help

[Lukael]

Hi,

I have a continous form (named "Search"), for which I have shortcut menu to open another form. Code for that is :

DoCmd.OpenForm "Results", , , "ID_Results=" & Forms![Search]![Search_Subform].Form![ID_Search]

This code opens form Results and displays records, that are linked by one to many relationship, via ID.



What I want on this form (Results) is to show record with same ID from "Search" form.

So basically, I want a Lookup in textboxes, that will show for what record of "Search" form I'm watching Results.

I tried his :

=DLookUp("Name";"Search";"ID_Search= Search.ID_Search")

This code doesn't produce any errors, but It shows only first record all the time. I want Lookup to show record I selected in "Search" form. Everything else I tried, produced errors.

Can somebody tell me what I'm doing wrong ??

[orange]

You aren't doing anything wrong. That's how DLookup works.

From M$oft:

If more than one field meets criteria, the DLookup function returns the first occurrence. You should specify criteria that will ensure that the field value returned by the DLookup function is unique.

[Lukael]

thanks for response Orange.

My criteria to return should be record that I selected. But I'm not sure how to make field value unique ?

In my Results table I have ID_Search, this ID links my Search table as I said (one to many relationship). But If I do It like this, I get an error :

=DLookUp("Name";"Search";"Results.ID_Search= Search.ID_Search")

Same error If I mix them opposite.

[Lukael]

I figured out, stupid. I didn't know I can use same synthax in DLookup as used when I open form Results. This is solution:

=DLookUp("Name";"Search";"ID_Search=" & Forms![Search]![Search_Subform].Form![ID_Search])

Problem is that DLookup returns me #Error for fields that are "empty" in Search record. Can I replace this with just an empty string, Nz doesn't work ??

[Lukael]

I solved that also:

=DLookUp("Name";"Search";"ID_Search=" & Nz(Forms![Search]![Search_Subform].Form![ID_Search];0))

Now Nz function works !