How to Create Monthly Bill Due Dates

[JR_1987]

I'd like some help figuring out how to have a form (that I will name frmBudget) generate recurring monthly due dates. These due dates will be for a given amount of time, and set at a specified frequency. I have set up a table (tblMonthlyBills) with the following fields:

ID
AccountName
EstimatedBudgetAmount
StartDate
EndDate
Frequency

Most of the records I will be creating will have a "Monthly" frequency. However, because I have one account where the bill occurs on a "Weekly" basis I'd also like to include this option as well as options for "Quarterly" and "Annually" in a Combo Box that I will name "cboFrequency." These four types of payment due dates will be scheduled between a StartDate and an EndDate. After clicking on a button that I will name "btnSubmit" in the form I would like the information that I entered to generate a list of due dates. I am not sure if this is done through simple query, on a table, or some other method that I'm unfamiliar with. So I may need additional pointers here.

I'd like the Monthly dates to repeat on the exact day of the month as the StartDate. For example, I'd like the Mortgage to show due on the 1st of every month, and the Electric bill to show due on the 23rd of every month. I'd also like the Weekly dates to repeat on the same day of the week as the StartDate. For example, I'd like the Daycare to show due on every Friday.



I'm not new to access, but I am also not an expert. This has proven to be quite challenging. I would greatly appreciate any input. Thank you.

[ranman256]

I have code that runs at the db start,
pit checks tLog table if billing was run for the month (or week ,or qtr)
a pop up shows.

i go to the billing screen, and run billing for that Period.
it puts a charge on customers accts, and puts a record in tLog.
it can also generate bills, paper or email.

[ssanfu]

This will require VBA, but there is a lot to consider.

Say the "StartDate" is 1/1/2019 and the "EndDate" is 12/1/2019. Are you clicking the button once per month or do you want to click the button once and have 12 records created?

Are AccountNames like "Sam Smith", "Jim Jones" or are they "Electric", "Rent", "Daycare"?



Maybe Start with these tables.(not sure of specific requirements/terms):
tblAccounts
---------------
AccountID_PK - Autonumber
FrequencyID_FK - Number - Integer
AccountName - Text
EstimatedBudgetAmount - Currency
StartDate - DateTime
EndDate - DateTime
DueDate - Number - Integer (for Weekly, specific Day of week (1 - 7), for Monthly, Quarterly, Annually, specific Day number)


tblFrequency
---------------
FrequencyID_PK - Autonumber
Frequency - Text


tblBudgets
-------------
BudgetID_PK - Autonumber
AccountID_FK - Number - Long
Amount - Currency
BudgetDate - DateTime

[JR_1987]

Thank you very much for your reply, ssanfu.

Ideally I would like for the button to create multiple records. As you suggested in your example, clicking the button would then generate 12 records.

As for AccountNames your second set of examples would be correct. They are accounts such as the Electric, Mortgage, and Daycare.

I assumed I would have to break down the table into multiple tables and relate them somehow. You mentioned there is VBA involved... The closest I came to an answer was an amortization schedule but couldn't find code that I could make sense of. Is there some kind of loop involved?

[ssanfu]

By the way.... Welcome to the forum..

Did you design your dB using pencil and paper? (or whiteboard, cardboard, etc?)
Do you have a dB started? If yes, would you post it?


It might help you to work through the tutorials at Rogers Access Library


Yes, looping is involved.

Say you have selected the account "Electric" and the dates are July 1, 2018 through Dec 31, 2018.
When you click the button, the first thing you have to check is if there is already a record for "Electric" with a date of {SomeMonth} 1, 2018,
If there IS a record, get the next date and check again, otherwise, (if there is NOT a record), create a record.
Do this until the month of Dec is reached.

It is not hard, you just have to think of everything...

[JR_1987]

Thank you for the welcome ssanfu.

So I've gone ahead and created the tables as you indicated above in your first response with one minor change. I broke the last field [DueDate] of tblAccounts into two fields as follows (I can change these back to one field if it proves to be more challenging the way I've done it):

tblAccounts
------------------
DueDate - Number - Integer (Monthly, Quarterly, Annually, specific Day number)
WeekdayID_FK - Number - Integer (for Weekly, specific Day of the week)

Then I created an additional table so that I could create a combo box in form for the days of the week as follows:

tblWeeklyFrequency
-------------------------
WeekdayID_PK - Autonumber
Weekday - Text (7 records total listing Monday through Friday)

Now for my long list of questions (I apologize in advance):

In your "Electric" scenario would clicking the button search through records and create them automatically? Or would clicking the button toggle through records, then let me know if there is an existing record where then I would have to manually enter then next day in the sequence? The former is what I'm looking for. Not so much the latter.

Furthermore, am I correct to assume that the records that will be created will then be logged in the tblBudgets table? If so is done through some kind of query?

Lastly, would you be able to provide an assist with some generic VBA to get the ball rolling. This is the furthest progress I've made with this issue and your input has been tremendously helpful so far. Thank you.

[ssanfu]

Be happy to help with the code.

New table: "tblWeeklyFrequency" - sounds good.

In your "Electric" scenario would clicking the button search through records and create them automatically?

Depends on what you want. I envisioned having a control for the "Account", a control for the start date and a control for the end date. the code would read the values, do a search to see if a record exists for the "account" on the start date. If no record exists, create a record.
Rinse, repeat until the end date is reached.

This way you would never have duplicate records. There are other methods: you could set an index on the Account and the date and have an error handler that handles if a duplicate record tried to be created, it wouldn't be allowed.


Maybe post your dB so I can see where you are at???

[JR_1987]

Hello again! I'd like to post the database however it does have a lot of personal information that I don't want place in a public forum. Essentially, though I have created the tables you recommended. Additionally, I've used some VBA that has gotten me off to a start.

Here's What I Have So Far:

Private Sub btnCreate_Click()


Dim Db As Database
Dim Recordset As Recordset
Dim StartDate As Date


Set Db = CurrentDb
Set Recordset = Db.OpenRecordset("tblMonthlyBudgets")
StartDate = Me.StartDate


Do Until StartDate > Me.EndDate
Recordset.AddNew
Recordset("BudgetDate").Value = StartDate
Recordset.Update
StartDate = DateAdd("m", 1, StartDate)
Loop


End Sub

This has worked. After clicking the "Create" button I have the correct amount of records in sequential order. In this case clicking the button has created the following dates:

1/1/2018
2/1/2018
3/1/2018
4/1/2018
5/1/2018
6/1/2018

However there are still some issues that I've encountered...

Issue With The Date

I know the DateAdd function is only adding one month until the EndDate specified. However, as mentioned above with the DueDate field, how would I go about inserting the desired day that the bill is due (in this case it would be the 16th) to reflect the following?:

1/16/2018
2/16/2018
3/16/2018
4/16/2018
5/16/2018
6/16/2018

This does well to add an additional month to the StartDate, however, I also have options for Weekly, Quarterly, and Annually which I assume would be written as:

StartDate = DateAdd("ww", 1, StartDate)
StartDate = DateAdd("qq", 1, StartDate)
StartDate = DateAdd("yyyy", 1, StartDate)

What kind of code can I include to have the database determine which of these four (Monthly, Weekly, Quarterly, Annually) frequencies to use from the cboFrequencyID combo box on my form (Control Source is FrequencyID on the tblMonthlyAccounts table)? It seems like an IF statement would be necessary here.

For example (while keeping in mind the following):

tblMonthlyAccounts...........frmBudget

FrequencyID....................... cboFrequencyID
AccountName...................... cboAccountName
EstimatedBudgetAmount...... txtEstimatedBudgetAmount
StartDate............................ txtStartDate
EndDate............................. txtEndDate
DueDate............................. txtDueDate
WeekdayID......................... cboWeekdayID


IF I were to select MONTHLY in cboFrequencyID with a txtStartDate of 1/1/2018, a txtEndDate of 6/1/2018, and a bill txtDueDate of the 16th then tblBudgets would populate the six records shown below:

1/16/2018
2/16/2018
3/16/2018
4/16/2018
5/16/2018
6/16/2018

HOWEVER, IF I were to select WEEKLY in cboFrequencyID with a txtStartDate of 1/1/2018, a txtEndDate of 2/28/2018, and a bill cboWeekdayID of 6 (for "Friday") the tblBudgets would populate the 8 records shown below:

1/05/2018
1/12/2018
1/19/2018
1/26/2018
2/02/2018
2/09/2018
2/16/2018
2/23/2018

How will this be represented in VBA where I'd like the database to know that when I select either MONTHLY, QUARTERLY, or ANNUALLY from cboFrequencyID the date in the txtDueDate field is what should be used to populate those desired due dates. Whereas if I were to select WEEKLY from cboFrequencyID the DAY selected from cboWeekdayID is what should be used to populate those desired due dates.

Issues With The Rest of the Record

Although the code I have used above works well for the dates it omits the rest of information that should populate the tblMonthlyBudget table ("AccountID" and "Amount"). I did add some code after the DateAdd function that partially did what I wanted it to do.

Here Is What I Used:

Recordset.AddNew
Recordset("Amount").Value = Me.EstimatedBudgetAmount
Recordset.Update

This worked in the way that it produced 6 records at the amount that I entered in the EstimatedBudgetAmount text box on the frmBudget Form. However, the BudgetDate field and the Amount fields alternated with data. In other words, I ended up with 12 records in all. One record with a date followed by a subsequent record with an amount. What I was hoping for was 6 records with both the amount and its respective date on the same record.

I know this is a lot and I apologize for any confusion, but I do appreciate all your help. Thank you!