Results 1 to 12 of 12

Thread: Design Advice?

  1. #1
    3 Star Lounger
    Join Date
    Nov 2002
    Location
    Belfast, Ireland
    Posts
    339
    Thanks
    2
    Thanked 0 Times in 0 Posts
    Hi

    I'm creating a Project Database that staff will complete showing the work they've done in a particular week. I've been asked whether it's possible for Access to open a Form that's almost like a Calendar, ie, Column Headings: Monday, Tuesday, Wednesday, etc, and then the Projects will be row headings, and the staff will complete the work they did for each project, eg.


    I'm not sure whether this is even possible in Access, so I'm asking the Experts!

    If not, do you think I'm best to create Fields called Monday-Friday and then just make the Field called Project a Combo Box, or does Access have an in-built clever way of achieving this?

    Many thanks for any advice.

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    Access doesn't have built-in support for grid-or calendar-like data entry.

    One solution is to create fields for Monday, Tuesday, ...
    This makes it easier to create a form, but harder to work with the data.

    Another solution is to create a record for each day of the week:

    Code:
    StaffID Project Week Day Hours
    	 37	  12	3   1	..
    	 37	  12	3   2	..
    	 ..	  ..   ..  ..	..
    	 37	  12	3   7	..
    You'd have to use an unbound form to enter the data, with code to save it in the table.
    This format makes it easier to work with the data, e.g. totalling by StaffID or by Project etc.

  3. #3
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts
    I have done a couple of databases that needed to store work records on Projects.

    I used the same solution each time. The data was stored with one record for each person, project and date on which the person did some work on that project:

    e.g. : 121 , 57, 02/08/2009, .75 would mean that person 57 did .75 days work on project 121 on 2/8/09. (I could easily have used hours as the unit instead of days- it would not have made much difference.)

    But the users wanted to be able to view/edit data via a Calendar form like this:

    [attachment=85715:calendar.gif]

    To accomplish this, I used a temporary table with a structure that matched this form, and a pretty complicated procedure to write work records into the temp table for viewing in the Calendar, then write them back again later. It has worked successfully for several years, but it did involve a lot of work.
    Attached Images Attached Images
    Regards
    John



  4. #4
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,624
    Thanks
    3
    Thanked 60 Times in 60 Posts
    In addition to the comments you received from Hans and John, another option is to use an OCX control from a third party. We have used one from DBI Technologies with good success. However using such controls does normally require a fair bit of programming as they don't typically bind to data like a regular Access form.
    Wendell

  5. #5
    3 Star Lounger
    Join Date
    Nov 2002
    Location
    Belfast, Ireland
    Posts
    339
    Thanks
    2
    Thanked 0 Times in 0 Posts
    Thanks to everyone for your replies. This is a small company, so I can't afford to spend a lot of time on Coding (I'm not an expert coder anyway).

    I think I'll have to a think about it.

    Many thanks again for your replies.

    I'll post back soon!

  6. #6
    New Lounger
    Join Date
    Sep 2009
    Posts
    17
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Post

    Hi again

    I've created a Form that has each User's Name & Week Commencing. A user selects (or creates) a Week Commencing Record and clicks Go.

    A Form then opens up that allows them to put in each Job and how many hours they did each day that week for the job.



    I'm having a wee problem though. As you can see in the image above, there's a Total Hours (This Week) unbound Text Box in the top, right-hand corner. I'd like this to update as soon as any hours are entered into any box, so I've tried using the Refresh method on the Form:
    Code:
    Private Sub Form_AfterUpdate()
    Me.Refresh
    End Sub
    But nothing happens.

    I tried adding AfterUpdate VBA to the days-of-the-week, but again, nothing happens or Access says the Me.Refresh code is invalid.

    Where should I put the Code to get the Total to update every time a new value is entered into any field?

    Many thanks for your ongoing assistance.

  7. #7
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    Is the control source of the Total Hours text box blank, or is it a formula =...+...+... ?

  8. #8
    New Lounger
    Join Date
    Sep 2009
    Posts
    17
    Thanks
    0
    Thanked 0 Times in 0 Posts
    [quote name='HansV' post='796066' date='02-Oct-2009 17:28']Is the control source of the Total Hours text box blank, or is it a formula =...+...+... ?[/quote]

    Code:
    =[txtSumMonday]+[txtSumTuesday]+[txtSumWednesday]+[txtSumThursday]+[txtSumFriday]
    If I close and reopen the Form, it seems to work, but if it's the first time someone's entering data for this work week, then nothing happens unless I click F5???

    Thanks for your reply

  9. #9
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    You need to put the following line in the After Update event of the individual text boxes for Monday, Tuesday etc.:

    Me.Recalc

  10. #10
    New Lounger
    Join Date
    Sep 2009
    Posts
    17
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Excellent. Thanks Hans.

    I don't suppose there's a way to get it or the Total Text Boxes to update as I move from Field to Field? At the moment, they only update when i move to a new or different record?

    Thanks again.

  11. #11
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    Did you put the line Me.Recalc in the After Update event of the text boxes in which the user enters the number of hours?

  12. #12
    New Lounger
    Join Date
    Sep 2009
    Posts
    17
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Yes, thank you Hans. That worked. I have another question, but I think it requires a different Topic, so I'll start it in such.

    Many 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
  •