Gurus needed: hiding/positioning subforms

  • Thread starter Thread starter Assaf
  • Start date Start date
A

Assaf

Here's one that got us scratching our heads....

We have an Invoice form with 5 sections implemented as subforms:
subMaterials, subLabor...

Not all sections are relevant to all Invoices so the customer would like to
eliminate (i.e., hide) sections which have no data, rather than see empty
grids or 0 sums.

Has anyone tackled this before?

The tricky parts we identified are:
1. Subforms load before the form itself so when (which event) do you use to
hide them if they have no data?
2. How do you dynamically position subforms correctly so that there are no
'gaps' in the parent Invoice form?

Are there other gottchas we should look out for?

Any leads are greatly appreciated.

- Assaf

(excuse crosspost)
 
You may find it easier to use a tab control, with subforms on individual
tabs. Then hide the tabs that aren't needed. This will save you from
repositioning subforms, which I don't believe is possible in MDE or run-time
anyway. In the Current event for the main form, you'll need code that
analyzes whether a tab should be shown based on whether the subform has data
or whether the subform is applicable to the invoice.
 
Can you describe a bit more?

Not sure how to display subforms 1 & 4 for invoice #1000 and subform 2 & 3 &
4 for invoice #1001 using tabs.

Thanks.
 
Assaf said:
Here's one that got us scratching our heads....

We have an Invoice form with 5 sections implemented as subforms:
subMaterials, subLabor...

Not all sections are relevant to all Invoices so the customer would like to
eliminate (i.e., hide) sections which have no data, rather than see empty
grids or 0 sums.

Has anyone tackled this before?

The tricky parts we identified are:
1. Subforms load before the form itself so when (which event) do you use to
hide them if they have no data?
2. How do you dynamically position subforms correctly so that there are no
'gaps' in the parent Invoice form?

Are there other gottchas we should look out for?

Any leads are greatly appreciated.

- Assaf

(excuse crosspost)
Click to expand...


Never tried it but here's how I would attack the problem, assuming the
height of all the subforms is 2 inches.


Public Sub OpenAccountForm(TheAccountNumber As Long)
Dim StrCriteria As String, PositionOffset As Long
PositionOffset = 3000
StrCriteria = "[Account] = " & TheAccountNumber
DoCmd.OpenForm "FrmAccount", , , StrCriteria

' do invoice subform
If IsNull(DLookup("[Invoice]", "tblInvoice", StrCriteria)) Then
[Forms]![FrmAccount]!InvoiceSubform.Visible = False
Else
[Forms]![FrmAccount]!InvoiceSubform.Visible = True
[Forms]![FrmAccount]!InvoiceSubform.Top = PositionOffset
PositionOffset = PositionOffset + 2880
End If

' do Paymentssubform

If IsNull(DLookup("[PaymentID]", "tblPayments", StrCriteria)) Then
[Forms]![FrmAccount]!PaymentsSubform.Visible = False
Else
[Forms]![FrmAccount]!PaymentsSubform.Visible = True
[Forms]![FrmAccount]!PaymentsSubform.Top = PositionOffset
PositionOffset = PositionOffset + 2880
End If

' do this for as many subforms you have

[Forms]![FrmAccount].Refresh

End Sub
 
Hi,

You could do the following:
1. Create a Private Sub that controls which subforms are
visible by toggling Visible property of each page of the
tab control. Each page of the tab control has its own
name and each page is kept in the Pages collection, so
you could hide/show them by using the following syntax:
Me![TabControlName].Pages![PageName].Visible = False /
True. TabControlName is the name of the tab control where
all the subforms are inserted on different pages.
PageName is the name of the respective page you want to
hide or show.
2. You could call this sub on OnCurrent event of the
form, so each time the current record is changed you
could rearrange the visible tabs. This way you make sure
that only the appropriate tabs (respectively subforms)
will be shown. You could also call the sub on the OnOpen
event of the form to arrange the tabs initially when the
form is opened for the first time.

Hope this helps.

Cheers,
Vasko
 
Taking your example:

Sub Form_Current()

If Me.Invoice = 1000 Then
Me.Page1.Visible = True
Me.Page4.Visible = True
Me.Page2.Visible = False
Me.Page3.Visible = False
ElseIf Me.Invoice = 1001 Then
Me.Page1.Visible = False
Me.Page4.Visible = False
Me.Page2.Visible = True
Me.Page3.Visible = True
End If

End Sub
 
Thanks. I'll give that a try.

Paul Overway said:
Taking your example:

Sub Form_Current()

If Me.Invoice = 1000 Then
Me.Page1.Visible = True
Me.Page4.Visible = True
Me.Page2.Visible = False
Me.Page3.Visible = False
ElseIf Me.Invoice = 1001 Then
Me.Page1.Visible = False
Me.Page4.Visible = False
Me.Page2.Visible = True
Me.Page3.Visible = True
End If

End Sub
--
Paul Overway
Logico Solutions, LLC
www.logico-solutions.com


3
Click to expand...
 
Thanks, Treebeard.

We're going to experiment with the approach you suggested.

My only concern is that that amount of logic will degrage batch printing
which is already pretty slow. I'll give it a shot, see how Access holds up.


Treebeard said:
Assaf said:
Here's one that got us scratching our heads....

We have an Invoice form with 5 sections implemented as subforms:
subMaterials, subLabor...

Not all sections are relevant to all Invoices so the customer would like to
eliminate (i.e., hide) sections which have no data, rather than see empty
grids or 0 sums.

Has anyone tackled this before?

The tricky parts we identified are:
1. Subforms load before the form itself so when (which event) do you use to
hide them if they have no data?
2. How do you dynamically position subforms correctly so that there are no
'gaps' in the parent Invoice form?

Are there other gottchas we should look out for?

Any leads are greatly appreciated.

- Assaf

(excuse crosspost)
Click to expand...


Never tried it but here's how I would attack the problem, assuming the
height of all the subforms is 2 inches.


Public Sub OpenAccountForm(TheAccountNumber As Long)
Dim StrCriteria As String, PositionOffset As Long
PositionOffset = 3000
StrCriteria = "[Account] = " & TheAccountNumber
DoCmd.OpenForm "FrmAccount", , , StrCriteria

' do invoice subform
If IsNull(DLookup("[Invoice]", "tblInvoice", StrCriteria)) Then
[Forms]![FrmAccount]!InvoiceSubform.Visible = False
Else
[Forms]![FrmAccount]!InvoiceSubform.Visible = True
[Forms]![FrmAccount]!InvoiceSubform.Top = PositionOffset
PositionOffset = PositionOffset + 2880
End If

' do Paymentssubform

If IsNull(DLookup("[PaymentID]", "tblPayments", StrCriteria)) Then
[Forms]![FrmAccount]!PaymentsSubform.Visible = False
Else
[Forms]![FrmAccount]!PaymentsSubform.Visible = True
[Forms]![FrmAccount]!PaymentsSubform.Top = PositionOffset
PositionOffset = PositionOffset + 2880
End If

' do this for as many subforms you have

[Forms]![FrmAccount].Refresh

End Sub
Click to expand...
 
My only concern is that that amount of logic will degrage batch printing
which is already pretty slow.
Click to expand...

Assaf,

Are you talking about a report or form? I think everyone thought you were
talking about a form. Performing this operation on a report is much easier
than a form.

Jack



Assaf said:
Thanks, Treebeard.

We're going to experiment with the approach you suggested.

My only concern is that that amount of logic will degrage batch printing
which is already pretty slow. I'll give it a shot, see how Access holds up.


Treebeard said:
like
to use
to
Click to expand...
are
no
'gaps' in the parent Invoice form?

Are there other gottchas we should look out for?

Any leads are greatly appreciated.

- Assaf

(excuse crosspost)
Click to expand...


Never tried it but here's how I would attack the problem, assuming the
height of all the subforms is 2 inches.


Public Sub OpenAccountForm(TheAccountNumber As Long)
Dim StrCriteria As String, PositionOffset As Long
PositionOffset = 3000
StrCriteria = "[Account] = " & TheAccountNumber
DoCmd.OpenForm "FrmAccount", , , StrCriteria

' do invoice subform
If IsNull(DLookup("[Invoice]", "tblInvoice", StrCriteria)) Then
[Forms]![FrmAccount]!InvoiceSubform.Visible = False
Else
[Forms]![FrmAccount]!InvoiceSubform.Visible = True
[Forms]![FrmAccount]!InvoiceSubform.Top = PositionOffset
PositionOffset = PositionOffset + 2880
End If

' do Paymentssubform

If IsNull(DLookup("[PaymentID]", "tblPayments", StrCriteria)) Then
[Forms]![FrmAccount]!PaymentsSubform.Visible = False
Else
[Forms]![FrmAccount]!PaymentsSubform.Visible = True
[Forms]![FrmAccount]!PaymentsSubform.Top = PositionOffset
PositionOffset = PositionOffset + 2880
End If

' do this for as many subforms you have

[Forms]![FrmAccount].Refresh

End Sub
Click to expand...
Click to expand...
 
Jack,

The invoice was implemented as a FORM before my time. I'm actually looking
into using a report right now. The tricky part is that every section in the
invoice has a different number and arrangement of column. Is there a way to
control which columns/fields appear and in what order?

- Assaf

Treebeard said:
My only concern is that that amount of logic will degrage batch printing
which is already pretty slow.
Click to expand...

Assaf,

Are you talking about a report or form? I think everyone thought you were
talking about a form. Performing this operation on a report is much easier
than a form.

Jack



Assaf said:
Thanks, Treebeard.

We're going to experiment with the approach you suggested.

My only concern is that that amount of logic will degrage batch printing
which is already pretty slow. I'll give it a shot, see how Access holds up.


Treebeard said:
Here's one that got us scratching our heads....

We have an Invoice form with 5 sections implemented as subforms:
subMaterials, subLabor...

Not all sections are relevant to all Invoices so the customer would like
to
eliminate (i.e., hide) sections which have no data, rather than see empty
grids or 0 sums.

Has anyone tackled this before?

The tricky parts we identified are:
1. Subforms load before the form itself so when (which event) do you use
to
hide them if they have no data?
2. How do you dynamically position subforms correctly so that there
Click to expand...
are
no
'gaps' in the parent Invoice form?

Are there other gottchas we should look out for?

Any leads are greatly appreciated.

- Assaf

(excuse crosspost)




Never tried it but here's how I would attack the problem, assuming the
height of all the subforms is 2 inches.


Public Sub OpenAccountForm(TheAccountNumber As Long)
Dim StrCriteria As String, PositionOffset As Long
PositionOffset = 3000
StrCriteria = "[Account] = " & TheAccountNumber
DoCmd.OpenForm "FrmAccount", , , StrCriteria

' do invoice subform
If IsNull(DLookup("[Invoice]", "tblInvoice", StrCriteria)) Then
[Forms]![FrmAccount]!InvoiceSubform.Visible = False
Else
[Forms]![FrmAccount]!InvoiceSubform.Visible = True
[Forms]![FrmAccount]!InvoiceSubform.Top = PositionOffset
PositionOffset = PositionOffset + 2880
End If

' do Paymentssubform

If IsNull(DLookup("[PaymentID]", "tblPayments", StrCriteria)) Then
[Forms]![FrmAccount]!PaymentsSubform.Visible = False
Else
[Forms]![FrmAccount]!PaymentsSubform.Visible = True
[Forms]![FrmAccount]!PaymentsSubform.Top = PositionOffset
PositionOffset = PositionOffset + 2880
End If

' do this for as many subforms you have

[Forms]![FrmAccount].Refresh

End Sub
Click to expand...
Click to expand...
Click to expand...
 
Back
Top