Results 1 to 9 of 9
  1. #1
    3 Star Lounger
    Join Date
    Jul 2001
    Location
    Minneapolis, Minnesota, USA
    Posts
    299
    Thanks
    0
    Thanked 0 Times in 0 Posts

    DLookup on subform (Access 2000 SP2)

    I'm trying to pull in a customer item from another table to match with our item on a subform. Problem #1 - we need the subform to have the ability to add new records, so joining the two tables doesn't work. Problem #2, if I try a DLookup, it works fine in the subform, but when opening the main form/subform, the field where the customer item number is flashes "#Error" Here's the lookup:

    DLookUp("CustomerItemNumber","T_CustomerItem","[CustomerID] = Forms!F_MickeyTest!CustID AND [ItemNumber] = FormsMickeyTest.Item ")

    I'm linking the mainform/subform fields as CustID;SalesRep;StartDate. Both the CustID and IItem are text fields that match the CustomerID and ItemNumber. Any suggestions? <img src=/S/help.gif border=0 alt=help width=23 height=15>
    Carpy Diem, it&#39;s .

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: DLookup on subform (Access 2000 SP2)

    A subform is not part of the Forms collection. You must always refer to a subform through its parent form (the main form). Say that the parent form is named frmMain. The expression would become:

    =DLookUp("CustomerItemNumber","T_CustomerItem","[CustomerID] = Forms!frmMain!F_MickeyTest!CustID AND [ItemNumber] = Forms!frmMain!F_MickeyTest!Item")

    (I assume that the omission of ! and F_ between Forms and MickeyTest in the last part was just a typo.)

  3. #3
    3 Star Lounger
    Join Date
    Jul 2001
    Location
    Minneapolis, Minnesota, USA
    Posts
    299
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: DLookup on subform (Access 2000 SP2)

    Hans, thanks for responding so quickly and yes, the missing ! was a typo as a result of trying to disguise the real name of the field with my usual "mickey" references.

    I tried referencing the main form for the CustId, but unfortunately, the Item is not available on the main form, as the usage is to set up the main customer information and then put in the detail (sort of like the Orders on Northwind). Everything was working fine until the end user requested this additional field. That's why I was hoping a DLookup would work because otherwise I fear I'll be querying myself to death trying to anticipate how items will be entered for a particular customer.
    Carpy Diem, it&#39;s .

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: DLookup on subform (Access 2000 SP2)

    You don't need CustID on the main form. If you look closely at the expression I posted, you will see that it refers to CustID on the subform, but through the main form:

    <font color=red>Forms</font color=red>!<font color=448800>frmMain</font color=448800>!<font color=blue>F_MickeyTest</font color=blue>!<font color=magenta>CustID</font color=magenta>

  5. #5
    3 Star Lounger
    Join Date
    Jul 2001
    Location
    Minneapolis, Minnesota, USA
    Posts
    299
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: DLookup on subform (Access 2000 SP2)

    Sorry, Hans - I guess either I need another <img src=/S/coffeetime.gif border=0 alt=coffeetime width=32 height=48> or a couple more hours of <img src=/S/snore.gif border=0 alt=snore width=32 height=15> . I'll try this and let you know how it goes.
    Carpy Diem, it&#39;s .

  6. #6
    3 Star Lounger
    Join Date
    Jul 2001
    Location
    Minneapolis, Minnesota, USA
    Posts
    299
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: DLookup on subform (Access 2000 SP2)

    Sorry, Hans, this didn't work. I still got the "#error" message. The odd thing is when I tried to do a update query on the customer item, I got the "use updateable query" message until I manually imported the table, which is residing in a SQL database. Importing the table sadly did not improve the DLookup() problem. We don't want to be importing this table every time the user opens the application, so I'd like the DLookup() to work but am unsure what to do next. The following is the real code:

    <font face="Comic Sans MS">DLookUp("CustomerItemNumber","dbo_ZNW_CustItem ","[CustomerID] = Forms!F_CustQuotesNew!F_CustQuotesNewSubTest!CustI D AND [ItemNumber] = Forms!F_CustQuotesNew!F_CustQuotesNewSubTest!Item" )</font face=comic>

    Maybe I need quotes around the fields or something?
    Carpy Diem, it&#39;s .

  7. #7
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Sint Niklaas, Belgium
    Posts
    2,778
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: DLookup on subform (Access 2000 SP2)

    Is F_CustQuotesNewSubTest the name of the form ?
    You have to use the name of the subform control and not the name of the subform. These are not necessary the same.
    Francois

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: DLookup on subform (Access 2000 SP2)

    Francois has a good point - you MUST use the name of the subform as a control on the main form; check by opening the main form in design view and clicking once on the subform (not twice!), then looking at the Name property in the Other tab of the Properties window.

    If that doesn't help, try this:

    =DLookUp("CustomerItemNumber","dbo_ZNW_CustItem","[CustomerID] = " & Chr(34) & Forms!F_CustQuotesNew!F_CustQuotesNewSubTest!CustI D & Chr(34) & " AND [ItemNumber] = " & Chr(34) & Forms!F_CustQuotesNew!F_CustQuotesNewSubTest!Item & Chr(34))

    again with the name of the subform as a control on the main form.

  9. #9
    3 Star Lounger
    Join Date
    Jul 2001
    Location
    Minneapolis, Minnesota, USA
    Posts
    299
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: DLookup on subform (Access 2000 SP2)

    To Hans and Francois,

    It finally worked! After reading your responses a few hundred times I finally noticed that in my attempts to test out the subform, the Dlookup was looking for the name of the subform (F_CustQuotesNewSub) and I had the reference to the subform as F_CustQuotesNewSubTest . So I renamed the F_CustQuotesNewSubTest form to F_CustQuotesNewSub, and when I opened up the main form, all the customer items showed up. Boy, do I feel silly. Anyway, thanks so much for your quick replies (and patience). <img src=/S/hailpraise.gif border=0 alt=hailpraise width=27 height=22>
    Carpy Diem, it&#39;s .

Posting Permissions

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