Results 1 to 12 of 12
  1. #1
    Join Date
    Jun 2020
    Posts
    6

    Sub forms and sub-sub forms

    Afternoon, I have a project in access 2019 where I have 3 combo boxes that update a sub list box - this sub box contains a summary of the product cost along with the supplier so I can compare and decide who’s the cheapest but I want to be able to click on each record in this list and have it update a further sub box, so I can get a break down of what the ‘summary cost’ is made of.
    Is this possible and how do I do this? Thanks

  2. #2
    kd2017 is offline Well, I tried at least.
    Windows 10 Access 2016
    Join Date
    Jul 2017
    Posts
    1,142
    In your first listbox, on the after update event, you would have to write some vba to update the second listbox. It's pretty straight forward but we'll need details of your project, forms, table names, field names, etc. to much more help. You can zip and upload your db.

  3. #3
    Join Date
    Jun 2020
    Posts
    6
    At the moment I can’t as it contains confidential data, if I list the names will that work?

  4. #4
    kd2017 is offline Well, I tried at least.
    Windows 10 Access 2016
    Join Date
    Jul 2017
    Posts
    1,142
    You can make a copy of your db and delete the confidential data.

  5. #5
    Join Date
    Jun 2020
    Posts
    6
    I can change it but I’m not sure how to then upload it here, my database is on my works server but there is limited internet access, forums are on the banned list so I’m using my iPad currently lol

  6. #6
    kd2017 is offline Well, I tried at least.
    Windows 10 Access 2016
    Join Date
    Jul 2017
    Posts
    1,142
    Give my suggestion on post #2 a try and let us know if you get hung up anywhere or have any specific questions.

  7. #7
    Join Date
    Jun 2020
    Posts
    6
    Hello

    I don't know the vba hence asking? hopefully I've uploaded the attachment correctly
    Attached Files Attached Files

  8. #8
    Join Date
    Jun 2020
    Posts
    6
    Hi again, sorry any ideas? I have tried googling linking the subform2 to the record ID of subform1 but this doesn't seem to work, I'm a complete newbie to access so any help would be appreciated.

  9. #9
    kd2017 is offline Well, I tried at least.
    Windows 10 Access 2016
    Join Date
    Jul 2017
    Posts
    1,142
    Sorry Satan, I didn't notice your reply.

    I just looked at your db and no vba is necessary.

    Step #1. Add a text box to the FAK Search Form and name it something like txtRateID
    Step #2. In the new text box data properties tab set the Control Source to =[AgentRateTQSUb].[Form]![RateID] (That is the RateID field of the first subform)
    Step #3. Modify the record source query of the second subform, AgentRateT subform, to include the RateID field
    Step #4. In the AgentRateT subform data properties tab set the Link Master Fields to txtRateID (or what ever you named the textbox in step 1), and set the Link Child Fields to RateID
    Step #5. Go get a beer

    Give this a read: https://www.fmsinc.com/microsoftacce...edSubforms.asp

  10. #10
    ssanfu is offline Master of Nothing
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    I know kd2017 provided a solution, but I wanted to show a different form design.
    I used the same method that kd2017 provided to link the main form and the sub form, but I only used one sub form.

    Note that I changed some field names and changed (removed) the LOOK UP FIELDS in the tables (not the fields, just the look up part). I also removed spaces in object names.
    The combo boxes for filtering were BOUND - by changing a filter combo box, it looks like it would be changing the value of a record.

    I changed the filter combo boxes to be unbound and added code to clear individual combo boxes or all combo boxes at once.


    In forms, I use a light yellow background to denote hidden controls.


    As I stated, this is how I design search forms.....
    Attached Files Attached Files

  11. #11
    Join Date
    Jun 2020
    Posts
    6
    That's brilliant thanks I'll try both!

    Thanks ssafu that's a really nice design! One question though in the AgentRateT it only shows numbers, is there a way to change it back to show names as there are going to be thousands of different combinations and I'll never remember all the numbers to names!

  12. #12
    ssanfu is offline Master of Nothing
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    You should never allow data to be added directly into a table. That is what forms are form.
    See The evils of lookup fields

    You don't have to remember numbers to names - use combo boxes on on forms. I had approx 4,000 names to numbers..... never had a look up FIELD in a table!

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

Similar Threads

  1. Replies: 7
    Last Post: 11-06-2017, 01:23 PM
  2. Replies: 2
    Last Post: 04-05-2016, 08:29 AM
  3. Replies: 3
    Last Post: 03-23-2016, 12:45 PM
  4. Replies: 2
    Last Post: 11-05-2014, 09:16 AM
  5. Replies: 4
    Last Post: 04-01-2009, 08:49 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