Results 1 to 5 of 5
  1. #1
    4 Star Lounger
    Join Date
    Dec 2002
    Location
    Kitchener, Ontario, Canada
    Posts
    421
    Thanks
    0
    Thanked 0 Times in 0 Posts
    In the details section of the form frmISAPSubMeetingsDetails I have a textbox Text12 that takes the numeric value from a hidden textbox Text10 and converts it to a text value through Dlookup. In other versions of this form that are identical except for a couple of new fields that have been added to this version (Bring Forward and comp) I do not get #error in the blank/empty record at the end of the set.

    I would be grateful if anyone can tell me why it is popping up here. I can't for the life of me figure out what is causing it.
    Attached Files Attached Files

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    You can delete Text10 and set the Control Source of Text12 to

    =DLookUp("[ServiceAreaDetails]";"tblLookupISAPServiceAreaDetails";"[ServiceAreaDetailsID] = " & Nz([ServiceAreaDetails];0))

    Note that Nz is not used for the DLookup function here but for ServiceAreaDetails, to convert Null to 0. Without Nz, the condition would evaluate to

    [ServiceAreaDetailsID] =

    if ServiceAreaDetails (or Text10) is Null, but that is not a valid condition.

    (BTW, what do you need the text box for? The combo box would appear to be sufficient in this situation)

  3. #3
    4 Star Lounger
    Join Date
    Dec 2002
    Location
    Kitchener, Ontario, Canada
    Posts
    421
    Thanks
    0
    Thanked 0 Times in 0 Posts
    [quote name='HansV' post='780405' date='17-Jun-2009 20:17']You can delete Text10 and set the Control Source of Text12 to

    =DLookUp("[ServiceAreaDetails]";"tblLookupISAPServiceAreaDetails";"[ServiceAreaDetailsID] = " & Nz([ServiceAreaDetails];0))

    Note that Nz is not used for the DLookup function here but for ServiceAreaDetails, to convert Null to 0. Without Nz, the condition would evaluate to

    [ServiceAreaDetailsID] =

    if ServiceAreaDetails (or Text10) is Null, but that is not a valid condition.

    (BTW, what do you need the text box for? The combo box would appear to be sufficient in this situation)[/quote]

    I added the Nz to the DLookup this afternoon before posting to see if it would make a difference and forgot to remove it. The setup is as it is because I've been using the form this way for at least 4 years with no problem, not necessarily because it is the most efficient way to do it...

    As for the text box, it is there because I am using continuous forms and the data disappears when you go to the next record and the combos in the previous record are then empty. It could have something to do with the fact that the recordsources are actually populated through code, not as I presented them in this stripped down version. I only gave you the bits of the form that were causing the problem.

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    OK, I see why you need Text12. But have you tried the modified control source that I proposed?

  5. #5
    4 Star Lounger
    Join Date
    Dec 2002
    Location
    Kitchener, Ontario, Canada
    Posts
    421
    Thanks
    0
    Thanked 0 Times in 0 Posts
    [quote name='HansV' post='780430' date='18-Jun-2009 01:26']OK, I see why you need Text12. But have you tried the modified control source that I proposed?[/quote]

    I haven't had a chance to yet. Oddly enough, as I was preparing this for the client, the #error stopped showing up. I will get back to if #error starts showing up again and your modified control source isn't working. I don't see any reason why it wouldn't.

Posting Permissions

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