Error when using combo box to hide/unhide subforms (1 Viewer)

gojets1721

Registered User.
Local time
Today, 08:04
Joined
Jun 11, 2019
Messages
429
I'm trying to use a combo box to allow the user to select the subform they want to use. The subforms are frm2021, frm2020, and frm2019. My code is below. Any time I try to use the combo box, I'm getting a '2450' error which says access cannot find my referenced subform. Any idea why?

Code:
Private Sub ComboYearSelection_AfterUpdate()

    If Me.ComboYearSelection.Value = "2021" Then
        Forms!frm2021.Visible = True
    Else
        Forms!frm2021.Visible = False
    End If
   
    If Me.ComboYearSelection.Value = "2020" Then
        Forms!frm2020.Visible = True
    Else
        Forms!frm2020.Visible = False
    End If
   
    If Me.ComboYearSelection.Value = "2019" Then
        Forms!frm2019.Visible = True
    Else
        Forms!frm2019.Visible = False
    End If
   
End Sub
 

jdraw

Super Moderator
Staff member
Local time
Today, 11:04
Joined
Jan 23, 2006
Messages
15,364
Can you show us design of the form(parent) where these(2019,2020,2021) are subforms?
 

Gasman

Enthusiastic Amateur
Local time
Today, 15:04
Joined
Sep 21, 2011
Messages
14,044
I doubt that is the way to go anyway? :unsure:
Why not have one form and change the data for the respective year?
 

CJ_London

Super Moderator
Staff member
Local time
Today, 15:04
Joined
Feb 19, 2013
Messages
16,553
if these are subforms then you need to hide the control. assuming the control has the same name as your form

me.frm2021.Visible =Me.ComboYearSelection = "2021"

But agree with gasman - think you are going about this the wrong way
 

gojets1721

Registered User.
Local time
Today, 08:04
Joined
Jun 11, 2019
Messages
429
So like this?
Code:
Private Sub ComboYearSelection_AfterUpdate()

me.frm2021.Visible =Me.ComboYearSelection = "2021"
   
me.frm2020.Visible =Me.ComboYearSelection = "2020"
   
me.frm2019.Visible =Me.ComboYearSelection = "2019"
   
End Sub

In response to Gasman, I don't disagree. The DB is customer complaints and there's a form for each year. One big form would probably be simpler but it's not up to me. They like three for each year
 
Last edited:

jdraw

Super Moderator
Staff member
Local time
Today, 11:04
Joined
Jan 23, 2006
Messages
15,364
What is the design of your table(s)? Do you have 3 copies of the same table design - 1 for each year?
 

Gasman

Enthusiastic Amateur
Local time
Today, 15:04
Joined
Sep 21, 2011
Messages
14,044
So like this?
Code:
Private Sub ComboYearSelection_AfterUpdate()

me.frm2021.Visible =Me.ComboYearSelection = "2021"

me.frm2020.Visible =Me.ComboYearSelection = "2020"

me.frm2019.Visible =Me.ComboYearSelection = "2019"

End Sub

In response to Gasman, I don't disagree. The DB is customer complaints and there's a form for each year. One big form would probably be simpler but it's not up to me. They like three for each year
So what happens when 2022, 2023 comes around? :(
Plus despite having three, they are only seeing one at a time anyway? :unsure:
I could understand it, if they wanted to compare side by side, but......?

That is what I liked about my old place of work, the bosses would listen to ideas/reason. :)
 

gojets1721

Registered User.
Local time
Today, 08:04
Joined
Jun 11, 2019
Messages
429
So what happens when 2022, 2023 comes around? :(
Plus despite having three, they are only seeing one at a time anyway? :unsure:
I could understand it, if they wanted to compare side by side, but......?

That is what I liked about my old place of work, the bosses would listen to ideas/reason. :)
Yea I understand it's silly but ultimately it's not up to me. Just looking for guidance on the combobox piece lol
 

CJ_London

Super Moderator
Staff member
Local time
Today, 15:04
Joined
Feb 19, 2013
Messages
16,553
The DB is customer complaints and there's a form for each year. One big form would probably be simpler but it's not up to me. They like three for each year

the point is, you only need one subform, not 3, 4, 5 or more.

Assuming the recordsource to your subform includes a year field then you would simply set the subform linkmaster property to the name of your combo and the linkchild to the name of the year field. (don't use year as a field name - that is the name of a function). So no code required.

And if it doesn't (or can't) have a year field then you could use something like this (I've 'renamed' the subform control as frmYear)

Code:
Private Sub ComboYearSelection_AfterUpdate()

me.frmYear.form.filter="Year(complaintDate)=" & Me.ComboYearSelection
me.frmYear.form.filteron=true
  
End Sub

This (and other ways as well) means you don't have to create a new form and modify your main form every year
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 11:04
Joined
Feb 19, 2002
Messages
42,970
but it's not up to me. They like three for each year
Actually it is up to you. YOU are the programmer. YOU get to decide implementation methods. The user gets to tell you what he wants to see, and you figure out how to make it happen. There is no way in the world that the user could tell that you used a single form as you should with criteria to choose the year or a separate subform for every year. Right now you have three forms. In two months you will have four. WHY, unless you want job security, would you make a new form for each year?
 

Users who are viewing this thread

Top Bottom