Dynamically changing the control source of a textbox

[Merlene]

Good Day,

I am attempting to change the control source of a textbox (Ms Access 2013) based on the quarter; however; the quarters I am using are not standard quarters. I've tried several options but nothing has worked so far. My quarters are:
November - January
February - April
May - July
August - October
The following is the last thing I've attempted (there were several previous unsuccessful attempts):

Private Sub Form_Load()
 
    If MonthName(DatePart("m", Date)) = "October" Then
        Me.PreviousActionPlan1.ControlSource = "Test"
    Else
        If MonthName(DatePart("m", Date)) = "November" Then
            Me.PreviousActionPlan1.ControlSource = "Test"
        End If
    End If
 
End Sub

I didn't bother doing the code for all months as I was testing as I go along and it isn't working. I thought maybe I have to DIM the result of the MonthName Function as text but I'm just guessing.

Any assistance is greatly appreciated.

[twinnyfo]

Merlene,

Forgive me for the length of this response, but I will discuss a few things before I directly address the question--but these things are related....

Let's just go in order of appearance of my discussion points:


1. Using MonthName()
There is no need to use such a complicated formula to achieve what you want here. An alternate would be:

If Month(Date) = 10 Then

2. Naming Conventions for Controls
If you troll this forum enough, you will run across me ranting about proper naming convetions--particularly for controls on Forma nd Reports. When you list Me.PreviousActionPlan1, I don't know whether that is referring to the Control that is named PreviousActionPlan1 or the underliying Field that is named PreviousActionPlan1. This is because the default naming conventions (that MS Access assigns to all new drag-n-drop-from-the-field-list fields is the name of the Field. This can cause some problems (as you continue developing in MS Access you will found out more about these problems), because that name is now ambigous. Just like I can't say for certain what you are referring to, MS Access will have the same problems when figuring our what to do. So, renaming the Control to something like txtPreviousActionPlan1, will make it not only clear to me that you want to set the control source of the Control, but it will also be clear to MS Access (it does have a mind of its own, by the way).

This bit about naming conventions is a standard principle in our community, and I highly recommend you take it to heart and exercise the practice regularly.


3. What are you setting the Control Source to?

.ControlSource = "Test"

Indicates (from the code snippet provided), that you always want the Control Source to be "Test". I am sure that is not what you want, due to the nature of your question. However, in general, your code should work to assign a new Control Source. It is better--in circumstances like these--to have an "Unbound" Control, meaning that there is no Control Source assigned to that Control in the first place.


4. A more direct answer to your question.
I don't know exactly what the layout of your Form is, so part of this is a guess that you want to show the current quarter and the next three quarters? (Whenever folks are dealing with quarters, it's usually financial, and the either want to look at preceding or upcoming quarters. THe general principle behind a solutino to this has less to do with constantly changing the control source of a control than it does with modifying the query underlying the form to reflect the data you want to portray.

That's the theory, here's the practical. Instead of having an underlying record source with output fields of "2018-Q4", "2019-Q1", "2019-Q2", etc., which are constantly changing as you pass through the calendar year, I would suggest that what you want is a query with output fields of "Q1" (in which the Quarter of the Current date is in Q1--i.e., today is Q4 2018 according to your list, but for this purpose would eb Q1), "Q2", "Q3", etc., which are constant, but the quarter they represent changes as time passes through the calendar. Thus, the controls on your forms (and reports) can have "Q1", "Q2", etc. as a Control Source. This precludes the need to change things programmatically--which can be clumsy, and mostly unecessary.

So to do this, you need a formula in your query (again, I don't know what your data set looks like) that converts the date of any transactions to Q1-Q4.

It would be helpful to see your underlying query and we could probably work from there.

[Merlene]

Thanks Twinnyfo, I admit that I should have assigned a better name, I am just starting off with Access and I would learn these lessons the hard way if they aren't pointed out. So I will definitely be more mindful going forward.

I put the "Test" in there only in an effort to see if what I was attempting was working. I actually want the form to pull from a different field based on the quarter that we are in. As each quarter's plans are different and have to be kept. Since after designing this database there will be users who are even more unfamiliar with Access I was attempting to have it do this on its own so that they persons running the report would not have to use the queries etc.

I hope I was able to accurately explain the situation.

[twinnyfo]

Agreed, your users should never have to touch the queries. But that is not where I am going with this.

What does the underlying query look like? Then we can work from there.

By the way, I have been mulling over this problem and putzing with possible solutions, and one of your more significant challenges is the breakout of your quarters. The standard (or even Fiscal) quarters have lots written about them for delineation. Yours will ikely be a challenge. But that is what we have to work with so we will drive on.

[NeoPa]

I think you've identified the one question that we can deal with in here Twinny.

@Merlene.
If you look at some of the threads that are put there to help you to post questions that we can work with most efficiently you'll find that we have a single question per thread rule here. Yours includes allusions to various questions - a bit like a request for a project plan. Going forward we'll deal with the first main fundamental question in your thread which is how to determine which quarter you're in. Anything beyond that will need to be requested separately in it's own thread.

So, as we're dealing with a question that hasn't been fully and clearly specified I'll start by assuming you're after a quarter indicator of 1 - 4 such that the year doesn't matter, the first quarter starts towards the end of the calendar year in November and the last quarter finishes at the end of October. A function that will return that value has been included below to get you started :

Public Function GetQuarter() As Integer
    GetQuarter = 1 + ((Month(Date() + 10) Mod 12) \ 3
End Function

[Merlene]

Thanks Twinny & Neo. I will set the quarters first and proceed from there. I will start a new post based on my progress after.

[twinnyfo]

NeoPa,

Humbly submitted, this function returns the correct quarters:

Public Function GetQuarter(dtDate As Date) As Integer
    GetQuarter = 1 + ((Month(dtDate) + 1) Mod 12) \ 3
End Function

The previous function was missing a parentheses, but only calculated today's date (as well as returning incorrect quarters).

[Merlene]

Thanks Twinny, yes I realized the closing parentheses was missing, I only tested for today's date so didn't pick up on the rest. Thanks again for the correction.

[NeoPa]

Darn it Twinny. You're so picky!

You also happen to be spot on. From the OP I took the question to be specific to the current date, so I suspect I was fine on that score - but I did miss out a closing parenth :-(

I also (deliberately in my confusion) set the November to January quarter as 4 instead of 1. I blame that on my Stylish scripts no longer working and my Bytes.com experience being the default layout which, let's just say, isn't my preference.

@Merlene.
Good for you. We'll look out for your next question thread.

PS. Just to be clear my original should have had another closing parenthesis after the call to Date().