Results 1 to 7 of 7
  1. #1
    New Lounger
    Join Date
    Mar 2006
    Posts
    10
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Create Multiple Records from Command Button (Access 2003)

    I have a database that is being built for a client. They are right now building a yearly schedule in Excel and its a nightmare! I am stuck right now in what I am building for them in Access. I have the forms built to populate the schedule, but what the customer wants is to enter the first date for work in the schedule, Jan 1 for example, and then click a button and that would then populate the schedule for that person for the whole year! I am totally missing something in the loop...pun intended. the field name for my date field is dtmDateWork. There are days that work is not scheduled...Mondays and Fridays. I am not well versed in writing code and I have already tried so many different macros my head is spinning. I looked up info on using loops in vba, but since I lack strong skills there, I'm lost! Any advice is very much appreciated!!

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Create Multiple Records from Command Button (Access 2003)

    Should the schedule always start on January 1, or on the exact date supplied by the user?
    Are there other days besides Mondays and Fridays that should not be included?

  3. #3
    2 Star Lounger
    Join Date
    Feb 2003
    Location
    Warwick, Warwickshire, England
    Posts
    189
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Create Multiple Records from Command Button (Access 2003)

    Create a table called datesavailable with two fields - dateavail (in date format) and avail (as Y/N format)
    In this keep a full list of everyday in the year (or as many years into the future as you require) in the dateavail fields and tick the dates that you wish to be available for a schedule (ie don't tick the weekends / holidays etc).
    When you wish to create the schedule use an append query to add this table to the main schedule table with any required if statement to define the start and end date and to exclude the unticked dates.

    It's maybe not the most elegant way to do it, but a similar method has worked well for me in some instances

    John

  4. #4
    New Lounger
    Join Date
    Mar 2006
    Posts
    10
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Create Multiple Records from Command Button (Access 2003)

    other than holidays, which i have a list of, just mondays and fridays, as those are the days the plant gets cleaned and the people who they are scheduling are not the sanitation people...they work for an outside sanitation company who sets their schedule, so i don't have to worry about them! and, for this year, since it already started, the first date that would appear in the schedule will be whatever date they can use once i get this to them. each year after this one, the schedule will start on january 1. i hope that all makes sense! thanks for the quick response!!!

  5. #5
    New Lounger
    Join Date
    Mar 2006
    Posts
    10
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Create Multiple Records from Command Button (Access 2003)

    thanks a lot john. i will try that!

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Create Multiple Records from Command Button (Access 2003)

    Say you have a form with a text box txtStartDate and a command button cmdOK, a table tblHolidays with a date field dtmHoliday and a table dtmSchedule with a date field dtmDateWork. The following code in the On Click event of the command button will add dates to ttblSchedule, from the date entered in txtStartDate to the end of the year.

    Private Sub cmdOK_Click()
    Dim dbs As DAO.Database
    Dim rstSchedule As DAO.Recordset
    Dim rstHolidays As DAO.Recordset
    Dim d As Date
    Dim dtmStart As Date
    Dim dtmEnd As Date

    Set dbs = CurrentDb
    Set rstSchedule = dbs.OpenRecordset("tblSchedule", dbOpenDynaset)
    Set rstHolidays = dbs.OpenRecordset("tblHolidays", dbOpenDynaset)

    dtmStart = Me.txtStartDate
    dtmEnd = DateSerial(Year(dtmStart), 12, 31)
    For d = dtmStart To dtmEnd
    If Not (Weekday(d) = 2 Or Weekday(d) = 6) Then
    rstHolidays.FindFirst "dtmHoliday =#" & Format(d, "mm/dd/yyyy") & "#"
    If rstHolidays.NoMatch Then
    rstSchedule.AddNew
    rstSchedule!dtmDateWork = d
    rstSchedule.Update
    End If
    End If
    Next d

    rstHolidays.Close
    Set rstHolidays = Nothing
    rstSchedule.Close
    Set rstSchedule = Nothing
    Set dbs = Nothing
    End Sub

    The attached database contains a sample form; the code there also includes error checking.

  7. #7
    Silver Lounger
    Join Date
    Jun 2001
    Location
    Niagara Falls, New York, USA
    Posts
    1,878
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Create Multiple Records from Command Button (Access 2003)

    Another practical DAO example

    Thanks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •