Creating a form (2003)

Topic

New Reply

I want to create a form that will allow me to enter a work schedule in my table. see attached

I have a table of employees, I have a table of potential schedules (7a-7p), I have a table with emp work schedule.

I have an autonumber field as primary in the work schedule, employee number, schedule number, and date worked.

Can I design what I want from this setup?

Thanks,

Reply | Quote

Replies

Wouldn’t Excel be an easier application for this?

Reply | Quote

I currently have it in several excel spreadsheets. I need to track more information such as who called in sick and who is filling in for them as well as track my OT cost. Our current payroll system is just for “collecting” data, I need more to project future salary budgets, need for more staffing, etc.

Reply | Quote

I’m still not convinced that Access is the ideal application for this. One of the reasons for this is that Access doesn’t make it easy to enter data in a format such as that in your spreadsheet, since the way the data are (or should be) stored is quite different from the way you want to see it on screen.

If you want to go ahead with it, I would use an unbound form with a grid of combo boxes. Labels above the grid show dates, and labels to the left of the grid display employee names (or IDs).
The captions of the labels and the values of the combo boxes must be set in the On Load (or On Open) event of the form. When the user has finished editing, (s)he clicks a command button that writes the data to the appropriate table(s).
You could use a separate form to be displayed before the data entry form in which the user can select the date range and group of employees to be displayed.

All this will require a lot of VBA code to tie it all together.

Reply | Quote

Is there a way to take it from Excel and import into my tables?

I follow what you are saying below about using VBA code to make it happen. I did not think it would be an easy task in access. I have done something similar to this before but it was on a report not a data entry screen. I am struggling to get my form to pull up just my employees and the specific date range. I am somewhat familiar with VBA but I guess not savy enough. Is there a forum for VBA questions?

Thanks!

Reply | Quote

You can ask Access VBA questions here, in the Access forum.

Reply | Quote

Why don’t you create a form where you select an employee, the form then shows records by date range and you can enter the details record by record. The database table is normalised and there would be little coding required for the form. if you wanted to show the schedule like you want in the spreadsheet you could make a crosstab query to drive the report.

Reply | Quote

The goal is to select a list of employees by their unit and then enter a work schedule for the entire month. The user does not want to view just one person at a time.

The idea was great and in any other situation I would use it. Any other suggestions!

Reply | Quote

What you could do is to introduce a table that holds an EmployeeID and 31 fields (1 for each day of the month, as you want to process a month at a time) to hold the work schedule.

What the form would do is to populate this table from the Work Schedule table you already have, then make your corrections on the form, and finally when exitting the form write these records back to your Work Schedule table.

As Hans has pointed out you could do it in Excel and import the sheet into Access into a table much like i described above and finally to populate the Work Schedule table from this.

Reply | Quote

I like the idea and I think it would work for me. I do have a few questions.

The user wants to be able to enter information by Month and by unit. I need to create a form that would select the unit of people, select the month and then populate the dates for that month. What would be the code to write the records back to my work schedule table? How do I get it to write to the next record? I programmed for years in cobol a long time ago and the logical steps would just appear and I could code. I guess the older you get and the longer you have been away from the process – you forget stuff. Here is my logical flow for this to happen and let me know if I am wrong in my thinking.

Select the month (dates populate on form)
Select the unit (employees populate on form)
Select time schedule under each date heading for each employee (the different shifts are in a table and I would use a combo box)
After I have entered all schedules for the unit (each person)- click a button to write.

In VBA code – once click to write
read a record, write Employee ID, Shift, first date to work schedule table.
Continue to record record write same employee id, shift and second date.
continue to I get to next employee and do the same.
When all employees for that unit are complete, select the next unit and do the same.

Is my logic correct?

Thanks for all your help.

Reply | Quote

That sounds ok. You may also want to allow editing existing shcedule records. This would entail reading the records, and populating the form controls with their values. Clicking Save would update the existing records instead of creating new ones.

Reply | Quote