Gurus needed: hiding/positioning subforms

[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)

 

[Paul Overway]

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.

 

[Assaf]

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.

 

[Treebeard]

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)

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

 

[Paul Overway]

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

 

[Assaf]

Thanks. I'll give that a try.

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

 

[Assaf]

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.

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)

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

 

[Treebeard]

My only concern is that that amount of logic will degrage batch printing

which is already pretty slow.

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

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.

like
to use
to

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

 

[Assaf]

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

My only concern is that that amount of logic will degrage batch printing
which is already pretty slow.

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

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.

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)




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