Hide Subform Until Mainform Has Data

[nyamrembo]

Hi, I have a Mainform and a Subform that are linked by a command button. Mainform has a combo box with Customer names. The subform show details about the customers. The mainform loads blank. When the mainform is blank, the subform also loads blank. But what I want is if the mainform is blank, then I do not want the subform to open until the mainform has corresponding data.

I am looking for some event that can trigger the subform to open only when there is an existing record in the customer table (mainform). I have searched the web but could not find any relevant solutions. My ideal solution is, if mainform has no data then the user cannot open the subform or user should get a message informing them that data is required in the mainform.

Please let me know if this is confusing...

Thanks,

 

[randy700]

How about something like this?

If Me.SomeTextbox = "" Then
    Me.Subform.Visible = False
Else
    Me.Subform.Visible = True
End If

Perhaps in the forms OnCurrent event?


Randy

 

[nyamrembo]

Anymore ideas? this one is giving me some method not found error...do you know what else I will need to do? can I put the code in the command button because that is where the subform is filtered.

Thanks again and have a lovely weekend!
[Sunshine]

 

[randy700]

What is the actual code you tried to use?
What are the names of your controls?


Randy

 

[TheAceMan1]

How are ya nyamrembo . . .

Note that a mainform with subform(s) opens from the innermost subform out to the mainform (relating to nested subforms) ... or ... [blue]the subform(s) open 1st[/blue], with the mainform opening last. Here's what to do:

In [blue]design view[/blue] of the mainform, set the subforms [blue]visible[/blue] property to [blue]no[/blue]. The subform is now hidden on open! Next ... copy/paste the following routine in the code module of the mainform:

[blue]Public Sub ShowSub()
   
   If Me.Recordset.RecordCount > 0 Then
      [[purple][B][I]YourSubFormName[/I][/B][/purple]].Visible = True
   End If
   
End Sub[/blue]

Finally ... in the [blue]On Load[/blue] & [blue]After Update[/blue] events of the mainform, copy/paste the following:

[blue]   Call ShowSub[/blue]

The [blue]On Load[/blue] event handles the subform during opening of the form ... and the [blue]After Update[/blue] event handles the subform when you finally add a record to the mainform.

[blue]Your Thoughts? . . .[/blue]

See Ya! . . . . . .

Be sure to see thread181-473997 [blue]Worthy Reading![/blue]
Also faq181-2886 [blue]Worthy Reading![/blue]

 

[nyamrembo]

Sorry, but my subform is not nested with the mainform. It's linked using a command button with the following code to open the subform.Mainform is called [frmCustomerInfo] and Suform is called [frmCustPurchasesSubform].

Dim stDocName As String
Dim strLinkCriteria As String

stDocName = "SubformName"
If IsNull(Me![tblCustomers.CustomerID]) Then
DoCmd.OpenForm stDocName
DoCmd.GoToRecord , , acNewRec
Else
DoCmd.OpenForm stDocName

strLinkCriteria = "[tblPurchase.CustomerID]=" & Me![CustomerID]
DoCmd.OpenForm stDocName, , , stLinkCriteria

End If

So what i need is how to prevent the command button from opening the form when there is no data on the mainform. is this possible? if not, what would be the best way to do this without embedding the subform with the mainform?
Thanks
[Sunshine]

 

[TheAceMan1]

nyamrembo . . .

In following this thread I've been been astray in my understanding due to your use of the term [blue]subform[/blue].

[blue]subForm: any form embedded within another.[/blue]

What you've been talking about is an [blue]independent[/blue] form!

So try the following:

[blue]   If Me.Recordset.RecordCount > 0 Then [green]'records exits in mainform[/green]
      stDocName = "frmCustPurchasesSubform"
      DoCmd.OPenForm stDocName
   End If[/blue]

See Ya! . . . . . .

Be sure to see thread181-473997 [blue]Worthy Reading![/blue]
Also faq181-2886 [blue]Worthy Reading![/blue]