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?

Thanks

Comments

  • 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
    .AddNew
    !EmpName = emp_name
    !dDate = DateSerial(year(start_date), Month(start_date), day(start_date) + i - 1)
    .Update
    Next i
    .Close
    End With
    End Sub


    Hope it works for you.


    Andy

    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!

Categories

In this Discussion