Drop-down Combo box problems

[celia2]

Hi,

I am doing a monthly timesheet in MS Excel for the use of 25 staff
where each of them will fill the individual form and send to the server at the end of each month. There are 4 sections: Admin (5), Accounts (2), Sales(6) and Others.

Now, I want to switch to MS Access. I try to using the pivot table I created in Excel but the format/fields are not compatible when I import into Access.

So, I need to create the form for user to enter data in Access. Now, I want to create 2 combo box. Where the first combo box select Admin then the second combo box will display the name of the Admin staff. This means, Admin staff can choose their names and then fill in the rest of the data. Of course, by then I will also need to provide login for each Staff. However, I want to solve the combo box things first.

Sorry I am a bit new to this MS Access.

Would vey much appreciate for reading through and please give some advice and help.

Thankyou.

celia

 

[smercer]

Now, I want to switch to MS Access. I try to using the pivot table I created in Excel but the format/fields are not compatible when I import into Access.

Do you want it linked or do you want it without the link so it does not update when you change it in excel?

 

[celia2]

Hi,

I guess you are answering my question regarding Excel. I have attached the pivot table I created which was actually link to the individual timesheet for your reference.

Answering to your question, I want it without the link so it does not update when I change it in excel.

I hope you can provide some advice.

Thankyou.

 

[smercer]

It'll have to wait until I get home from work.

 

[smercer]

Celia: While I can see you have worked very hard on the time sheet, It does not tell me what I need to know to solve your problem, for instance I could not find the 4 sections: Admin (5), Accounts (2), Sales(6) and Others.

I think it may be in a hidden column/rows, but i think it would be easier for you to tell me where to find it.

 

[celia2]

Smercer,

Yes, you could not find the 4 sections in my Excel timesheet because I only propose to add in when I create the Access form. Actually, the Excel timesheet is given to the staff and they fill it. After they submit, then I combined it in pivot table according to sections. So, all the admin staffs’ will be combined into one sheet as well as other sections. And then, from the pivot table, the data are transfer into another sheet where you can see in q1.xls enclosed. From there, the total time is calculated and another sheet for cost is created. The main data we need is the time (normal working hours, OT) spent in each projects and the cost spent on projects. Based on that, many sheets are created.

So, I was thinking to switch to using MS Access. At first, I want to keep the timesheet in Excel for everyone to fill in and use the pivot table created in Excel for building the table in Access. But, after import into Access, the table doesn’t seem right. So, now maybe I just need to create the form to be entered by the users of course at last login are needed for their own sheet. In the form, Admin staff select Admin from 1st dropdown combo box, in second combo box they can select their name where only admin’s name are shown. Then, they fiil in the project no, working hours, on leave ……

Hope you understand what I am telling. Please give some advice whether it is advisable to use Excel together with Access if the pivot table can be used. Or the way I create the Access form is correct or not. Thanykyou.

Hav a nice weekend,
celia

 

[smercer]

This should get you started

 

[celia2]

Thanks for the idea.

Not much like what I want because my timesheet is used to record the works done by each person on each working days. Anyway, will look and understand. Thankyou very much.

 

[smercer]

Thanks for the idea.

Not much like what I want because my timesheet is used to record the works done by each person on each working days.

I was wondering, would it be better for you to record the times they log on to computer for the times they start, and record the times they logoff for when they finish.

This could be accomplished by having the database autostart when the workstation starts, however when the workstation is shut down is a different story. You may have to ask around about that.

 

[celia2]

No, I dont think I need that because it up to them when they want to fill the form. They need to done it by the end of the month, that's it.

Therefore, I need to create login for them and command buttons to enable them to save or delete data. DO you think I have made things too complicated?

Thanks,
celia

 

[smercer]

Therefore, I need to create login for them and command buttons to enable them to save or delete data. DO you think I have made things too complicated?

Thanks,
celia

It all depends on what you mean by "save". when users enter data into or edit a record they are saved immediately when they go to next record, so a save button is not needed. however if you have the record restricted so that the user is only being able to edit his/her record, then a save button would be a good idea as the user can not go to next record.

If you are going to ask how to restrict users as mentioned above, does each user have a logon username as his/her own name, or do you have one group of people for one username? if this is the case a password on your database is a good idea and use that to restrict users like that.

to restrict users for the database password have "CurrentUser()" as criteria in a query and base form on the query.

to restrict users for with the windows logon have

Environ ("UserName")

as criteria in a query and base form on the query. (only works with Windows NT, 2000 and XP)