Add date to table at start of each month

[DubCap01]

Hi all,

I have a table with one field - StartDate, and it is populated with the first day of each month going back however many months. It is purely the first date of each month. I use it for when people need to narrow down their query searches to a particular month, and the startdate is a good 'starting point', excuse the pun.



I want to try and automate adding the first day of a new month so that I don't have to manually type it in as a new entry. It doesn't get added until obviously the new month starts, so 1st July 2015 won't get added until the morning of Wednesday 1st July!

Does anyone know how I could accomplish this via code?

thanks in advance
Pete

[orange]

How about giving us a description of your database and business requirement --simply English no jargon?

I use it for when people need to narrow down their query searches to a particular month, and the startdate is a good 'starting point', excuse the pun.

Also could you tell us what users are trying to find and why they have to narrow down the query?

I'm sure readers will have options once they understand your needs.

[DubCap01]

Hi Orange,

The TblDates which stores the PK (#1/1/2015#, #1/1/2015#, etc) is referenced thru combo box, and once a value from that combo box is selected (ie June-15), it provides the date range (first and last days of June-15) for a subform (or sometimes a report's underlying query)

So, just being able to automate adding the first day's 'date' of the current month to TblDates fixes the problem of not having the current month displayed in any combo box. PK takes care of multiple attempts

cheers
Pete

[orange]

In database PK usually means Primary Key. Are you saying you have a function named PK?
Can you step back and describe in plain English what and why you are trying to so something with this first day of the month?

You can always find the current month by using the Month() function and supplying today's date Date. So Month(Date) gives you June, and Year(Date) gives the Year. You can use StartDate =CDate("01" & "/" & Month(Date) & "/" & Year(date)).

I'm not following the need for this table.

[DubCap01]

In plain English, User Joe opens a form, which contains a subform. The recordsource for the subform relies upon the FIRST and LAST days of any particular calendar month, and populates on AfterUpdate of the CboMonth combo Box. CboMonth (formatted to show Mar-15, Apr-15,etc) must contain the current month. I'd just like to go on vacation knowing that whilst I am away the Combo box can read all entries from the table TblDates and know that, on the 2nd day of July this year, the entry 1-July2015 will exist without someone having to manually enter it!!!

In this case TblDates has ONE field. It is a Primary Key field. I thought that a reference to PK would be assumed to be Primary Key
The Table stores the first day of each month, progressively. So, when July comes around, I'd like to get SOMETHING (of my choice, say opening a particular form which relies on the TblDates being current and updated) to send a signal (via VBA) to TblDates to say:

HEY! It's the second day of July 2015! If you don't have 1-July-2015 as one of your entries, then add it now!

Orange, please try to remember that users on this forum may not have the VBA knowledge that you have, and sometimes (as you referred to earlier in this thread) have to put it in plain English. PK that I referred to meant that TblDates has one field, it is the Primary Key field. It is not a Function. So any further attempt to add 1-July-2015 to the field once it exists would be discarded.

Does that explain it better?
I am quite happy to get a function to execute this, if that helps

cheers
Pete

[orange]

Pete,

I'm trying to get you to tell me and readers WHAT you are trying to do in plain English. My example with startdate was just an attempt to show you can get the 1st day of a month programmatically -using built in functions in Access. Many developers would use a popup form with 2 textboxes with Calendars to get a Begin and End Date, and use those values as criteria in a query.

However, you have expressed HOW you have done something

....and populates on AfterUpdate of the CboMonth combo Box. CboMonth (formatted to show Mar-15, Apr-15,etc) must contain the current month. I'd just like to go on vacation knowing that whilst I am away the Combo box...

and we still don't know the something.

Plain English no jargon doesn't include after update and cboMonth in my view. Those elements may in fact be part of the solution in the end, but it seems you have jumped to one solution approach (with which you are having difficulty) before readers fully understand your requirement.

I'm not challenging your solution. I'm trying to understand the issue and identify options. If your solution works for you and your users, then go for it.

[cbende2]

DubCap1,

What Orange is saying is that you already have a thought out solution, you're just not sure how to do it.

What Orange wants to do is here the process of what you're trying to do. We may be able to come up with a better and more efficient solution if we know exactly what you want and how its being used.

After reading the entire thread, I'm still confused as to what you want as well....

[DubCap01]

THIS..... is what I was trying to achieve, and figured it out myself. Thanks for everyone's input.

Private Sub Form_Activate()
If DMax("StartDate", "TblDates") < Date - 28 Then
Dim Rs As Recordset
Set Rs = CurrentDb.OpenRecordset("TblDates", dbOpenDynaset)
Rs.AddNew
Rs![StartDate] = DateAdd("m", 1, DMax("StartDate", "TblDates"))
Rs.Update
Rs.Close
Set Rs = Nothing
Else
Exit Sub
End If

End Sub