Tab control, invisible tab when no record (1 Viewer)

Bjorn_DA · Feb 5, 2013

Hi

I have a main form where I choose customer that opens a new form with customer info and a tab control, with different products for each tab for that specific customer. It's a total of 17 tabs.
The tabs contains subforms created on the tables (not queries). So far so good, everything works fine.

I have received requests from users that the tabs that contains no records gets marked or sets invisible.
The users does not add or edit any records, but in the back end we update the tables with regularity.

I have searched around but have not found any solution for this. Has anyone here suggestions on how I solve it?

I'm not good at VBA coding.

gemma-the-husky · Feb 5, 2013

in the mainforms current event - you could try something like this

subdetails.form.visible = dcount("*","qryname")>0

where subdetails is the name of the subform control on your form
qryname is the query that drives that subform.

i haven't checked, but I expect there might be code that would set a specific page of a tab control to not visible also.

Artair · Feb 5, 2013

Try putting the following code into each of the sub forms modules. I've tested this in Access 2003 and it seems to do the trick.

Code:

Private Sub Form_Load()

    Dim rs As DAO.Recordset
    Set rs = Me.Recordset
    
    With Me.Parent.TabCtl0.Pages(0)
        If rs.RecordCount = 0 Then
            .Visible = False
        Else
            .Visible = True
        End If
    End With

    Set rs = Nothing

End Sub

Change the 'TabCtl0' to the name of your Tab Control. For each form you will also need to change the page index for the tab you wish to hide. In this example it will hide the first page.

Remember that the index starts at 0. So the first tab is 0, the second is 1, third is 2 and so on.

Bjorn_DA · Feb 7, 2013

Apologies that I havent responded earlier, have a cold and fever ...

Thank you both for your replies! I think Artair is closest to what I'm looking for, because I did not create queries to the tab-subforms.
But I can't make it work. Could it be because the subforms are datasheets?

gemma-the-husky · Feb 7, 2013

artairs code sounds logical, so are you putting his code in each of your subforms?

however - I suspect it might not work, as I doubt very much if you can make an active control/form/whatever not visible.

I used "qryname" as a generic. The subforms MUST be bound to something, must they not?

Artair · Feb 7, 2013

I tested the code on a test db before I posted it and it worked as I would expect it to, only my subforms were continuous forms, not data sheets. Don't know if it would make a difference.

The only thing I didn't test was having no records in the subform that was contained on the first tab. I guess if you have a subform on the first tab, and that form is trying to hide that tab because it has no records, it would fail because the tab has the focus.

What do you have on the first tab? Is it a subform?

Bjorn_DA · Feb 7, 2013

Yes its a subform on the first tab but its always a record in it, so that should not be the problem.

I will try again with your code, maybe I made something wrong yesterday. If I dont get it to work I'll create a sample-db to post here.

Artair · Feb 7, 2013

Is it just not hiding the tabs or are you getting an error when you open the main form?

Bjorn_DA · Feb 7, 2013

Its just not hiding, the code is "accepted".

Bjorn_DA · Feb 7, 2013

Strange, now I am getting error when open main form... :banghead:

I attach a sample-db (MSA 2010), without the code. Search "sell01" and open up a Customer card.

Artair · Feb 7, 2013

Unfortunately I don't have Access 2010. Can you convert it to 2003?

Bjorn_DA · Feb 7, 2013

Yes, here it is. I really appreciate your help

Artair · Feb 7, 2013

The reason my initial code isn't working is because for some reason the code isn't running until the form is displayed on the screen by clicking that tab.

Try the following piece of code. Put it in the module for frmCUSTOMER_CARD. You will also need to change the Tag property of each subform control to the index of the tab that subform is on

So for sfrmPROD1, change the tag to 1, for sfrmPROD2, change it to 2 and so on...

Code:

Private Sub Form_Load()
    Dim rs As DAO.Recordset
    Dim ctrl As Control
    Dim x As Integer
   
    For Each ctrl In Me.Controls
        If ctrl.ControlType = acSubform And Len(ctrl.Tag) <> 0 Then
            Set rs = ctrl.Form.Recordset
            If rs.RecordCount = 0 Then
                x = ctrl.Tag
                Me.TabCtl139.Pages(x).Visible = False
            End If
        End If
    Next ctrl
    
    Set rs = Nothing
End Sub

Let us know how you get on.

Bjorn_DA · Feb 7, 2013

Thanks a lot! I dont get it to work now, maybe dont understand clompletly, but its late and I think I need to sleep my cold away. Will try tomorrow again.

Artair · Feb 7, 2013

Each control has a 'Tag' property. On each sub form control (not the form itself, but the control that holds the form, so sfrmPROD1, sfrmPROD2 etc) on the customer card will need the value of its Tag property as the index of the Tab page that the sub form control is on.

I've re-attached your DB with the code in it so you can see.

Bjorn_DA · Feb 8, 2013

Your my hero!

It took me a while before I found the tag control, but now I get it. Thanks a lot!!

Artair · Feb 8, 2013

You're welcome. Glad you got it working.

Bjorn_DA · Mar 25, 2014

Hi again

This form worked perfect and was very appreciated by our sellers. But now I have a new, similar project. The principle is the same; by clicking on the customer name in the main form, a new form opens that contains a tab control with a page for each product (lets say product A, B and C) for that specific customer. If the customer doesnt have a product, the tab page is hidden for that product. Perfect so far! (Each tab page contains subform for each product.)

But...in this new project the users want to have the two forms open beside; they dont want to have to close the product-form between selecting different customers. It means that if they click on a new customer in the main form, the tab control in the subform must be reloaded. The info in the tab control is correct, but the tab pages is still hidden even if the newly selected customer has it and vice verse.

I have tried a lot of macros and vba on different events, but I cant get it to work. I'm not good at vba (rather lousy).

I got the code for hiding tab pages from Artair in this thread:

Private Sub Form_Load()
Dim rs As DAO.Recordset
Dim ctrl As Control
Dim x As Integer

For Each ctrl In Me.Controls
If ctrl.ControlType = acSubform And Len(ctrl.Tag) <> 0 Then
Set rs = ctrl.Form.Recordset
If rs.RecordCount = 0 Then
x = ctrl.Tag
Me.tabProdukt.Pages(x).Visible = False
End If
End If
Next ctrl

Set rs = Nothing
End Sub

I guess that the solution is simple, but as I said; I'm lousy at vba-coding. Have been searching around a lot but cant find anything to "steal".

Hope you understand what I mean (otherwise I'll try to explain myself), and that someone has a solution to the problem!

Bjorn_DA · Mar 26, 2014

Problem solved. I put an Close subform-macro on the current event of the main form. So by clicking customer name the subform is closed and then re-opened.

Tab control, invisible tab when no record (1 Viewer)

Registered User.

Super Moderator

Registered User.

Registered User.

Super Moderator

Registered User.

Registered User.

Registered User.

Registered User.

Registered User.

Attachments

Registered User.

Registered User.

Attachments

Registered User.

Registered User.

Registered User.

Attachments

Registered User.

Registered User.

Registered User.

Registered User.

Similar threads

Users who are viewing this thread