Page 1 of 2 12 LastLast
Results 1 to 15 of 28
  1. #1
    GregShah is offline Advanced Beginner
    Windows 7 32bit Access 2007
    Join Date
    Feb 2012
    Location
    Canton, Ohio
    Posts
    60

    Dlookup works in subform but not in Main Form

    I created a form and subform. In the subform, I used dlookup to find the current price of the item. When I just open the subform alone, the current price is displayed accurately. However, when I open the main form, I get a error #Name? Everything else displays properly.

    The dlookup expression is=DLookUp("[current price]","shCID","[scid]=""" & [Forms]![po detail]![scid] & """")

    I cannot figure out why it works independently but not in the master form.



    Thanks,

    Greg

  2. #2
    RuralGuy's Avatar
    RuralGuy is offline Administrator
    Windows 10 Access 2013 32bit
    Join Date
    Mar 2007
    Location
    8300' in the Colorado Rocky Mountains
    Posts
    12,922
    I suspect your "subform" is named [po detail]. If so then that will be a problem since SubForms do not appear in the Forms collection.

  3. #3
    CJ_London is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    if your control scid is in the same form as your dlookup code (i.e. the dlookup code is in the 'po detail' form), all you need is

    .............","[scid]=""" & me.scid & """")

  4. #4
    ssanfu is offline Master of Nothing
    Windows XP Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    In addition:
    There is a special way to refer to subforms and sub-subforms.

    See Refer to Form and Subform properties and controls
    http://access.mvps.org/access/forms/frm0031.htm

  5. #5
    GregShah is offline Advanced Beginner
    Windows 7 32bit Access 2007
    Join Date
    Feb 2012
    Location
    Canton, Ohio
    Posts
    60
    Ran into problems. Now nothing is working. Here are more details.

    I am pulling the "current price" from a query called shcid. This query has a field for the "CID" and the "current price"

    My form/subform is called "job" and "podetail subform." The only field on the main for is "job" and the fields on the subform are "qty", a combo box whick lists all of the CID numbers and the dlookup to pull in the current price from the query.

    Now I am not getting any data pulled . Been trying various configurations but none are working. Can you help out a rookie?

    Thanks,

    Greg

  6. #6
    RuralGuy's Avatar
    RuralGuy is offline Administrator
    Windows 10 Access 2013 32bit
    Join Date
    Mar 2007
    Location
    8300' in the Colorado Rocky Mountains
    Posts
    12,922
    This scheme will be *very* slow if the SubForm has many records once you get it to work. Have you tried using a query with a join for the record source of the subform?

  7. #7
    ssanfu is offline Master of Nothing
    Windows XP Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    I would think Ajax's solution would work.

    So you have a query to get the current price. What is the name of the field for "CID"?

    The subform has a combo box for "CID". What is the name of the combo box?

    It appears that the "CID" field type is Text.
    Code:
    =DLookUp("[current price]","shCID","[scid] = '" & Me.scid & "'")
    should work IF the query has a field named "scid" and IF the subform has a control for the CID that is named "scid".

  8. #8
    GregShah is offline Advanced Beginner
    Windows 7 32bit Access 2007
    Join Date
    Feb 2012
    Location
    Canton, Ohio
    Posts
    60
    Not sure what you mean. I need to pull over the current cost for the specific CID into the subform. I can multiply by qty but would like to get a total of the set of CIDs. I know that this requires some manipulating of data but I have successfully completed this before.

    Can you simplify a bit for me?

    Thanks,

  9. #9
    RuralGuy's Avatar
    RuralGuy is offline Administrator
    Windows 10 Access 2013 32bit
    Join Date
    Mar 2007
    Location
    8300' in the Colorado Rocky Mountains
    Posts
    12,922
    What relationship does the "shCID" table have with the Record Source of the SubForm?

  10. #10
    GregShah is offline Advanced Beginner
    Windows 7 32bit Access 2007
    Join Date
    Feb 2012
    Location
    Canton, Ohio
    Posts
    60
    The only relationship I created is the Job table and the POdetail table. I linked the Job in each table, one to many.

  11. #11
    RuralGuy's Avatar
    RuralGuy is offline Administrator
    Windows 10 Access 2013 32bit
    Join Date
    Mar 2007
    Location
    8300' in the Colorado Rocky Mountains
    Posts
    12,922
    What is the Record Source of the Sub Form and what field (if any) does it share with the "shCID" table?

  12. #12
    GregShah is offline Advanced Beginner
    Windows 7 32bit Access 2007
    Join Date
    Feb 2012
    Location
    Canton, Ohio
    Posts
    60
    The record source for subform is podetail. The shared records are cid and current price.

    I want to be able to select a cid and then have the current price pulled inot the subform.

  13. #13
    RuralGuy's Avatar
    RuralGuy is offline Administrator
    Windows 10 Access 2013 32bit
    Join Date
    Mar 2007
    Location
    8300' in the Colorado Rocky Mountains
    Posts
    12,922
    Create a Query with both tables and join them on the CID field. You should then be able to select fields from either table as if they were in one table.

  14. #14
    GregShah is offline Advanced Beginner
    Windows 7 32bit Access 2007
    Join Date
    Feb 2012
    Location
    Canton, Ohio
    Posts
    60
    No progress at all but lots of frustration.

    Here's what I am trying to do. I have a long list of CID's which are items my customers purchase. These items each have a standard base price but the actual selling price is based on an index which changes. So I use a query to reset the selling price based on the proper index.

    Then I want to create a form/sub form. The form will have the main details about the job we have opened to make all of the items such as my job number, delivery date etc. The sub form will list the quantity, CID number and the correct price. I then want to total the items on the subform to make sure my form/subform final pricing matches my customer's PO.

    So on the subform, I plan on using a combo box to make selection of the CID easier. I then need a good method for the subsform to go the specific CID and pull the latest price from my query. Sounds very simple but it has be a PITA for me. I don't have much experience but I have always been able to get through any issues with help for people in this forum.

    Been spinning my wheels for a few days and need a push in the right direction.

    Thanks,

    Greg

  15. #15
    RuralGuy's Avatar
    RuralGuy is offline Administrator
    Windows 10 Access 2013 32bit
    Join Date
    Mar 2007
    Location
    8300' in the Colorado Rocky Mountains
    Posts
    12,922
    Any chance you could post your DB? Compact an Repair and zip it first if you can. Remove any sensitive data of course but leave enough sample data to show the issue.

Page 1 of 2 12 LastLast
Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 3
    Last Post: 03-26-2015, 06:50 PM
  2. Replies: 2
    Last Post: 12-07-2014, 10:03 PM
  3. Replies: 1
    Last Post: 11-14-2014, 05:12 PM
  4. Replies: 6
    Last Post: 08-20-2014, 06:43 PM
  5. Form works, Subform gets error
    By jlclark4 in forum Forms
    Replies: 9
    Last Post: 01-03-2011, 09:24 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