Results 1 to 10 of 10
  • Thread Tools
  1. 4 Star Lounger
    Join Date
    Feb 2002
    Posts
    537
    Thanks
    0
    Thanked 0 Times in 0 Posts

    DLookup (Access 2000)

    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]")

  2. Subscribe to our Windows Secrets Newsletter - It's Free!

    Get our unique weekly Newsletter with tips and techniques, how to's and critical updates on Windows 7, Windows 8, Windows XP, Firefox, Internet Explorer, Google, etc. Join our 480,000 subscribers!

    Excel 2013: The Missing Manual

    + Get this BONUS — free!

    Get the most of Excel! Learn about new features, basics of creating a new spreadsheet and using the infamous Ribbon in the first chapter of Excel 2013: The Missing Manual - Subscribe and download Chapter 1 for free!

  3. 4 Star Lounger SteveH's Avatar
    Join Date
    Jan 2001
    Location
    Chelsea, Gtr London, United Kingdom
    Posts
    587
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: DLookup (Access 2000)

    try this:

    =DLookUp("Notes";"NotesToContacts";"[clientid]=" & [Me]![clientid])
    Steve H
    IT Lecturer/Access Developer
    O2K SR3/O2007 SP1; WinXPPro SP3

  4. 4 Star Lounger
    Join Date
    Feb 2002
    Posts
    537
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: DLookup (Access 2000)

    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
    Attached Files Attached Files

  5. Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts

    Re: DLookup (Access 2000)

    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.
    Regards
    John



  6. 4 Star Lounger
    Join Date
    Feb 2002
    Posts
    537
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: DLookup (Access 2000)

    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 ?

  7. Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 13 Times in 13 Posts

    Re: DLookup (Access 2000)

    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.

  8. Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts

    Re: DLookup (Access 2000)

    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.
    Regards
    John



  9. 4 Star Lounger
    Join Date
    Feb 2002
    Posts
    537
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: DLookup (Access 2000)

    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?
    Attached Files Attached Files

  10. Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 13 Times in 13 Posts

    Re: DLookup (Access 2000)

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

  11. 4 Star Lounger
    Join Date
    Feb 2002
    Posts
    537
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: DLookup (Access 2000)

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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •