Results 1 to 6 of 6
  1. #1
    Join Date
    Aug 2007
    Posts
    11

    Unanswered: DLookup as ControlSource

    I'm looking for help with using the DLookup function as a controlsource for a control on a subform. It's looking like a fairly common problem, but I can't find a solution in any previous threads. Right now my controlsource reads:

    = DLookup("[Current Revision]", "[Drawing Log]", "[Drawing Number] =" & [Forms]![DCNs]![DCN Details subform].[Drawing Number])

    When this is entered as the controlsource, I get an error that "The object doesn't contain the Automation object "Drawing Number" ". There IS a control on the subform with both datasource and name "Drawing Number". I also tried just

    = DLookup("[Current Revision]", "[Drawing Log]", "[Drawing Number] =" & [Forms]![DCN Details subform].[Drawing Number])

    But it reads that the subform isn't open

    and also

    = DLookup("[Current Revision]", "[Drawing Log]", "[Drawing Number] =" & [Me].[Drawing Number])

    And it reads the same 'Automation" error but for the objst 'Me'

    All three of these return the frustrating "#Name?" in the text box, which I know means that the function isn't working properly.

    To be explicit, I need this text box to look up a value from a table that is not represented in the form, but has a One-To-Many relationship with the table that the subform is based on.


    ( P.S.
    To be even more frustrating and perplexing, when I tried to look up the data by another criteria, this one coming from a control on the parent form, the DLookup was:

    =DLookUp("[Current Revision]","[Drawing Log]","[Last Date]=" & [Forms]![DCNs]![DCN Date])

    And THIS Dlookup function worked, but returned only a blank box, meaning it found no records, though I triplechecked that the data was in synch. Same
    when I used the Parent syntax. )

    Thanks in advance ...
    Dube

  2. #2
    Join Date
    Aug 2007
    Posts
    11

    Sorry, Before You Reply

    I noticed the error that the period before the control name should be an exclamation point ([DCN Details subform]![Drawing Number]) And fixed it, but now it's a NEW ripple! It returns an error! It "Can't find the automation object 'TEST10'(one of my test drawings)"

    So now the text box reads #Error and is constantly flashing, which I guess means it keeps trying to calculate it.

    Sorry about the flub!

  3. #3
    Join Date
    Jul 2003
    Location
    Michigan
    Posts
    1,941
    For your second problem, try this:

    =DLookUp("[Current Revision]","[Drawing Log]","[Last Date]=#" & [Forms]![DCNs]![DCN Date] & "#")


    I avoid setting the control name to the same thing as the field name.
    It's also a good idea to avoid spaces in field names. Try changing the name of the Drawing Number control to txtDrawingNum, and then change your formula to:
    = DLookup("[Current Revision]", "[Drawing Log]", "[Drawing Number] =" & [Forms]![DCN Details subform]!txtdrawingnumber)
    Inspiration Through Fermentation

  4. #4
    Join Date
    Aug 2007
    Posts
    11
    Thanks RedNeck, Your correction for the date criteria worked and returns the data, but I only used that as a test, because there will theoretically be many different drawings with the same revision date. It's a bummer because it is working, but I really do need to look up by the field Drawing Number. I tried just using the criteria "[Drawing Number] = TEST10" and it still returned the same error

    "The expression you entered as a query parameter produced this error: 'The object doesn't contain the Automation object 'TEST10."

    This doesn't make sense, though. It means that it is retrieving the value from the control alright, but not using it the right way, even though it used the Date correctly with your tip. I tried entering empty quotes where the "#" was, since this was a tip in a previous thread, but it didn't change anything. I did as you advised and got rid of the duplicate names, so now it reads:

    =DLookUp("[Current Revision]","[Drawing Log]","[Drawing Number]=" & [Forms]![DCNs]![DCN Details subform]![txtdrawingnumber] & "")

    When I tried just entering "[Drawing Number] = TEST10" it still returned the same error, but I know that there is a drawing with that drawing number in the table!

    I looked up what the 'Automation object' means, and it seems like nothing here should apply to it, so why is that my error looking up by one field, but looking up by another works?! (not to mention one that doesn't help)


  5. #5
    Join Date
    Jul 2003
    Location
    Michigan
    Posts
    1,941
    So drawing number isn't really a number?
    If it's really a text field, then you need to use quotes

    = DLookup("[Current Revision]", "[Drawing Log]", "[Drawing Number] ='" & [Forms]![DCN Details subform]!txtdrawingnumber & "'")
    Last edited by RedNeckGeek; 08-14-07 at 12:04.
    Inspiration Through Fermentation

  6. #6
    Join Date
    Aug 2007
    Posts
    11
    Thank you so much, RedNeck!!

    It works now with the quotes. I had tried several other ways to try and get quotes in there since I had a hunch that I needed to identify the data in some way, but hadn't hit on the correct one, so thanks for your help! You have no idea how long I've been wrestling with this problem, reconstructing my forms trying to get this lookup/edit process to work the right way, and now it does!

Posting Permissions

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