Results 1 to 10 of 10
  1. #1
    UT227 is offline Expert
    Windows 7 32bit Access 2013 32bit
    Join Date
    Feb 2016
    Posts
    581

    #Error

    I have an unbound text box ([txtTrainer] that gets its information from a dlookup. When the form is new, the box shows #Error. The box works great except for that. It displays what I want it to display. Is there a way to not show that #Error when it's new?

  2. #2
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,825
    What do you mean by 'when the form is new' - what is 'new'?

    Why using DLookup()? Probably better ways to pull related info to form.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #3
    UT227 is offline Expert
    Windows 7 32bit Access 2013 32bit
    Join Date
    Feb 2016
    Posts
    581
    New is when the form has no information and it's a new record. I don't know what you mean by pulling related info to form.

  4. #4
    Minty is offline VIP
    Windows 10 Access 2016
    Join Date
    Sep 2017
    Location
    UK - Wiltshire
    Posts
    3,001
    What is the exact DLookup() you are using? If it references another control on the form and it's not populated then you'll get an error.
    Perhaps use the NZ() around the lookup to provide a value if there isn't one?
    DLookup Syntax and others http://access.mvps.org/access/general/gen0018.htm
    Please use the star below the post to say thanks if we have helped !
    ↓↓ It's down here ↓↓

  5. #5
    UT227 is offline Expert
    Windows 7 32bit Access 2013 32bit
    Join Date
    Feb 2016
    Posts
    581
    In the control source, I have:
    =DLookUp("FullName","qryTrainingScheduleXInstructo rs2","ScheduleID=" & [ScheduleID])

  6. #6
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,825

    Try:
    =DLookup("FullName", "qryTrainingScheduleXInstructors2", "ScheduleID=" & Nz([ScheduleID],0))

    Is there a combobox on form for selecting ScheduleID? If you can associate instructor with ScheduleID in qryTrainingScheduleXInstructors2 then should be able to do that in combobox RowSource. Then instead of DLookup(), textbox can reference combobox column for instructor FullName.


    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  7. #7
    UT227 is offline Expert
    Windows 7 32bit Access 2013 32bit
    Join Date
    Feb 2016
    Posts
    581
    No. The ScheduleID is the primary key. It's not in a combo box. That code worked perfectly. Thanks. I understand the Nz, what is the 0 for after the ScheduleID? Nz([ScheduleID],0)

  8. #8
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,825
    0 is value provided if ScheduleID is Null. You could return anything you want - "None", "N/A", 999999, Date() - depending on field type being searched.

    The argument is optional and if no value specified defaults to empty string.

    I don't understand DLookup to a query with primary key value and primary key is null so how would there be any records with that value? I am confused but if you are satisfied, all good.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  9. #9
    Missinglinq's Avatar
    Missinglinq is offline VIP
    Windows 7 64bit Access 2007
    Join Date
    May 2012
    Location
    Richmond (Virginia, not North Yorkshire!)
    Posts
    3,016
    Quote Originally Posted by UT227 View Post
    No. The ScheduleID is the primary key. It's not in a combo box. That code worked perfectly. Thanks. I understand the Nz, what is the 0 for after the ScheduleID? Nz([ScheduleID],0)
    If ScheduleID is Null (Empty) the function replaces it with a Zero...preventing the error.

    Linq ;0)>
    The problem with making anything foolproof...is that fools are so darn ingenious!

    All posts/responses based on Access 2003/2007

  10. #10
    UT227 is offline Expert
    Windows 7 32bit Access 2013 32bit
    Join Date
    Feb 2016
    Posts
    581
    Thanks for the help. I had to use the primary key to solve another problem. I was asked to add something after the form was completed and was in use for a while. I wanted the quickest easiest way without going into the tables and making a lot of codes. This will work just great. I appreciate the help.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 13
    Last Post: 05-31-2019, 10:48 PM
  2. Replies: 1
    Last Post: 07-26-2016, 06:34 AM
  3. Replies: 3
    Last Post: 01-23-2014, 07:49 AM
  4. Replies: 0
    Last Post: 07-16-2012, 05:42 AM
  5. Replies: 6
    Last Post: 05-30-2012, 12:32 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums