Results 1 to 2 of 2
  1. #1
    davidcousins is offline Novice
    Windows XP Access 2007
    Join Date
    Aug 2011
    Posts
    1

    calendar issues


    I am trying to create a works calendar in MS access 2007 and want to be able to enter a start date and end date that will sometimes span 5 or 6 days. How can you get access to replicate this entry on every day without having to make an entry for each day?

  2. #2
    rpeare is offline VIP
    Windows XP Access 2003
    Join Date
    Jul 2011
    Posts
    5,442
    we'd have to know the structure of your database but I'm assuming you have something like:

    Tbl_PeopleTime
    PTID (autonumber)
    PersonID (number foreign key)
    PersonDate (this is the field you want to increment with your data entry)

    I'm assuming you then want to put in a start date, and end date and have this table filled out?

    if so the only way I can think to do this would be through code

    Let's say your data entry form is called Frm_DataEntry, on that form you have two unbound (are not linked to any field) text boxes called StartDate and EndDate and of course your PersonID is a field as well.

    You would have to (say on a button click) run some code that would look at the start and end date and if the end date was > 1 day difference from the start date insert a record into your table for each day until it reaches the end date

    for instance

    dim db as database
    dim ssql as string
    dim dStartDate
    dim dEndDate
    dim lPersonID as long

    set db = currentdb

    lpersonID = me.PersonID
    dstartdate = me.startdate
    denddate = me.enddate

    do while dstartdate <= denddate
    ssql = "INSERT INTO tbl_PeopleTime (PersonID, PersonDate) VALUES ("
    ssql = ssql & lpersonID & ", "
    ssql = ssql & "#" & dstartdate & "#"
    ssql = ssql & ")"
    db.execute ssql
    dstartdate = dateadd("d", 1, dstartdate)
    loop

    db.close

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Calendar example
    By pkstormy in forum Code Repository
    Replies: 1
    Last Post: 02-28-2011, 04:09 AM
  2. Help with making a calendar
    By slmorgan25 in forum Access
    Replies: 1
    Last Post: 09-15-2010, 10:32 AM
  3. Event Calendar Help
    By Nosaj08 in forum Forms
    Replies: 9
    Last Post: 06-11-2010, 11:19 AM
  4. Help with Calendar
    By alanl in forum Access
    Replies: 1
    Last Post: 03-15-2010, 08:16 AM
  5. Calendar Mystery
    By BankWalker in forum Forms
    Replies: 0
    Last Post: 02-16-2006, 01:29 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums