Hiding/UnHiding Tab Control Pages based on Combo Box Selection

[Ina]

I've been struggling with this the whole day and appreciate any help.
I have a form with a subform(MedicalSubF) containing a Combobox(CaseCombo) with three Values – Reproduction; Health; Injury


On the same form I have a Tab Control(TabCtl27) with 3 pages. Each page has a separate subform.
Page0(Reproduction) with subform(MedicalRepF)
Page1(Health) with Subform (MedicalHlthF)
Page2(Injury) with SubForm (MedicalInjF)
The Form opens with the Tab Control invisible (as per the code in Form_Load Event in the Main Form.
When the user selects any of the values in the Combobox it should make the corresponding Page visible(as per the code in the After_Update Event of the Combobox).
When I make a selection from the Combobox I get a Run-time error ‘438’: Object doesn’t support this property or method with this line being yellow:
Me.Parent!TabCtl27.Enabled
I would also like to have code to ensure only one Page is visible at all times.
When the user selects a value from the combobox and the page shows up and he goes back to the combobox and select another one, the first one should be hidden again.

[June7]

Code should be posted as text between CODE tags, not as image.

Why use Enabled property? If you want to make tab control visible, use Visible property. However, not seeing the Enabled line in code image.

Combobox is on a subform and tab control is on main form? Why is CaseID all by itself in upper subform? Is CaseID from same table for both forms?

I have had to do this. Example from my project - note that the tab control is not referenced, just its pages; also focus is set away from tab control because any page that has focus cannot be hidden:

Private Sub ShowTabs()

With Me

.tbxSet = TestSet(.tbxLABNUM, "Soils & Aggregate")

.pge1.Visible = True
.pge2.Visible = True
.pge3.Visible = True
.pge4.Visible = True
.pge5.Visible = True
.pge6.Visible = True
.tbxLABNUM.SetFocus

'use Val to capture characters preceeding :### which will be present if gradation test selected
If Not Val(.tbxSet) Like "*1*" Then
    .pge1.Visible = False
End If
If Not Val(.tbxSet) Like "*2*" Then
    .pge2.Visible = False
End If
If Not Val(.tbxSet) Like "*3*" And Not Val(.tbxSet) Like "*E*" Then
    .pge3.Visible = False
End If
If Not .tbxSet Like "*P*" Then
    .pge4.Visible = False
End If
If Not .tbxSet Like "*V*" Then
    .pge5.Visible = False
Else
    .pge2.Visible = False
End If
If Not .tbxSet Like "*H*" Then
    .pge6.Visible = False
End If

If .pge6.Visible Then
    .pge6.SetFocus
ElseIf .pge1.Visible Then
    .pge1.SetFocus
ElseIf .pge2.Visible Then
    .pge2.SetFocus
ElseIf .pge3.Visible Then
    .pge3.SetFocus
ElseIf .pge4.Visible Then
    .pge4.SetFocus
ElseIf .pge5.Visible Then
    .pge5.SetFocus
End If

End With

End Sub

At least one page must always be visible so procedure starts with setting them all visible then hiding what is not needed.

For hiding, you could use:

Me.Parent.Page0.Visible = Me.CaseCombo = 1
Me.Parent.Page1.Visible = Me.CaseCombo = 2
Me.Parent.Page2.Visible = Me.CaseCombo = 3

Then to set focus:

Me.Parent("Page" & Me.CaseCombo - 1).SetFocus

But why bother with this? User could just as easily click on appropriate tab as select from combobox.

[Ina]

Thank you for your reply, June7.
I add the latter part of your suggested code to the Form_Load Event. Not sure about the SetFocus line?
I add the first part of your suggested code to the After_Update event of the Combobox. Again not sure if this is right?
I could have the combobox subform in a separate page as well, if that would make it easier?

The reason I want the page that has focus visible and all the others invisible, is the way I setup the pages.
The combobox writes the a value of the selection to a field called CaseID depending . If the user click on other pages because and realise they made the wrong choice, the CasID needs to change to whatever selection they make.. So forcing the user to go back to the combobox ensures that. Each page would in fact be a different record. I might need more code to be able to get that happened.

I attach a part of my db which will make it all clear. I'm not sure if the way I do this is the best approach??
Thank you for helping!!

[June7]

The MedicalAddMainF makes no sense.

You want each treatment to be a record - Why, since you have fields for treatments? Instead of a subform on each tab page, why not one subform for MedicalXDogT with a tab control and distribute its fields on each tab page? Eliminate MedicalAddMainF subform.

If each treatment should be a record, then should redesign table MedicalXDogT which is basically a spreadsheet. Have a Visits table then a related dependent table for treatments provided. Each treatment (Vaccination, Worming, Neutering, etc.) would be a record. Instead of a field for each treatment type, would be one field Treatment. Still eliminate MedicalAddMainF subform.

Can't use code from my project exactly - it was only an example - why did you put it verbatim into MedicalAddMainF ?. Of course it will error because my control names are not same as yours. TestSet() is a custom function in my project. Modify/eliminate as needed for your situation. I gave you some of the replacement code.

[Ina]

The MedicalAddMainF makes no sense.

You want each treatment to be a record - Why, since you have fields for treatments? Instead of a subform on each tab page, why not one subform for MedicalXDogT with a tab control and distribute its fields on each tab page? Eliminate MedicalAddMainF subform.

If each treatment should be a record, then should redesign table MedicalXDogT which is basically a spreadsheet. Have a Visits table then a related dependent table for treatments provided. Each treatment (Vaccination, Worming, Neutering, etc.) would be a record. Instead of a field for each treatment type, would be one field Treatment. Still eliminate MedicalAddMainF subform.

Can't use code from my project exactly - it was only an example - why did you put it verbatin into MedicalAddMainF ?. Of course it will error because my control names are not same as yours. TestSet() is a custom function in my project. Modify/eliminate as needed for your situation. I gave you some of the replacement code.

I'm still learning! I know the code would error but I didn't understand exactly what I should do with it. I only tried my best and wish I knew more.

The reason why each treatment should be a separate record is because the dates would be different for each and every treatment, except occasionally there might be two treatments on the same day. At the end of the day I need to get a report for each dog which shows by date all the treatments it had. Reproduction will be a separate report, as well as Vaccinations, Weights, etc. The only two that would go on one report would be Health & Injuries. Reproduction has a few sub-sections.

I don't understand what you mean by having a Visits Table and Treatment Table. I would love to know so I could do the change.
What I have is my Dog table where I have all my dogs with their basic information, eg dob, colour, gender, parents (if they were bred by me). I have a status field which distinguish between, Active Breeding Dogs, Outside Sires, Retired Dogs, Rehomed Dogs, Puppies up to 8 weeks old, and after that their status change to sold. I also have links their to tables which are relational to each dog, eg. Litter Table, Buyer Table linked to each puppy in my Dog Table, DNA Table, and also a Daily Check List Table which stores a few records per Active dog per day.

Looking forward to get more specific advice and guidance on how to implement it.

Thank you for your time!!

[CarlettoFed]

First you have to rename the Close button to butClose then you have to change the code on the OnLoad event of the form to the following:

Private Sub Form_Load ()
      Me.butClose.SetFocus
      Me.Pge1.Visible = False
      Me.Pge2.Visible = False
      Me.Pge3.Visible = False
      Me.Pge4.Visible = False
      Me.Pge5.Visible = False
      Me.Pge6.Visible = False
      Me.Pge7.Visible = False
 End Sub

[Ina]

Thank you so much for your reply, CarlettoFed! I did that and I still get a Run-time Error 2452 The expression you entered has an invalid reference to the Parent property.

First you have to rename the Close button to butClose then you have to change the code on the OnLoad event of the form to the following:

Private Sub Form_Load ()
      Me.butClose.SetFocus
      Me.Pge1.Visible = False
      Me.Pge2.Visible = False
      Me.Pge3.Visible = False
      Me.Pge4.Visible = False
      Me.Pge5.Visible = False
      Me.Pge6.Visible = False
      Me.Pge7.Visible = False
 End Sub

[June7]

Again, MedicalAddMainF subform makes no sense. Typing or selecting into CaseID combobox will either change data in existing record or create a new record with no data other than a record ID. Then when you enter into each treatment subform will edit or create a new record in each. I think this combobox should be UNBOUND and placed directly on MedicalAddF header and eliminate MedicalAddMainF.

Code behind MedicalAddF would not use Parent prefix because it is the Parent.

As I said, at least one tab page must always be visible which means cannot set them all not visible at same time. Set them all visible then code hides what is not needed.

You reference a Pge8 which does not exist, should be Pg7.

CaseCombo is not a control on treatment subforms so that code errors.

Check out this revision:

[Ina]

I'm desperate to get this right. Is it possible to get some sort of example database on how to structure the health aspect of my database.
Let me explain how we do it.
The dogs get wormed once a month, Puppies every two weeks.
The dogs get vaccinated once a year, puppies once at 6 weeks of age.
The dogs get weighed once a month, puppies every day for the first 2 weeks and then weekly up to 8 weeks.
When a dog gets diarrhea or any other illness, we either treat them, depending on the seriousness, or we take it to the vet.
When a dog get injured we take it to the vet.
If the vet prescribe medication it needs to be recorded twice or three times a day.
Some births are done by C-section
General surgeries, eg desexing, teeth jobs, etc.

The Reproductive section has 8 sub sections:
Breeding Clearance
Post Partum Clearance
Progesterone Test
Artificial Insemination
Ultrasound
Semen Collection
Semen Evaluation
Check Up

At the end of the day I need to get a report for each dog which shows by date all the treatments it's had. Reproduction will be a separate report, as well as Vaccinations, Weights, etc. The only two that would go on one report would be Health & Injuries.

[June7]

Did you check out the revised db? I did not alter data structure, just design of MedicalAddF and its subforms.

[Ina]

Sorry June7, I didn't see your reply. I'll have a look at it now.

[Ina]

Did you check out the revised db? I did not alter data structure, just design of MedicalAddF and its subforms.

Exactly what I was looking to achieve! Thank you so much!
I just had to add a line to the Form_Load Event of all the subforms to open on a new record. Also put a Select Statement in the Record Source of the Subform to only show records equal to the current CaseID and equal to the current DogID.
All sorted! Thank you very much for your help, very much appreciated
I will mark it as Solved.