Results 1 to 5 of 5
  1. #1
    PDilly is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Jan 2019
    Posts
    78

    getting #Name? in a nested subform!!

    I have a puzzle!
    Main Form is Member_entry and data source is Member details table (has one to many relationship with Angling details table). PERMNO is primary key for member table. It corresponds to APERMNO field in Angling table and V_member in voucher table. i.e they are the same number.

    Subform 2 is inside Main form and it’s Data source is standalone Voucher table
    SELECT Vouchers.V_number, Vouchers.V_member
    FROM Vouchers
    WHERE (((Vouchers.V_member)=[Member_details].[PERMNO]));

    Only a text box is displayed in subform 2 with the code:
    =IIf([Forms]![Member_entry]![PERMNO]=[V_member],"VOUCHER " & [V_number] & " PURCHASED","")

    THIS WORKS FINE! It does exactly what it should by displaying a voucher with a record if a voucher exists.

    But similar coding in subform 3 doesn’t work

    Subform 1 data source is angling details table
    Subform 1 is inside Main form
    Subform 3 is inside Subform 1
    SELECT Vouchers.V_number, Vouchers.V_member
    FROM Vouchers
    WHERE (((Vouchers.V_member)=[Angling_details]![APERMNO]));

    Again, only a textbox is displayed


    =IIf([Forms]![Member_entry_subform1]![APERMNO]=[V_member],"VOUCHER " & [V_number] & " PURCHASED","")

    The values of APERMNO are always available in subform 1 as the records change. The values of V_member and V_number in subform 3 also change as the records change in the main form and subform 1 but APERMNO just gives #Name?
    I don’t actually want the same code, but if I can get that to work, I can alter it to what I need

    Can anyone tell me what I’m missing please.

  2. #2
    orange's Avatar
    orange is online now Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    I suggest you post a copy of the database (zip format) with only a few records that demonstrate the issue. Anonymize anything private/confidential.

    This link re form/subform control references is considered a great resource.

    Good luck.

  3. #3
    PDilly is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Jan 2019
    Posts
    78
    As suggested depersonalised DB attached.
    subform in question is bottom right data entry screen
    Two records have vouchers, two don't
    "Voucher Purchased" appears in bottom left subform (which is only visible when true) but not in the bottom right
    Its just a question of passing a value from one subform to another as my OP

    any help welcomePDilly example database.zip

  4. #4
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,822
    Query criteria makes no sense. Angling_details is a table, not a form. Can't reference table directly like that. Even if could, Access has no idea which record to use.

    Filtering a subform based on non-key value in a higher form is not easy. Be aware, subforms load before higher forms, yes, that seems weird, but it's true. Form/subform synchronization should be managed with Master/Child Links.

    You have defined primary key in Lookup_type table yet you are not saving that value as foreign key in related table. Defined primary key should be saved as foreign key.



    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  5. #5
    PDilly is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Jan 2019
    Posts
    78
    Quote Originally Posted by June7 View Post
    Query criteria makes no sense. Angling_details is a table, not a form. Can't reference table directly like that. Even if could, Access has no idea which record to use.

    Filtering a subform based on non-key value in a higher form is not easy. Be aware, subforms load before higher forms, yes, that seems weird, but it's true. Form/subform synchronization should be managed with Master/Child Links.

    You have defined primary key in Lookup_type table yet you are not saving that value as foreign key in related table. Defined primary key should be saved as foreign key.



    Yep I'm just getting too old for this sort of thing. The DB has some issues but it works. I'd forgotten Child/master links. I'll work on that. Thanks

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

Similar Threads

  1. Nested Loop Not working correctly in Subform
    By canela123 in forum Programming
    Replies: 4
    Last Post: 11-25-2017, 01:48 AM
  2. Limit number of entries into nested subform?
    By shadowbh2 in forum Programming
    Replies: 7
    Last Post: 11-28-2014, 11:03 PM
  3. Replies: 2
    Last Post: 05-09-2014, 03:01 PM
  4. Nested Subform's control events
    By amrut in forum Forms
    Replies: 3
    Last Post: 01-10-2013, 02:19 PM
  5. Delete record from nested subform
    By raton in forum Forms
    Replies: 1
    Last Post: 04-14-2012, 11:39 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