Create a repeating Diary Appointment 2

[Moss100]

Hello,

I am trying to achieve a repeating appointment for a small diary database I have.

At its most basic, I have an Appointments table: tbl_Appointment

It has:

A unique ID of Appoint_ID
An appointment date: Appoint_Date

On an unbound form I have the following:

textbox: txt_Start_Date
textbox: txt_End_Date
combobox: cbo_Repeat_Frequency (this contains daily, weekly, monthly,annually)
button: btn_Add

Thanks for any help.

Mark.

 

[Andrzejek]

How about:

Option Explicit

Private Sub Form_Load()

With cbo_Repeat_Frequency
    .AddItem "daily"
    .AddItem "weekly"
    .AddItem "monthly"
    .AddItem "annually"
End With

End Sub

Private Sub btn_Add_Click()
Dim strInterval As String
Dim datApp As Date

Select Case cbo_Repeat_Frequency
    Case "daily"
        strInterval = "d"
    Case "weekly"
        strInterval = "ww"
    Case "monthly"
        strInterval = "m"
    Case "annually"
        strInterval = "yyyy"
End Select

datApp = CDate(txt_Start_Date)

Do Until datApp > CDate(txt_End_Date)
    Debug.Print "INSERT INTO tbl_Appointment (Appoint_Date) VALUES(#" & datApp & "#)"
    datApp = DateAdd(strInterval, 1, datApp)
Loop

End Sub

---- Andy

There is a great need for a sarcasm font.

 

[Moss100]

Just what I required - A great help many thanks. Mark

 

[SkipVought]

@Andy
Thx!

Skip,

_{Just traded in my OLD subtlety...
for a NUance!}

 

[Andrzejek]

Of course you need to add some validation to this logic, like validate the dates in both text boxes if users can type them – unless you use some kind of calendar to choose dates from. And if ‘daily’ means Monday to Friday, you need to exclude weekends, etc.


---- Andy

There is a great need for a sarcasm font.

 

[dhookom]

I've posted a working "recurring" schedule application (Access 2000) on Rogers Access Library.

Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016