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!

Categories

Welcome to the new platform of Programmer's Heaven! We apologize for the inconvenience caused, if you visited us from a broken link of the previous version. The main reason to move to a new platform is to provide more effective and collaborative experience to you all. Please feel free to experience the new platform and use its exciting features. Contact us for any issue that you need to get clarified. We are more than happy to help you.

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?

Thanks

Comments

  • abitw2abitw2 Posts: 4Member
    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.