Need help with Access and Do/With Loop

Morning all,

I am trying to figure out how to use the Do/With loop to automatically enter records into a table. I have never used this function before and for some reason I just can't get my head around it. I have a table that holds daily appointments. I use a form to fill in the information (EmpName, event, start date, end date). After the information is entered we can open another form designed to look like a monthly calender which list each individuals appointments. Our issue is that we can only enter single day appointments. If an employee were to take, lets say 6 days off for vacation, we would have to enter 6 days worth of data. What we want is to only enter the start date and end dates and use a do/with loop to enter the days inbetween.

So 1) can this be done?

and 2) any suggestions on how?



  • Hi,

    Yes, it's possible. :)

    I assume you have a table for the calendar [tblCalendar], with the field EmpName and the date [dDate] of the appointment.

    Here's a suggestion for you:

    Sub Test_SetAppointments()
    SetAppointments "ndbadger", DateSerial(2009, 5, 5), DateSerial(2009, 5, 15)
    End Sub

    Sub SetAppointments(ByVal emp_name As String, ByVal start_date As Date, ByVal end_date As Date)
    Dim rs As Recordset
    Dim i As Integer, cnt_days As Integer

    cnt_days = Int(end_date) - Int(start_date) + 1

    Set rs = CurrentDb.OpenRecordset("tblCalendar")
    With rs
    For i = 1 To cnt_days
    !EmpName = emp_name
    !dDate = DateSerial(year(start_date), Month(start_date), day(start_date) + i - 1)
    Next i
    End With
    End Sub

    Hope it works for you.


    PS: I always use the ungainly DateSerial function as it prevents dates from getting scrambled by different regional settings on different computers.

Sign In or Register to comment.

Howdy, Stranger!

It looks like you're new here. If you want to get involved, click one of these buttons!


In this Discussion