Page 1 of 2 12 LastLast
Results 1 to 15 of 18
  1. #1
    MacB is offline Novice
    Windows 11 Access 2016
    Join Date
    Sep 2022
    Posts
    8

    Daily cleaning record; stuck at the beginning


    Hello, I have basic knowledge of Access and trying to create a "seemingly easy" daily cleaning record for the company I work for. Every day there's a list of daily tasks (cleaning checks; bins, sink, tables and miscellaneous) that need to be signed off by any of the staff working here (each person could do any of the tasks on a given day).

    I created two tables:

    1) tblCleaningRecords:
    CleaningRecordID(PK - Autonumber)
    Cleaning Date

    2) tblCleaningChecks:
    CleaningCheckID (PK - Autonumber)
    CleaningRecordID(FK - Number)
    CleaningCheck (Short Text)
    Signature (Short Text)

    see table relationship below:
    Click image for larger version. 

Name:	cleaning1.png 
Views:	32 
Size:	15.6 KB 
ID:	48688

    now, I know I'm missing something obvious, I'm only self-taught on Access and database design, and only for the past 2-3 months.

    The end result will be a daily list of the (same four) checks on each record, and the user will just select their initials from a combobox.

    I know loads about form design/basic vba code, but I don't know how to start. I created a form/subform linking them together but can't get the checks to autopopulate for every record. Any feedback much appreciated. Thanks

  2. #2
    Bob Fitz's Avatar
    Bob Fitz is offline Access Developer
    Windows 10 Access 2016
    Join Date
    May 2011
    Location
    Essex UK
    Posts
    3,530
    I think that you should start with your tables.

    How often is the cleaning done and is that frequency the same for all the items that are cleaned?
    If this helped, please click the star at the bottom left of this posting and add to my reputation . Many thanks.
    Bob Fitzpatrick

  3. #3
    orange's Avatar
    orange is offline Moderator
    Windows 10 Office 365
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    Not sure exactly of the requirements. I suggest you review the tables and relationships to ensure you can store and retrieve the info necessary to support your "business".

    Here is a draft that may help. I hope it's useful.

    Click image for larger version. 

Name:	DraftTasksByEmployee.png 
Views:	28 
Size:	22.5 KB 
ID:	48689

  4. #4
    MacB is offline Novice
    Windows 11 Access 2016
    Join Date
    Sep 2022
    Posts
    8
    Hi all, thanks for your replies. This is currently a sheet that needs to be filled in every day, with the date being the column and the tasks being the rows. @orange I'll see if I can work with that draft and will get back to you, thanks.

  5. #5
    MacB is offline Novice
    Windows 11 Access 2016
    Join Date
    Sep 2022
    Posts
    8
    @orange, I've just created the tables and the relationships in the draft model you showed me and it looks exactly like what I'm trying to achieve. Now I need to figure out how to make this functional, is it time to create a form to try to combine all these together? I have the Task (filled with the four recurring tasks) and Employee tables ready, TaskDate would be recorded on the form I guess, which is every day except weekends. So where do I go from here? Sorry, I'm quite novice and I know this might feel very obvious to most of you here....

  6. #6
    Bob Fitz's Avatar
    Bob Fitz is offline Access Developer
    Windows 10 Access 2016
    Join Date
    May 2011
    Location
    Essex UK
    Posts
    3,530
    MacB

    There's no football on today and as much as I loved our departed Queen there's a limit to how much coverage of current events I want to watch.
    My wife is busy working so I've had little play around. Perhaps the attached file will be of help. It needs some code to add items to the combos on the subform.
    Attached Files Attached Files
    If this helped, please click the star at the bottom left of this posting and add to my reputation . Many thanks.
    Bob Fitzpatrick

  7. #7
    orange's Avatar
    orange is offline Moderator
    Windows 10 Office 365
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    Quote Originally Posted by MacB View Post
    @orange, I've just created the tables and the relationships in the draft model you showed me and it looks exactly like what I'm trying to achieve. Now I need to figure out how to make this functional, is it time to create a form to try to combine all these together? I have the Task (filled with the four recurring tasks) and Employee tables ready, TaskDate would be recorded on the form I guess, which is every day except weekends. So where do I go from here? Sorry, I'm quite novice and I know this might feel very obvious to most of you here....
    I suggest you review this Stump the Model link. You can use pencil an paper; some index cards or whatever.
    Work through a little test data and convince yourself that you can or can not store and retrieve the required info. If you can NOT, then adjust and try again. Much easier to confirm/correct your tables now, than once you get forms etc in place. Good luck.

  8. #8
    Join Date
    May 2018
    Location
    Living in Scotland UK
    Posts
    1,557
    Hi Mac

    I used Bob's table design but created a different Form layout.

    See attached.
    Attached Files Attached Files
    You can PM me if you need further help.
    Good Reading https://docs.microsoft.com/en-gb/off...on-description

  9. #9
    orange's Avatar
    orange is offline Moderator
    Windows 10 Office 365
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    Mac,

    Mike's sample DB is similar to the model I suggested. He has added the form and some combos. Looks good.
    I would also consider the 2 fields from the model in post #3.
    CompletedYN -- to indicate if the task/item was completed, and
    Comment ------ to allow for any note/comment/etc re that item on that day but that Employee

  10. #10
    MacB is offline Novice
    Windows 11 Access 2016
    Join Date
    Sep 2022
    Posts
    8
    BoB, Mike and orange, thank you so much, i think it's exactly what I'm looking for and you've also given me a template for future projects. I'll work on it and post an update very soon!

  11. #11
    MacB is offline Novice
    Windows 11 Access 2016
    Join Date
    Sep 2022
    Posts
    8
    Hello all,

    regarding bob's suggestion/design and mike's update, I have a question, hope it doesn't sound too stupid:

    Would it be possible for the cleaning items (in tblItems) to automatically show up and be locked and not enabled for each record/Date?

    I wouldn't want the user to be selecting the items from the combobox, but instead have all the items there and the user just picks from the staffID the person that did the task.

    See screenshot, this is how the paper version of that record looks like at the moment, and a person updates it every day.

    Thanks again!

    Click image for larger version. 

Name:	daily cleaning record.PNG 
Views:	14 
Size:	16.1 KB 
ID:	48697

  12. #12
    Bob Fitz's Avatar
    Bob Fitz is offline Access Developer
    Windows 10 Access 2016
    Join Date
    May 2011
    Location
    Essex UK
    Posts
    3,530
    In your proposed db, what data would be in the "Signature"?
    If this helped, please click the star at the bottom left of this posting and add to my reputation . Many thanks.
    Bob Fitzpatrick

  13. #13
    MacB is offline Novice
    Windows 11 Access 2016
    Join Date
    Sep 2022
    Posts
    8
    Quote Originally Posted by Bob Fitz View Post
    In your proposed db, what data would be in the "Signature"?
    The StaffID would do fine, we only need to identify who did each task

  14. #14
    Join Date
    May 2018
    Location
    Living in Scotland UK
    Posts
    1,557
    Hi Mac

    See the attached modified Db.

    Just Click on the "Add Cleaning Items" Command Button
    Attached Files Attached Files
    You can PM me if you need further help.
    Good Reading https://docs.microsoft.com/en-gb/off...on-description

  15. #15
    MacB is offline Novice
    Windows 11 Access 2016
    Join Date
    Sep 2022
    Posts
    8
    Hey mike, exactly want I want, thanks! I seem to be getting an error after I add a new date. I click add new date, then Add cleaning items, and error 3201 pops up (in cmdAdd_click, line 50). but then if i close the form and open it again a few times, it opens with the items. Maybe i'm doing something wrong?

Page 1 of 2 12 LastLast
Please reply to this thread with any new information or opinions.

Similar Threads

  1. Need help cleaning up VBA
    By templeowls in forum Programming
    Replies: 2
    Last Post: 08-16-2022, 03:05 PM
  2. Replies: 1
    Last Post: 12-09-2016, 08:58 AM
  3. Replies: 3
    Last Post: 06-27-2012, 10:57 AM
  4. Replies: 0
    Last Post: 11-22-2011, 09:22 PM
  5. Cleaning Up Data - Need Help
    By NeedHelp in forum Access
    Replies: 2
    Last Post: 06-05-2010, 10:06 AM

Tags for this Thread

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