DLookup (Access 2000)

[freelance]

I have a table of only 2 fields: clientid and note.This field is memo.I tried
to make a DLookup control in my form but i cannot get error.The form has also the control clientid but is based on another table. What is the
reason ? The unbound control in my form i have written as folllows:
=DLookUp(" <img src=/S/note.gif border=0 alt=note width=20 height=20>";"NotesToContacts";" [clientid]=[Me]![clientid]")

[SteveH]

try this:

=DLookUp("Notes";"NotesToContacts";"[clientid]=" & [Me]![clientid])

[freelance]

Thank you for your reply.This time i get the error "Name?". I must be doing something wrong therefore i am sending an example for help

Thank you

[johnhutchison]

Remove the Me from =DLookUp("Notes";"NotesToContacts";"[clientid]=" & [Me]![clientid])

Me is used in VBA

=DLookUp("Notes";"NotesToContacts";"[clientid]=" & [clientid])

But you should rethink the design.
Should there be multiple notes for each client , or just one?

Your current design, with ClientID as the Key for the table NotesToContacts , means you can only have one.
But if there is only one, you could include the Note directly in tbClients.

If there are to be mutliple Notes for each client, the the table NotesToContacts should have a different primary key NoteID autonumber. In that case the notes should be entered via subform.
A dlookup will display just the first note only, and also the form will not allow you to enter notes if the control is bound to a dlookup.

[freelance]

Thank you for your reply. All your remarks are quite relevant.Of course it is better to have it in the original table, but i would like to keep it under a separate table if it possible,for some reasons.Also, one field for each customer is OK for me. This time i get no errors, but i cannot enter or edit this control.May be it is not possible with the DLookup function ?

[HansV]

A calculated control (that is, a control whose control source is a formula beginning with =) can never be edited. It displays the result of the formula.

[johnhutchison]

If you want to enter the notes via the form, and keep the separate tables, join the two tables in a query and base the form on the query.

[freelance]

This is a very good idea but i cannot build the query.I would like to show all the records fromn the table tblclients and also the corresponding notes from the table NotesToContacts.Could you help me with the relationships?

[HansV]

You must enforce referential integrity for the relationship between the tables. See the attached version.

[freelance]

Thank you very much indeed ! The query is exactly what i want and it will be very useful for me. Thank you !