Question Duties Not Done Status Board- Set-up help

[Losartan]

Hello,

I am a self-taught Access programmer and have done quite a few database builds. I'm struggling right now on how to set up my latest challenge.

I want to create a database to keep track of duties that need to be done every day.

Vision:
A list of duties shows up on a form each day. An employee who completes the duty will pick their name from a drop down box and press an action button to complete it. This documents the existing date that it was completed and takes the duty off the list to do. At the end of the day, the form should be blank.

The next day, the form should reset and show all of the duties that haven't been done that day and so on.

Is this possible?

I'm having a hard time setting it up.

Any thoughts would be greatly appreciated!!!

Thanks in advance,
Jason

 

[pbaldy]

I can think of a couple of ways, depending on whether you want to keep the history or not. Assuming you do, I'd have a table containing the daily tasks, and another table for the activity. Each day you would append records from the tasks table to the activity table populating a date field with the current day. Your form would display records for the current day that haven't been completed. As each task is marked completed, a requery of the form would drop it from the list.

 

[MSAccessRookie]

Hello,

I am a self-taught Access programmer and have done quite a few database builds. I'm struggling right now on how to set up my latest challenge.

I want to create a database to keep track of duties that need to be done every day.

Vision:
A list of duties shows up on a form each day. An employee who completes the duty will pick their name from a drop down box and press an action button to complete it. This documents the existing date that it was completed and takes the duty off the list to do. At the end of the day, the form should be blank.

The next day, the form should reset and show all of the duties that haven't been done that day and so on.

Is this possible?

I'm having a hard time setting it up.

Any thoughts would be greatly appreciated!!!

Thanks in advance,
Jason

As Long as each task has a Completed (Y/N) field in its record, then you could base the TaskSelect Combo Box List on the list of Tasks to do today, where the TaskCompleted Field is False. When any employee enters their name as completing the task, the TaskCompleted field could be set to True. That way, it will no longer appear in the TaskSelect Combo Box List. I believe that this would be a good start

 

[Losartan]

I can think of a couple of ways, depending on whether you want to keep the history or not. Assuming you do, I'd have a table containing the daily tasks, and another table for the activity. Each day you would append records from the tasks table to the activity table populating a date field with the current day. Your form would display records for the current day that haven't been completed. As each task is marked completed, a requery of the form would drop it from the list.

Ok. If I understand this correctly:
List all Daily tasks in one table:
TaskID
Task

What would go in the activity table? Date Completed and Employee?

Would I have to go into the db daily to update the tables so the form will show the current day's duties or can that be automated?

Thanks for the help.
Jason

 

[pbaldy]

Those 2 fields plus TaskID. It wouldn't be hard to automate. I would create a "utility" db that did nothing but run an append query then close itself down. Then run that db from Scheduled Tasks on the appropriate schedule.

 

[Losartan]

Ok.

I think I have most of it figured out. A few more questions:
Each task has whether a technician or pharmacist is required for the task. If the pharmacist is checked yes, I want a command button for a pharmacist check to show for that task. If a pharmacist is not needed, the command button for a pharmacist check should not display.

Also, when the pharmacist check button is pressed, I want a label to appear for that task that states, "Pharmacist Check Requested!"

I have the code in place but it is not working right.

I have a continuous form and everytime a pharmacist is required for one duty, it shows for all of the duties. Also, when the command button is checked, the text box for a pharmacist shows for all duties, not just the specific duties.

How do I get it to only show the buttons/text box for the duties that needs that and not for all of them?

Thanks,
Jason

 

[pbaldy]

That is the nature of unbound controls. You can play with conditional formatting though, as that will affect individual records. You can't set the visible property, but you can enable/disable the button. You can make the textbox invisible by setting the font color to match the back color.

 

[Losartan]

pbaldy,

I sent you a pm.

Here is my db.

Thanks,
Jason

 

[pbaldy]

One way of creating records for all dates is this type of thing:

  Dim dteDate            As Date
  
  dteDate = #2/1/2009#
  
  Do Until dteDate > #2/28/2009#
    CurrentDb.Execute "INSERT INTO CompletedDutyTbl ( DutyID, [Date]) " _
                    & "SELECT Dutytbl.DutyID, #" & dteDate & "# " _
                    & "FROM Dutytbl"

    dteDate = dteDate + 1
  Loop

which you'd probably want to modify to accept dates from the user. It could also be done without code if you had a table with all the desired dates in it. Also, "date" should not be used as a field name, as it's a reserved word:

http://allenbrowne.com/AppIssueBadWord.html

And I would recommend against the table lookup field:

http://www.mvps.org/access/lookupfields.htm

 

[Losartan]

I know better than to use the word "Date" as a field. I had a brain fart. I didn't know about the table lookup field. Would I just write code to pick the employee?

The code looks like it would do what I want. I don't want the user to input dates.

I'll give it a shot.

Seriously, I really appreciate your help!
Jason

 

[Losartan]

Where would I insert that code?

 

[pbaldy]

Sorry, missed your last question. Given what you're doing, perhaps behind a special button that you click when you want to add records. If you make it dynamic (doesn't need user input or code changes to do correct dates), you could make it a public function in a standard module in a stand-alone database. Call that function from an autoexec macro and call this stand-alone database from Scheduled Tasks so it will basically automatically add the appropriate dates on your chosen schedule.

 

[Losartan]

pbaldy,

Thanks for your response. Now I feel like I am getting outside of my knowledge of Access. I would like for it to automatically add a date for all duties without user input.

You are saying that I make a module in a separate database with the code you posted. Then, I use a macro from my duties db to run the code in the other database?

I'm having a hard time grasping that.

 

[pbaldy]

No, where I was headed was making a database that did nothing but add the dates. It would have an autoexec macro that ran your function and then closed itself.

Alternatively, you could leave the function in your main database, create a macro that runs the function and closes the database. I believe you can specify that macro to run when you call it from Scheduled Tasks.

 

[Losartan]

I'm sorry pbaldy.

I am completely lost! That must mean it's Monday! I hate to even ask but is there any way you can help me with this?

Jason

 

[Losartan]

pbaldy,

I figured it out. Thanks again for all of your help!

Jason

 

[pbaldy]

Sorry Jason, I lost track of this thread and didn't see your question yesterday. Glad to hear you got it sorted out.