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
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