Creating dates in a table automatically

TimboWalker

New member
Local time
Today, 05:28
Joined
Dec 29, 2010
Messages
2
Hi

I am pretty new to Access, and want to be able to autocreate dates in a calendar table for a payroll database that I am creating. If possible, I would like to be able to use a drop down list for weekly or monthly, and depending on this selection, create either 52 dates adding on to the first date for weekly (i.e if first date is 2nd January, 2011, auto create 9th, 16th, 23rd etc) and if selection is monthly, autocreate 31st Jan, 28th Feb, 31st March etc)

I know that it would be easier to buy software, but I work from an offshore Island, and there is not a great choice of cost efficient software available, so looking to do some basic development myself! Thanks in advance.

Tim
 
Here is some code you can use in combo boxes to generate self perpetuating dates

Code:
Function ListFridays(Fld As Control, id As Variant, _
    row As Variant, col As Variant, Code As Variant) _
     As Variant
    Dim intOffset As Integer
    Dim szDate As Date
    szDate = DateAdd("m", -4, Now())
    
    Select Case Code
        Case acLBInitialize            ' Initialize.
            ListFridays = True
        Case acLBOpen                    ' Open.
            ListFridays = Timer        ' Unique ID.
        Case acLBGetRowCount            ' Get rows.
            ListFridays = 18
        Case acLBGetColumnCount    ' Get columns.
            ListFridays = 1
        Case acLBGetColumnWidth    ' Get column width.
            ListFridays = -1            ' Use default width.
        Case acLBGetValue                ' Get the data.
            intOffset = Abs((9 - Weekday(szDate)) Mod 7)
            ListFridays = Format(szDate + _
             intOffset + 7 * row, "dd/mm/yyyy")
            ListFridays = DateAdd("d", 4, ListFridays)
    End Select
End Function

In your combo box simply replace your rowsource with ListFridays()
 
To add to a table you can use the following code. The fields in blue need to be changed.
As far as the [FirstDate] and [LastDate], using code like DCrake in combo boxes will work. Or, better still, code that runs everytime the db opens that adds the next weeks dates in automatically.

I have an app that revolves around a "main" table. The PK in this table is the date. I require that every date for the month is present. My code that runs at startup is something like this:

If max date < last day of this month then run "make dates code" where start = first date of month and end = last date of month.

So the code only runs on the 1st day of the month. There is no way of deleting records from the table. And the dates in the table cannot be edited or added by the user other than via the code. I always have all the date in this month every time I open the DB. Big fail safe!

This way
Code:
Dim rst As DAO.Recordset
Dim db As DAO.Database
Dim D As Date

    Set db = DBEngine(0)(0)
    Set rst = db.OpenRecordset("[COLOR="Blue"]Table1[/COLOR]", dbOpenDynaset, dbAppendOnly)
[COLOR="SeaGreen"]'Note the dbAppendOnly. If the date already exisits it is skipped[/COLOR]

On Error Resume Next
    For D = [COLOR="blue"][FirstDate][/COLOR] To [COLOR="blue"][LastDate][/COLOR]
    If D = DateAdd("d", DateDiff("d", 1, D), [COLOR="Blue"]1[/COLOR]) Then
[COLOR="SeaGreen"]'The 1 here increments by 1 day[/COLOR]

        rst.AddNew
        rst("[COLOR="Blue"]Date1[/COLOR]") = D
        rst.Update

    End If
    Next D

[COLOR="SeaGreen"]'You can leave this bit out if you want[/COLOR]
Dim Msg, Style, Title, Response, MyString

    Msg = ("Dates between" & vbNewLine & vbNewLine & _
    [COLOR="blue"][FirstDate][/COLOR] & " and " & [COLOR="blue"][LastDate][/COLOR] & vbNewLine & vbNewLine & _
    "have been created or already exist.")
    Style = vbOKOnly + vbInformation
    Title = "Dates Created"
    Response = MsgBox(Msg, Style, Title)
 
Many thanks to you both for your quick replies. I will look further at both options to see which one suits me the best, but thank you both

Kind regards

Tim
 

Users who are viewing this thread

Back
Top Bottom