Solved Fill dropdown box with years and months (1 Viewer)

Birrel88

Registered User.
Local time
Today, 11:52
Joined
Sep 22, 2017
Messages
21
Hello there,

I hope someone can help me with this issue.
I trief solving it myself but I can't figure out how to do it.

The problem:
I have a report which shows monthly income made from different customers.
When you want to open this report, you go thru a form.
This form consists of 2 comboboxes. The first one is to select which year you want to show, and after this is selected the second combobox is for selecting the month.
After that, you press the button and it opens the correct form.

Until now this worked perfectly. What I did was let the first combobox (year) search in the table (which consists all income data) and made a DISTINCT query to filter out which years data was put in.
Then the second combobox also searches in the same table, but looks at all the months data was put in with a criteria of the selected year.

Until now almost every day data was put in so it wasn't a problem. But due to the corona crisis the last month no data was put into the table.
But the user still want's to print the report, even if it shows 0 income.

Because no data was put in, the comboboxes won't show the month (April 2020). Because it can't find that month in the table.

What do I want to create?:
I want the comboboxes to show every year and month. Startdate: January 2018, enddate: Current month.

What I tried:
I tried making a new table. Put in 2 fields (next to the autokey field).
1 for the year, 1 for the month.
I manually put in the years and months from Januari 2018 until this month (May 2020).

I then let the 2 combo boxes not search in the income table, but get all the data from the newly created table.
That works.

Then I tried doing some vba code on the Form_Load at the first form that loads when opening MS Access.
That vba code (in my mind) has to check when the program is opened, if the current month is already in the table, if not: add it to the table and continue.
It also has to check a couple months back. I was thinking 3 months back. So if the program wasn't opened in the last 3 months, it also has to add those 3 months.
In reality, the program is opened at least 6 days in the week. But to make it failsafe I thought, 3 months will be ok.
Even better if I can make it so it checks back until 2018-1 (The first month data was put in the system)

I just can't figure out how to do this check.
I've used a lot of nested If/Else, tried with a Do.. loop to check those 3 months. But I can't seem to find the correct logic to use.

Am I making this too hard for me? And is there a simple solution to this problem?

I hope someone can help.
Thanks in advance
 

Micron

AWF VIP
Local time
Today, 14:52
Joined
Oct 20, 2018
Messages
3,476
Is there a reason why you don't just use 2 textboxes formatted to date and use the built in calendar control for them, choosing a begin and end date? Normally I'd say you can end up with no records when you provide date ranges for which there is no data, but you want to do that anyway. You'd pick the begin and end of a month, or months range, or for a single day, the same date. You would have to validate your date choices of course.
 

Birrel88

Registered User.
Local time
Today, 11:52
Joined
Sep 22, 2017
Messages
21
Thank you for the quick reply.

The reason why I don't choose for 2 date formatted textboxes and pick a range is that I want the program to be very easy for the user.
I want the user to just select a year and a month. Not pick a daterange where it is also possible to accidently pick a range from let's say October 2nd, 2019 till October 31st, 2019. Then the form won't show the first day of the month.

I know it's not very hard to do that wrong, but even if the odds are very low I wan't to prevent that to happen.
 

plog

Banishment Pending
Local time
Today, 13:52
Joined
May 11, 2011
Messages
11,611
The first one is to select which year you want to show, and after this is selected the second combobox is for selecting the month...
...What I did was let the first combobox (year) search in the table (which consists all income data) and made a DISTINCT query to filter out which years data was put in.
Then the second combobox also searches in the same table, but looks at all the months data was put in with a criteria of the selected year.

Leave the year one as it is--that should still work. For months, hard code the 12 values as the list for the combo box to use.

Of course the issue you are probably going to have is if the user selects data just for April 2020 The report will not show just 0's it will come up blank.
 

Birrel88

Registered User.
Local time
Today, 11:52
Joined
Sep 22, 2017
Messages
21
Well. It seems taking a shower and smoking a cigarette helped getting my thinking process activated again.
Got a revelation and after looking up how to do it, got a solution that works (hopefully) for now and the future.

Ditched the table with the years and months, and put up some vba code to populate the 2 comboboxes at form_load and when the year is selected.

Thanks for trying to help me. The support on this forum is always great and quick.
I put up the code for reference when somebody else is searching for it.

I know, it's kinda messy. Access and vba is a hobby, never had real training in it, still have to make myself code less messy.

Code:
Private Sub Form_Load()
' Lock month cbo and open report button
Me.cboMaand.Enabled = False
Me.cmdOpenReport.Enabled = False

' Put years in array (From 2018 till current year)
Dim jarenArray() As Integer
Dim intCounterJaar As Integer
Dim intHuidigJaar As Integer

intCounterJaar = 2017
intHuidigJaar = CInt(Year(Date))

Dim a As Integer
a = 0
Do While intCounterJaar < intHuidigJaar
    intCounterJaar = intCounterJaar + 1
    ReDim Preserve jarenArray(a)
    jarenArray(a) = intCounterJaar
    a = a + 1
Loop

' Place years from array in cbo
For i = LBound(jarenArray) To UBound(jarenArray)
    Me.cboJaar.AddItem (jarenArray(i))
Next

End Sub

Code:
Private Sub cboJaar_Change()
Dim intGeselecteerdJaar As Integer
intGeselecteerdJaar = Me.cboJaar.Value

' Enable month cbo
Me.cboMaand.Enabled = True

' Clear month cbo for a fresh start
' If I don't do this it will keep adding the months when reselecting the year
Me.cboMaand.RowSource = ""

' Add all previous months and current month in an array (from January 2018)
Dim maandenArray() As Integer
Dim intCounterMaand As Integer
Dim intHuidigMaand As Integer

intCounterMaand = 0
intHuidigMaand = CInt(Month(Date))

If intGeselecteerdJaar = CInt(Year(Date)) Then ' If selected year is current year
    ' Place previous months including current month in array
    Dim x As Integer
    x = 0
    Do While intCounterMaand < intHuidigMaand
        intCounterMaand = intCounterMaand + 1
        ReDim Preserve maandenArray(x)
        maandenArray(x) = intCounterMaand
        x = x + 1
    Loop
ElseIf intGeselecteerdJaar < CInt(Year(Date)) Then ' If selected year is before current year
    ' Place all 12 months in array
    For i = 0 To 11
        ReDim Preserve maandenArray(i)
        maandenArray(i) = i + 1
    Next i
End If

' Put months from array in cbo
For i = LBound(maandenArray) To UBound(maandenArray)
    Me.cboMaand.AddItem StrConv(MonthName(maandenArray(i)), vbProperCase) & ";" & maandenArray(i)
Next

End Sub
 

pbaldy

Wino Moderator
Staff member
Local time
Today, 11:52
Joined
Aug 30, 2003
Messages
36,118
I'm curious why you have a two-step process using an array to populate a combo (and you are far from the first I've seen do it by the way). The array strikes me as an unnecessary middle-man. Why not just populate the combo in the first loop, like:

Code:
Do While intCounterJaar < intHuidigJaar
    intCounterJaar = intCounterJaar + 1
    Me.cboJaar.AddItem intCounterJaar
Loop
 

Birrel88

Registered User.
Local time
Today, 11:52
Joined
Sep 22, 2017
Messages
21
I'm curious why you have a two-step process using an array to populate a combo (and you are far from the first I've seen do it by the way). The array strikes me as an unnecessary middle-man. Why not just populate the combo in the first loop, like:

Code:
Do While intCounterJaar < intHuidigJaar
    intCounterJaar = intCounterJaar + 1
    Me.cboJaar.AddItem intCounterJaar
Loop

You are right. Now I see this it makes it so much simpler.

There is no reason why I put in in an array first. I think it's because when looking for a solution I first came on a website where I learned how to put things from an array in a combobox. After that I looked up how to out things in an array in vba. Guess I never really got to the conclusion that I can put the things in a combobox directly.

Still learning a lot every day. Thank you.
 

pbaldy

Wino Moderator
Staff member
Local time
Today, 11:52
Joined
Aug 30, 2003
Messages
36,118
No problem. We've all got those things where we learn some way of accomplishing something and since it works we move on. Often it takes another pair of eyes to see the potential improvements.
 

Birrel88

Registered User.
Local time
Today, 11:52
Joined
Sep 22, 2017
Messages
21
I edited the code, and removed the array.
Makes it much simpler and less code.

Thank you !

Final code:
Code:
Private Sub Form_Load()
' Disable month cbo and open report button
Me.cboMaand.Enabled = False
Me.cmdOpenReport.Enabled = False

' Place all years from 2018 into the cbo
Dim intCounterJaar As Integer ' Which year is currently added in the loop
intCounterJaar = 2018 ' First year that has to be added

' Add every year from start until current year to the cbo
Do While intCounterJaar <= Year(Date)
    Me.cboJaar.AddItem (intCounterJaar)
    intCounterJaar = intCounterJaar + 1
Loop

End Sub

Code:
Private Sub cboJaar_Change()
' Enable month cbo after year is selected
Me.cboMaand.Enabled = True

' Erase month cbo, this prevents months being added again and again after changing year again
Me.cboMaand.RowSource = ""

' Place selected year in integer
Dim intGeselecteerdJaar As Integer
intGeselecteerdJaar = Me.cboJaar.Value

' Put all months from selected year in cbo
Dim intCounterMaand As Integer ' Which month is being added in the loop
intCounterMaand = 1 ' First month which has to be added (1=January)

If intGeselecteerdJaar = Year(Date) Then ' If selected year is current year
    ' Put all months including current month in cbo
    Do While intCounterMaand <= Month(Date)
        Me.cboMaand.AddItem StrConv(MonthName(intCounterMaand), vbProperCase) & ";" & intCounterMaand
        intCounterMaand = intCounterMaand + 1
    Loop
    
ElseIf intGeselecteerdJaar < Year(Date) Then ' Selected year is earlier then current year
    ' Place all 12 months into cbo
    For i = 1 To 12
        Me.cboMaand.AddItem StrConv(MonthName(i), vbProperCase) & ";" & i
    Next i
End If

End Sub
 

Users who are viewing this thread

Top Bottom