By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,644 Members | 1,866 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,644 IT Pros & Developers. It's quick & easy.

Subform won't show when adding a new record to main form.

100+
P: 115
Not sure what happened, but cannot figure out why its not working at this point. I attached the database so anyone willing to help might be able to tell me what went wrong and what I can do to fix it.

Thanks for any help provided.
Attached Files
File Type: zip OEE Database.zip (1.10 MB, 11 views)
Sep 4 '18 #1

✓ answered by zmbd

DJRhino1175
I do want the subform to show all the time, just one line for a new entry.
Normally, new question new thread; however, in this case I think this is really a refinement of what your actual problem was...

OK,
Because you really intended to have the subform show all of the time I really advise simply removing of the calls to the Function ReSizeForm.

HOWEVER, if you really want to use the function then we can simplify things a bit

We'll remove the optional new record flag and set a minimum subform height.

So you now have:
Form: Frm_FrmLine - On_Current Event:
Expand|Select|Wrap|Line Numbers
  1. Private Sub Form_Current()
  2.  
  3.      Call ReSizeSubform(frm:=Me)
  4.  
  5. End Sub
and change the function so that you have:
Module: MDLForms - Function ResizeSubform:
Expand|Select|Wrap|Line Numbers
  1. Function ReSizeSubform(frm As Form)
  2.   ' Runs on the OnCurrent of the MAIN Form (FrmLine)
  3.     Dim TotalHeight As Single
  4.     Dim NoRecs As Long
  5. '
  6.     With frm.subfrmDrawing.Form
  7.       If Not .Recordset.EOF Then
  8.         .Recordset.MoveLast
  9.         NoRecs = Nz(.Recordset.RecordCount) + 1
  10.         If NoRecs > MaxRecs Then
  11.           NoRecs = MaxRecs
  12. '         .ScrollBars = 0             ' Show scrollbar
  13. '       Else
  14. '         .ScrollBars = 0             ' end if
  15.         End If
  16.         TotalHeight = .Section(acHeader).Height + .Section(acFooter).Height + (.Section(acDetail).Height * NoRecs)
  17.         If Not .Recordset.BOF Then
  18.           .Recordset.MoveFirst
  19.         End If
  20.       End If
  21.     End With
  22. '
  23.   If TotalHeight < 2415 Then TotalHeight = 2415
  24. '
  25.   frm.subfrmDrawing.Height = TotalHeight
  26.   frm.Repaint
  27. End Function
Line 24, ensures that the subform is always shown regardless of the actual number of child records associated with parent.

Again, the value of 2415 for the subform height is an arbitrary value I used because the 4 record subform was aesthetically pleasing to my eye as I stepped through the records.

Share this Question
Share on Google+
10 Replies


zmbd
Expert Mod 5K+
P: 5,287
DJRhino1175:
- Please keep in mind that most of use will not d/l files without them being asked for first. Many of us do this during down time at work and there are usually very strict rules about downloading a files.

- You have several forms in this database
Which form are you referring to?
You cannot expect anyone here to wade through your application to find the issue; help us to help you by telling us which forms are the issue and in this case give a bit of detail about how they are related.
Sep 4 '18 #2

100+
P: 115
Sorry ZMBD, I apologize if I broke any rules or protocols. On the start page click the button that says OEE Entry form. That opens "FrmLine". When creating a new record a subform should be showing below the comments box. it doesn't show for a new record, but will show for an old record. The subform is called SubfrmDrawing.
Sep 4 '18 #3

zmbd
Expert Mod 5K+
P: 5,287
Form: FrmLine
Event: On_Current
Calls Module MDLForms - Function ReSizeSubForm
On a new record there are no related records; thus,
frm.subfrmDrawing.Height = TotalHeight
TotalHeight = 0
Therefor the subform is hidden.

I'll take a closer look in a little bit... the lab timer just buzzed :)
Sep 4 '18 #4

100+
P: 115
ZMBD,

I disabled this code and the subform is showing up again, so now I need to look at this code a little closer to find what needs to change so that it at least shows up when there is no records.
Sep 4 '18 #5

zmbd
Expert Mod 5K+
P: 5,287
Change the following:
Form: Frm_FrmLine
On_Current Event:
Expand|Select|Wrap|Line Numbers
  1. Private Sub Form_Current()
  2.  
  3.     Call ReSizeSubform(frm:=Me, zMeNewRecord:=Me.NewRecord)
  4.  
  5. End Sub
This will allow you to pass a new record flag to the resize function

Module: MDLForms
Function ResizeSubform:
Adding an optional flag with a default of false for new record lets you set the size of the subform to an arbitrary value; running your form a few times I settled on the size for 4 records as the default subform size when on a new record.
Expand|Select|Wrap|Line Numbers
  1. Function ReSizeSubform(frm As Form, Optional zMeNewRecord As Boolean = False)
  2.   ' Runs on the OnCurrent of the MAIN Form (FrmLine)
  3.   '    Dim NoRecs As Integer
  4.     Dim TotalHeight As Single
  5.     Dim NoRecs As Long
  6.   If zMeNewRecord Then
  7.     TotalHeight = 2415
  8.   Else
  9.     With frm.subfrmDrawing.Form
  10.       If Not .Recordset.EOF Then
  11.         .Recordset.MoveLast
  12.         NoRecs = Nz(.Recordset.RecordCount) + 1
  13.         If NoRecs > MaxRecs Then
  14.           NoRecs = MaxRecs
  15. '         .ScrollBars = 0             ' Show scrollbar
  16. '       Else
  17. '         .ScrollBars = 0             ' end if
  18.         End If
  19.         TotalHeight = .Section(acHeader).Height + .Section(acFooter).Height + (.Section(acDetail).Height * NoRecs)
  20.         If Not .Recordset.BOF Then
  21.           .Recordset.MoveFirst
  22.         End If
  23.       End If
  24.     End With
  25.   End If
  26. '
  27.   frm.subfrmDrawing.Height = TotalHeight
  28.   frm.Repaint
  29. End Function
Verify:
Form: FrmLine
Open in design view
Show the form's Property Sheet
In the selection type dropdown select: "subfrmDrawing"
Select the data tab
Verify that [Filter On Empty Master]= Yes
This will ensure that when you are on a new record the subform doesn't simply display the first record(s) in the bound data set.
Sep 4 '18 #6

zmbd
Expert Mod 5K+
P: 5,287
Ok,
Looking at your last post: if you always want the subform to be visible then the modifications I looked at for new record are not needed - instead, don't call Function ReSizeSubform and set the subform area in the parent to the correct size for the desired display.
Sep 4 '18 #7

100+
P: 115
I updated to what you suggested and it all works fine. I do want the subform to show all the time, just one line for a new entry. The second half of the module that was modified was for when adding or deleting lines of data from that sub form. Now I'm curious if I'll need to update the second half of the MDLForms module which handles the After update and on Delete of the subform.
Sep 4 '18 #8

100+
P: 115
I would like to thank you for the help and guidance on repairing this issue.
Sep 4 '18 #9

zmbd
Expert Mod 5K+
P: 5,287
DJRhino1175
I do want the subform to show all the time, just one line for a new entry.
Normally, new question new thread; however, in this case I think this is really a refinement of what your actual problem was...

OK,
Because you really intended to have the subform show all of the time I really advise simply removing of the calls to the Function ReSizeForm.

HOWEVER, if you really want to use the function then we can simplify things a bit

We'll remove the optional new record flag and set a minimum subform height.

So you now have:
Form: Frm_FrmLine - On_Current Event:
Expand|Select|Wrap|Line Numbers
  1. Private Sub Form_Current()
  2.  
  3.      Call ReSizeSubform(frm:=Me)
  4.  
  5. End Sub
and change the function so that you have:
Module: MDLForms - Function ResizeSubform:
Expand|Select|Wrap|Line Numbers
  1. Function ReSizeSubform(frm As Form)
  2.   ' Runs on the OnCurrent of the MAIN Form (FrmLine)
  3.     Dim TotalHeight As Single
  4.     Dim NoRecs As Long
  5. '
  6.     With frm.subfrmDrawing.Form
  7.       If Not .Recordset.EOF Then
  8.         .Recordset.MoveLast
  9.         NoRecs = Nz(.Recordset.RecordCount) + 1
  10.         If NoRecs > MaxRecs Then
  11.           NoRecs = MaxRecs
  12. '         .ScrollBars = 0             ' Show scrollbar
  13. '       Else
  14. '         .ScrollBars = 0             ' end if
  15.         End If
  16.         TotalHeight = .Section(acHeader).Height + .Section(acFooter).Height + (.Section(acDetail).Height * NoRecs)
  17.         If Not .Recordset.BOF Then
  18.           .Recordset.MoveFirst
  19.         End If
  20.       End If
  21.     End With
  22. '
  23.   If TotalHeight < 2415 Then TotalHeight = 2415
  24. '
  25.   frm.subfrmDrawing.Height = TotalHeight
  26.   frm.Repaint
  27. End Function
Line 24, ensures that the subform is always shown regardless of the actual number of child records associated with parent.

Again, the value of 2415 for the subform height is an arbitrary value I used because the 4 record subform was aesthetically pleasing to my eye as I stepped through the records.
Sep 4 '18 #10

100+
P: 115
zmbd,

Updated the code and all checks out.
Sep 4 '18 #11

Post your reply

Sign in to post your reply or Sign up for a free account.