none
Correlated Subforms - VBA Events RRS feed

  • Question

  • I have multiple "Master Subforms" and multiple "Detail Subforms" (relies on Master Subform) on a main form.  Both are continuous.  I can get the following code to work for the first Master Subform in the Current Event:

    Me.HoistMaster.Top = 288

    If Me![HoistMaster].Form.RecordsetClone.RecordCount > 0 Then
    Me.HoistMaster.Height = (Me![HoistMaster].Form.RecordsetClone.RecordCount * 421) + 864
    Else
    Me.HoistMaster.Height = 0
    End If

    However, when I try the same code for the Detail Subform, things start to go crazy.  The record counts do not work properly, and depending on the variation I try, it may ask for parameters.  I'm needing some direction on how to get the above code to work with multiple controls.  (And if using in Current Event of Main Form is the correct way to do so.)  

    Wednesday, July 26, 2017 3:17 PM

All replies

  • Hi,

    I think it's all about the syntax. If the code is on the main form and you're trying to control the detail subforms inside a master subform, then the syntax might be more like:

    Me.HoistMaster.Form!DetailSubformName.Form.Recordset.RecordCount

    and

    Me.HoistMaster.Form!DetailSubformName.Height

    Hope it helps...

    Wednesday, July 26, 2017 3:29 PM
  • Hi,

    I think it's all about the syntax. If the code is on the main form and you're trying to control the detail subforms inside a master subform, then the syntax might be more like:

    Me.HoistMaster.Form!DetailSubformName.Form.Recordset.RecordCount

    and

    Me.HoistMaster.Form!DetailSubformName.Height

    Hope it helps...

    The Detail Subforms are not inside a Master.  The Master and Detail are two, separate subforms placed onto the Main.  The following code works perfectly when I view record #1, but when I flip thru records, both subforms will only show 1 row of records instead of actual record count:

    Private Sub Form_Current()


    Me.HoistDetail.Top = 792
    Me.HoistMaster.Top = 288

    If Me![HoistDetail].Form.RecordsetClone.RecordCount > 0 Then
    Me.HoistDetail.Height = (Me![HoistDetail].Form.RecordsetClone.RecordCount * 331) + 310
    Else
    Me.HoistDetail.Height = 0
    End If


    If Me![HoistMaster].Form.RecordsetClone.RecordCount > 0 Then
    Me.HoistMaster.Height = (Me![HoistMaster].Form.RecordsetClone.RecordCount * 421) + 864
    Else
    Me.HoistMaster.Height = 0
    End If

    End Sub

    Wednesday, July 26, 2017 3:37 PM
  • Try verifying what the code is seeing by temporarily adding the following lines:

    MsgBox "Master Record Count: " & Me.HoistMaster.Form.RecordsetClone.RecordCount

    MsgBox "Detail Record Count: " & Me.HoistDetail.Form.RecordsetClone.RecordCount

    Do their return more than 1?

    Wednesday, July 26, 2017 3:58 PM
  • Try verifying what the code is seeing by temporarily adding the following lines:

    MsgBox "Master Record Count: " & Me.HoistMaster.Form.RecordsetClone.RecordCount

    MsgBox "Detail Record Count: " & Me.HoistDetail.Form.RecordsetClone.RecordCount

    Do their return more than 1?

    Good catch.  The results for the Master were either 0 or 1.  But for the detail it would pull the correct number for the corresponding Master.  Do you know why it's not pulling the correct record count for the Master?  It's identical code for both Master and Detail (just different control names).
    • Edited by FK_1234 Wednesday, July 26, 2017 4:40 PM Explanation
    Wednesday, July 26, 2017 4:08 PM
  • Why the master was only pulling one record would have nothing to do with your code. If you remove the Current event and just let the subforms open full size, do you see more than one records for the master as you go through the records? If not, then you'll have to isolate why. Check the linked fields to make sure they are correct, for example. Or, see if a filter or criteria is being applied somewhere else.

    Hope it helps...

    Wednesday, July 26, 2017 4:49 PM
  • I just ran more testing.  If I open directly to a record that has more than 1 record in Master Subform, they all appear without any problems.  I've isolated the problem to: if I flip through records and go from a form that has 1 result in a Master Subform to one that has >1, the height is not adjusting and the record count is staying at 1.  Weird?  I know.

    Maybe I need to move the code from On Current to different Event?  I've tried On Load and it does the same thing.  Any thoughts?

     
    Wednesday, July 26, 2017 4:53 PM
  • The difference between Load and Current event is the Load event only gets triggered once. If you want the code to run each time you move through the records, then you should use the Current event.
    Wednesday, July 26, 2017 5:08 PM
  • The difference between Load and Current event is the Load event only gets triggered once. If you want the code to run each time you move through the records, then you should use the Current event.

    I have narrowed this down to:

    The correct Recordcount is not retrieving with this code:

    If Me![HoistMaster].Form.RecordsetClone.RecordCount > 0 Then
    Me.HoistMaster.Height = (Me![HoistMaster].Form.RecordsetClone.RecordCount * 421) + 864
    Else
    Me.HoistMaster.Height = 0
    End If

    If the first record I open has 3 records in the subform, it will show all 3.  If the next one has 1, it shows 1.  If the next record has 4, it shows 1.  Once it decreases the record count, it won't catch back up.  The height doesn't adjust and the Message Box with the record count isn't pulling the correct # of records unless it's the first record I open.

    Wednesday, July 26, 2017 9:05 PM
  • If the first record I open has 3 records in the subform, it will show all 3.  If the next one has 1, it shows 1.  If the next record has 4, it shows 1.  Once it decreases the record count, it won't catch back up.  The height doesn't adjust and the Message Box with the record count isn't pulling the correct # of records unless it's the first record I open.

    Hi FK_1234,

    I would not be suprised is if there is a timing problem with setting the height of the control and the availability of the correct RecordCount.

    What happens when you - in the Load event of the main form - step through all the controls that must be height-adjusted, determine the real RecordCount, and then set the height of that control according to your formula?

    Imb.

    Wednesday, July 26, 2017 9:31 PM
  • I think it may have something to do with my formula missing:

    .MoveFirst & .MoveLast

    I'm just not sure how to incorporate it.  Any suggestions?

    Wednesday, July 26, 2017 9:46 PM
  • I think it may have something to do with my formula missing:

    .MoveFirst & .MoveLast

    I'm just not sure how to incorporate it.  Any suggestions?

    Hi FK_1234,

    When stepping through the relevant controls you could use something like:

        Set cur_set = Me!Hoistmaster.Form.Recordset
        cur_set.Movelast
        nr_records = cur_set.RecordCount
        cur_set.Close

    and use nr_records to adjust the Height of the control.

    Imb.

    Wednesday, July 26, 2017 10:02 PM
  • I think it may have something to do with my formula missing:

    .MoveFirst & .MoveLast

    I'm just not sure how to incorporate it.  Any suggestions?

    Hi FK_1234,

    When stepping through the relevant controls you could use something like:

        Set cur_set = Me!Hoistmaster.Form.Recordset
        cur_set.Movelast
        nr_records = cur_set.RecordCount
        cur_set.Close

    and use nr_records to adjust the Height of the control.

    Imb.

    I was able to solve the problem with the # of Master Subform records pulling in by adding .MoveLast.  I now have an issue of the Detail Subform only expanding to the height of the Master Subform.  The Detail Subform is also supposed to adjust height for # of records for each record on Master Subform that is clicked.  It's staying at the same height

    If Me![HoistMaster].Form.Recordset.RecordCount > 0 Then
    Me![HoistMaster].Form.Recordset.MoveLast
    Me.HoistMaster.Height = (Me![HoistMaster].Form.Recordset.RecordCount * 421) + 864
    Me.CageMaster.Top = 1500 + Me.HoistMaster.Height
    Else
    Me.HoistMaster.Height = 0
    Me.CageMaster.Top = 288
    End If


    If Me![HoistDetail].Form.Recordset.RecordCount > 0 Then
    Me![HoistDetail].Form.Recordset.MoveLast
    Me.HoistDetail.Height = (Me![HoistDetail].Form.Recordset.RecordCount * 331) + 310
    Else
    Me.HoistDetail.Height = 0
    End If


    Thursday, July 27, 2017 2:40 PM
  • I was able to solve the problem with the # of Master Subform records pulling in by adding .MoveLast.  I now have an issue of the Detail Subform only expanding to the height of the Master Subform.  The Detail Subform is also supposed to adjust height for # of records for each record on Master Subform that is clicked.  It's staying at the same height

    Hi FK_1234,

    In general, the height of the lowest level subform, that is the one with the actual controls of the data, must be large enough to hold all the controls.I\

    If you want to see all date in the parent's control, then the height of that control can be adjusted. But the height of the holder of that control must be enough to hold that control, so that height must also be adjusted.

    The same again must be fulfilled for the next parent's control, etc.

    You can make a routine that adjusts all parent's control heights depending on the lowest level [edit] subform [/edit] height.

    Imb.



    • Edited by Imb-hb Thursday, July 27, 2017 7:08 PM
    Thursday, July 27, 2017 4:06 PM
  • Hmm, I thought I posted this earlier but I was just thinking you might try adding the same code to resize the Detail subform in the Current event of the Master subform.

    Just a thought...

    Thursday, July 27, 2017 4:16 PM