Need help with Access and Do/With Loop - Programmers Heaven

Howdy, Stranger!

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


Need help with Access and Do/With Loop

ndbadgerndbadger Posts: 1Member
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?



  • abitw2abitw2 Posts: 4Member

    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.