Results 1 to 6 of 6
  1. #1
    Abacus1234 is offline Competent Performer
    Windows Vista Access 2010 32bit
    Join Date
    Nov 2011
    Posts
    212

    DLookup in unbound control

    When the source record for an unbound control on a form using a DLookup is deleted, it causes a flashing #Error. Is their any protection against this?

    Thanks for any comments.

  2. #2
    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
    That's going to happen, if Access cannot evaluate the expression (in this case the DLookup function) used as Control Source. You haven't given much in the way of exactly what you're doing, here, but the only thing I can think of, off the top of my head, would be to

    1. Add a Field to the underlying Table
    2. Bind it to the currently Unbound Control
    3. Use the DLookup as the Default Value, instead of as the Control Source.

    Maybe someone else will have a better idea!

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

    All posts/responses based on Access 2003/2007

  3. #3
    Abacus1234 is offline Competent Performer
    Windows Vista Access 2010 32bit
    Join Date
    Nov 2011
    Posts
    212
    Thanks for the suggestion. The timing is wrong to use the default value. I open the main data entry form, then open another form while that form is open to enter the data I need to look up, then close that form, back to the main data entry form.

    Is there anyway I can test evaluating the expression, and only go ahead and evaluate if I know it is there?

    I have many users of this new system. I have 2 different tables that essentially have the same data in. So instead of asking people to add the same data to both tables, I want to copy the contents of one table to the other table. In so doing, I will be changing data that has already been used in previous records. This will cause the #error message. The whole system goes haywire at this stage, the #error message flashes and nothing works, so I have to close the database. So I hope their is a way to skin this cat!

  4. #4
    JamesDeckert is offline Competent Performer
    Windows 7 64bit Access 2013
    Join Date
    Jul 2015
    Location
    Salina, KS
    Posts
    262
    >>I have 2 different tables that essentially have the same data in.
    >>
    I want to copy the contents of one table to the other table.
    This looks like a headache waiting to happen. Are the tables normalized? This is the first step in creating forms which work well.
    What is the purpose of the two tables? Can you provide a little of what you are trying to accomplish with the tables?

  5. #5
    Abacus1234 is offline Competent Performer
    Windows Vista Access 2010 32bit
    Join Date
    Nov 2011
    Posts
    212
    After the fact, I realized that the 2 tables essentially contain the same people. No one wants to do the double the data entry. So I want one table to be the feed to both sources of data. So at ay one time, a person in the table can be either a or b in the transaction.

  6. #6
    Abacus1234 is offline Competent Performer
    Windows Vista Access 2010 32bit
    Join Date
    Nov 2011
    Posts
    212
    I solved the problem with #Error by putting the DLookup in an IIf statement. I said IIf IsNull Dlookup, 0, Dlookup. If the value cannot be evaluated I get a 0 which is fine. They can just choose another value which is there.

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

Similar Threads

  1. Formatting an Unbound Control on a Report
    By Sherrydahlmontee in forum Reports
    Replies: 4
    Last Post: 08-19-2014, 04:56 PM
  2. Replies: 4
    Last Post: 05-31-2013, 07:00 PM
  3. Formatting Unbound Control
    By SltPhx in forum Forms
    Replies: 12
    Last Post: 08-17-2011, 01:49 PM
  4. Replies: 1
    Last Post: 07-11-2011, 04:35 AM
  5. Using Dlookup in unbound text box in report
    By PrintShopSup in forum Reports
    Replies: 3
    Last Post: 12-27-2010, 10:29 AM

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