Results 1 to 6 of 6
  1. #1
    namtip is offline Novice
    Windows 10 Access 2016
    Join Date
    May 2019
    Posts
    5

    Displaying records from one table in a subform whose parent form is bound to another table

    Hi all,



    I'm trying to get a subform to display results from its table, but based on criteria from the parent form which is bound to another table.

    The main form "frmJobs" shows information about different jobs in a quality inspection department which is stored in table "tblJobs". There are many subforms on frmJobs, which are all linked to the main form by the job number (which is the primary key in tblJobs) and record various information about the job in other tables (defects, locations, inspector names and times etc.). This all works really well. However I now want to add a subform (sbfrmPart_Competency) which shows who is competent to inspect the job. This information will rely on the part number given in the parent form frmJobs, and will look the part number up in tblPart_Competency and return information about all staff that have that part number.

    I've used a bit of trickery to get this working as best i can, like using =[Parent]![txtPart_Number] in the subform, and DLookups in the VBA behind sbfrmPart_Competency, but I seem to be getting strange results like multiple identical records being shown for just one entry in tblPart_Competency.

    I'm somewhat a beginner and have worked hard to get this far mostly by myself, but I'm getting frustrated that this seemingly simple task has been eluding me for a couple of days now! I wonder if an expert can offer any advice?

    Sharepoint link to the file is available here: https://leveldevelopments-my.sharepo...h2MxQ?e=A5YeEy

  2. #2
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    Haven't looked at the file, but I'd probably either:


    • have the subform bound to a query that uses the main form value in the criteria, and requery the subform in the after update event of the control on the main form
    • manipulate the record source of the subform in the after update event of the main form control, again using the value as the criteria.


    The first is probably simplest.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  3. #3
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Reviewing your db. I get an error "cannot read data in tblPart_Costings … required version to read data is 16.0.7124.1000". It's the only table I cannot open.

    Opening tblInspected_By triggers a popup input prompt because of combobox settings for Inspection_Operation field - referenced field in RowSource does not exist. I NEVER build lookups in table.

    I really don't understand the issue. Subform for compentency has RecordSource and Master/Child links set. Bind controls to fields. Eliminate VBA. The subform automatically updates when navigating main form. Should user be allowed to edit data in this subform?
    Last edited by June7; 05-03-2019 at 10:15 PM.
    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.

  4. #4
    namtip is offline Novice
    Windows 10 Access 2016
    Join Date
    May 2019
    Posts
    5
    Quote Originally Posted by June7 View Post
    Reviewing your db. I get an error "cannot read data in tblPart_Costings … required version to read data is 16.0.7124.1000". It's the only table I cannot open.
    This is likely because I used a multi-value field which I understand isn't very compatible with older versions of access. There's no real reason for me to have this and so I got rid of that feature. The table should now open normally.

    Quote Originally Posted by June7 View Post
    Opening tblInspected_By triggers a popup input prompt because of combobox settings for Inspection_Operation field - referenced field in RowSource does not exist. I NEVER build lookups in table.
    Thanks for the pro tip - there was indeed no need for this, as the form is set up to lookup these values. I see now that the table should just be a data repository and nothing else. I have now removed the lookup from this table.

    Quote Originally Posted by June7 View Post
    I really don't understand the issue. Subform for compentency has RecordSource and Master/Child links set. Bind controls to fields. Eliminate VBA. The subform automatically updates when navigating main form. Should user be allowed to edit data in this subform?
    The user should not be able to edit this form. This form should fetch and display information from tblPart_Competency which looks like this:

    Click image for larger version. 

Name:	tblPart_Competency.PNG 
Views:	11 
Size:	14.5 KB 
ID:	38285

    You'll see that for part number 6003/101 there are 3 entries, i.e. 3 people competent to inspect this part. However in frmJobs, the subform sbfrmPart_Competency shows the following for a record where the part is 6003/101:

    Click image for larger version. 

Name:	sbfrmPart_Competency.PNG 
Views:	11 
Size:	12.7 KB 
ID:	38286

    I want it to display the 3 entries as displayed in tblPart_Competency (the first image)

    Thank you for the help so far, and I hope this clarifies what I'm aiming for.

  5. #5
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    No, I see the 3 different names in the form. Did you try the edits I suggested?

    The form is editable. If you don't want that then set the container control property Locked property to Yes.

    I am using Access 2010 and should be able to handle multi-value fields.
    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.

  6. #6
    namtip is offline Novice
    Windows 10 Access 2016
    Join Date
    May 2019
    Posts
    5
    Quote Originally Posted by June7 View Post
    No, I see the 3 different names in the form. Did you try the edits I suggested?

    The form is editable. If you don't want that then set the container control property Locked property to Yes.

    I am using Access 2010 and should be able to handle multi-value fields.
    Thanks June7! It's all working how it should be! I made the edits you suggested and it all started working. Oddly enough, I'm sure this is how I had it set right at the very beginning when I first made the subform, however I don't think I linked the master/child fields at the time, and so I started barking up the wrong tree by unbinding the controls and using workarounds like [parent!][...] and VBA to try and get the thing to work. I then must have cottoned-on at some point to linking the master/child fields - but never re-bound the controls to the table or ditched the VBA until you told me to.

    It's been a fantastic little journey - I've reached the destination albeit via the scenic route, but have learnt a lot by doing so.

    Thanks again for helping me out.

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

Similar Threads

  1. Replies: 3
    Last Post: 01-24-2019, 02:03 PM
  2. Replies: 56
    Last Post: 05-28-2017, 05:56 PM
  3. Replies: 2
    Last Post: 11-11-2016, 09:07 AM
  4. Replies: 2
    Last Post: 08-24-2015, 02:56 PM
  5. Replies: 5
    Last Post: 03-04-2014, 03:12 PM

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