Hide subform field's dafault value until needed (1 Viewer)

bongbang · Jan 13, 2018

On my form, the default value of a subform field shows up on the New Entry row, even the row is not being edited. This is not only unsightly, but also misleading and confusing. Is there a way to have the subform's defaults show up only when they're needed (i.e. the new entry row is focused)?

Much to my surprise, I have not found others with this question in my more than cursory search.

Eljefegeneo · Jan 13, 2018

This is what I do, you can modify it to suit.

You can hide a subform based on another field on the form. In this case, if my client is Active, certain pages or tabs appear, otherwise they don’t.

Code:

If Class = "Active" Then
   
      Me.SubForm1.Pages(1).Visible = True
      Me.SubForm1.Pages(4).Visible = True
      Me.SubForm1.Pages(5).Visible = True
      Me.SubForm1.Pages(9).Visible = True
      Me.SubForm1.Pages(10).Visible = True
      Me.SubForm1.Pages(11).Visible = True
   
  Else
      Me.SubForm1.Pages(1).Visible = True
      Me.SubForm1.Pages(4).Visible = False
      Me.SubForm1.Pages(5).Visible = False
      Me.SubForm1.Pages(9).Visible = True
      Me.SubForm1.Pages(10).Visible = False
      Me.SubForm1.Pages(11).Visible = False
  End If

Or, you can make the form blank by the following. It determines if there are any records.

Code:

  With Me![qryMainActiveSalesSub subform].Form
          .Visible = (.RecordsetClone.RecordCount > 0)
      End With
   
      With Me![frmMainInActiveSalesSub].Form
          .Visible = (.RecordsetClone.RecordCount > 0)
      End With

I use both, but on the second method, I have label under the subform on the tab that will either say “No Active Sales” or “No inactive sales”.

Pat Hartman · Jan 13, 2018

To hide the "new" record until you need it, set the form's "AllowAdditions" property to No. Add a button to the form to toggle this. When the button changes the property, have it go to the new record. In the AfterInsert event, you can reset the property to No if you want them to only insert one record or you can leave it set.

To use this method, you will probably have to set the AllowAdditions to No in the form's current event. That will reset it for each new record.

If you want to get fancy, you can set the enabled property of the button to No in its click event to prevent it from being clicked again and then set the enabled property to Yes in the form's AfterInsert event.

Hide subform field's dafault value until needed (1 Viewer)

bongbang

Registered User.

Eljefegeneo

Still trying to learn

Pat Hartman

Super Moderator

Similar threads

Users who are viewing this thread