Results 1 to 5 of 5
  1. #1
    Ruegen's Avatar
    Ruegen is offline VIP
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jul 2013
    Location
    Australia
    Posts
    1,496

    After update puzzle for looking up value that might not be there

    I have a after update event on a combo box that does a dlookup to grab the person that is allocated to the area. Areas are allocated to a school which is related to a bookings table



    using

    BookerIDBonus = DLookup("TourOrganiserID", "tblTourOrganiser", "TourOrganiserID=" & TourOrganiserID)
    BookerIDBonusName = DLookup("MergedNameT", "tblTourOrganiser", "TourOrganiserID=" & TourOrganiserID)
    it pulls in the person from the tblTourOrganiser table.

    However.

    If a school is allocated to an area but no person is allocated to an area yet then there is no id then for it to pull I get an error and runtime shutown (runtime just crashes on errors).

    In access how would I get around this that if I ran into this rare problem it wouldn't crash. How can I get around this? Ask an "if null" ?

  2. #2
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Use Nz().

    Nz(DLookup("MergedNameT", "tblTourOrganiser", "TourOrganiserID=" & TourOrganiserID),"")
    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
    Ruegen's Avatar
    Ruegen is offline VIP
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jul 2013
    Location
    Australia
    Posts
    1,496
    Quote Originally Posted by June7 View Post
    Use Nz().

    Nz(DLookup("MergedNameT", "tblTourOrganiser", "TourOrganiserID=" & TourOrganiserID),"")
    I haven't tested it yet but I am pretty sure that will work... I will give you an update

    thanks for the quick help!

  4. #4
    Ruegen's Avatar
    Ruegen is offline VIP
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jul 2013
    Location
    Australia
    Posts
    1,496
    I was using Nz(DLookup("MergedNameT", "tblTourOrganiser", "TourOrganiserID=" & TourOrganiserID),"")

    instead of

    Nz(DLookup("MergedNameT", "tblTourOrganiser", [TourOrganiserID],"")

  5. #5
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    I am confused by that statement - is it backwards? The second structure should fail.
    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.

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

Similar Threads

  1. Replies: 7
    Last Post: 08-26-2013, 06:05 AM
  2. Iff Puzzle VBA and DLookup
    By Ruegen in forum Forms
    Replies: 5
    Last Post: 08-20-2013, 12:25 AM
  3. VB Strategy Games - Circle Puzzle and Others
    By pkstormy in forum Code Repository
    Replies: 4
    Last Post: 03-05-2011, 03:18 AM
  4. Some neat puzzle visual basic games
    By pkstormy in forum Sample Databases
    Replies: 1
    Last Post: 09-05-2010, 06:57 PM
  5. Unique Puzzle
    By Christina G in forum Database Design
    Replies: 0
    Last Post: 03-14-2009, 06:37 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