Page 1 of 3 123 LastLast
Results 1 to 15 of 31
  1. #1
    Join Date
    Jun 2013
    Posts
    81

    Unanswered: MS Access 03: Linking Junior and Senior Subforms

    Hi All, quick question for you.

    Is it possible to have two subforms within a form, where you chose which entry to view in the second subform from the first subform (if that makes any sense)?

    I've created a form for entering generic set information, I've then created a subform which gives a list of all the equipment within that set (for example you might have a list of weights, which will show the serial number (if there is one - note: this is not the primary key), the denomination of the weight and the prescribed limit of error for the weight, this form can't be altered or added to - the primary key is included in this list, but it isn't visible. I then have a third subform which shows more in depth information regarding the weight. At the moment the subform will show all weights in the set, individually as you click through the records using the navigation bar. However, I would like to be able to click on a particular weight in the list and have the information regarding that weight displayed in the subform below.

    I've been searching round but so far am struggling to find anything. I would appreciate a point in the right direction.
    Last edited by SamJP; 07-12-13 at 06:31. Reason: Title alteration

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    yes
    you can also manage which forms are displayed using a tabbed dialog
    say your top table defines an equipment type
    then dependent on some column within that row you decide which child dialog to show
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    Jun 2013
    Posts
    81
    Ahaa excellent thanks. I'll have a dig round see if I can work out how to do that, if not I'll be back again. Anything to get away from blasted dlookups, I will work it out if it kills me lol.

  4. #4
    Join Date
    Jun 2013
    Posts
    81
    Ok I'm starting to get frustrated now. I would really appreciate a bit more help with this.

    From what I understand a tabbed dialog is another way of displaying the data, so instead of a single form you have a form with tabs (which I love, its much neater) - although I really need to neaten up the subforms on the tabs.

    I've been searching round and found a suggested VBA code for opening the weight record, by double clicking on the entry in the weight list. It sort of worked and sort of didn't. In trying to fix that I came across a reference to synchronizing the sub forms (both of which are based on the Weight table), so the weight list has a master link of SetID from the Set form, and a child link of Set ID from the Weight List from, then the Weight Record form has a master Link of Weight ID from the Weight List form and a child link of Weight ID from the weight record form. Well to put it simply the only master link it wants to let me use is for the set form.

    Ideally I would like to be able to click on an entry within the Weight list subform (probably double click) and have the programme open up the 3rd tab (which holds the weight record subform) and display the weight record for that entry (e.g. if you double click on the 2kg in the weight list it will display the full record for the 2kg weight in the Weight record tab.

  5. #5
    Join Date
    Jun 2013
    Posts
    81
    I've had a thought (yes it did hurt lol) - would the fact that my two subforms (frmWeight and frmWeightList) are both based on the same table (tblWeight) effect the master child links.

    I've been doing my utmost to set the master child link for frmWeight to WeightID (a field which doesn't appear in other form or table - frmSet and tblSet). The only options Access will give me however are for fields from the Set table/form.

    I would really appreciate some help with this. Thanks.

  6. #6
    Join Date
    Nov 2011
    Posts
    413
    If the forms record source does not have a matching ID to link to then it will not show up.It should have a matching ID for it to work. WeightID to WeightID for example.Take a look at your table(s) and see what fields are available to link on. Generaly if there is a matching ID, Access automatically links it for you.

  7. #7
    Join Date
    Jun 2013
    Posts
    81
    Hi Burrina thank you for your comment, I really appreciate it.

    The main forms record source is tblSet, which doesn't contain a field called WeightID but does contain a field call SetId (pk). The two suforms (subfrmWeightList and subfrmWeight) both have a record source of tblWeight, which contains a field called WeightID and SetID. As I'm attempting to link the 2 subforms together (via the Weight ID - pk) I'm having problems as Access only seems to be seeing tblSet as the link (which is technically true as both subforms links to that table). Any ideas?

    It is usable without being able to click on a record in the Weight List and have the full record show in the weight subform, but it isn't very user friendly and to be honest I think it will be a pain in the neck (for me when my colleague don't understand)

    For the moment I've gone back to playing with VBA coding, trying different things to see if I can get it to work. As this time I'm actually making a note of what I've tried I may be able to work it out (or word a better question). Thus far I just seem to get Name errors, but I've litterally copied and pasted the form names from the form properties. I can't for the life of me work out where I've gone wrong. I'll post the coding later if I can't work it out. I'm dealing with another job at the moment (thankfully nothing to do with Access lol), but will be back to Access tomorrow at the latest.

  8. #8
    Join Date
    Nov 2011
    Posts
    413

    Main Form With 2 Subforms

    Maybe a little more detail.A pic of your relationships would be helpful.Here is a mock screenshot of what I think your structure should look like.
    Attached Thumbnails Attached Thumbnails MainfrmSubfrms.png  

  9. #9
    Join Date
    Jun 2013
    Posts
    81
    Hi Burrina thanks your response. I really appreciate your help.

    Your not a million miles off with the basic structure, but the weight List form is literally that. It shows a list of all the weights within that set (generally 1kg, 500g, 200g (x2), 100g, 50g, 20g (x2) 10g, 5g, 4g, 3g, 2g, 1g, 500mg, 200mg, 100mg). All it shows for each weight is the serial number (not all weights have this) the weight quantity (e.g. 1kg) and the prescribed limit of error (e.g. 0.1mg). Hidden in the background I've got the Set ID the weight is linked to (e.g. Metric 22) and the Weight ID which is a auto number primary key. The weight form shows essentially the same information (plus a bit more) but for each individual weight and its shown one weight at a time.

    I'm having some major technical issues today (working from home, no work laptop available and no Microsoft access on my own laptop - great start huh). When I get in the office tomorrow I'll post a screen dump of the relationships plus a screen dump of the forms and subforms in question so you can see (hopefully) what I'm attempting to do.

  10. #10
    Join Date
    Jun 2013
    Posts
    81
    Hi Burrina, as requested I've attached a screen dump of my relationships (the PLE's table will be linked in, I've just not worked out how exactly) and a screen dump showing the 3 tabs on the form I'm working on.

    What I'm attempting to do is set the form up so the user can click on a record in the Weight List tab (e.g. 500g) and have the corresponding record displayed in the Weight tab. In the background of the Weight List and Weight subforms there is the tables primary key (WeightID), but as it is meaningless to the user I've hidden it. Please note both the Weight List subform and the Weight subform are based on the table tblWeight.

    Many thanks for your help with this I really appreciate it.
    Attached Thumbnails Attached Thumbnails Relationships.jpg   Form layout.jpg  
    Last edited by SamJP; 07-17-13 at 05:39. Reason: Alter attachment

  11. #11
    Join Date
    Nov 2011
    Posts
    413
    I don't understand the logic behind this.Although you could create a command button or after update event to set focus on the tab and corresponding record.It would make more sense to combine both subforms into one.

  12. #12
    Join Date
    Jun 2013
    Posts
    81
    Sucess (of sorts) finally!!! I've managed to get a bit of code to do almost what I want it to. The code I'm using is linked to the WeightQuantity field on the WeightList form for the Double click event. The code I've used is:

    Private Sub WeightQuantity_DblClick(Cancel As Integer)

    Dim rs As Object
    Dim lngBookmark As Long

    'set a variable to the current record
    lngBookmark = Me.weightID
    'open the new form
    DoCmd.OpenForm "frmWeight"
    'take it to the selected record
    Set rs = Forms!frmWeight.RecordsetClone
    rs.FindFirst "WeightID=" & lngBookmark

    Forms!frmWeight.Bookmark = rs.Bookmark

    End Sub

    The code works brilliantly at opening the Weight form and selecting the correct record, while still allowing you to view other records and add records. The only downside is it opens the form as a pop up rather than bringing up the form on the third tab, as I want it to.

    I would have thought I should use some form of "GoTo" coding instead of the OpenForm coding, but thus far its elluding me. Any suggestions (or suggestions on a better method)?

    I think I have a semi-reasonable understanding of how that piece of coding works, but I'm not 100%. Still working things out in my head.

  13. #13
    Join Date
    Nov 2011
    Posts
    413
    Instead of opening the form, just set focus on the tab first!

  14. #14
    Join Date
    Jun 2013
    Posts
    81
    Ooops sorry hadn't seen your post Burrina.

    I had originally got the two Weight subforms as one single subform as a list. But I found it quite cumbersome, difficult to view and use. The section which will get the most use is the general list of weights (when we come to test the set we can have a quick look to see what weights we should find in the kit - its not unusual for one or two to go walkabout, especially the smaller ones). But we still need to record the other details for the weights for audit purposes. Occasionally we will need to view them when testing

  15. #15
    Join Date
    Jun 2013
    Posts
    81
    Lol my timing with these posts is rather interesting today.

    I had attempted to Set Focus earlier, but suspect I'd got the coding wrong. How do you set focus on a tab (or even refer to it). Most of my google searches find answers relating to the tab key on the keyboard (if that makes sense).

Posting Permissions

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