How to lock specific records based on selection of Month?

[lulu123]

I have a database, which keep track of the hours employee worked.
The employee enter the hours monthly. The table is set to like,

Project/ Month/ Hour/

what i want to do is: I want to lock the record based on the month. if i make a form with a month combo box. ..I want all of the record to be locked except the record that has the month matching the selected month. so, when employee enter their hours, they can only edit or add record to the month i specified.

how can i do that? plese give me some advise. Thanks.

[n8kindt]

you are the one specifying the month right? not the employees? if so, how are u able to specify the month while keeping them from doing likewise? i'm confused about what the purpose of the combo box is. are you wanting them to be able to view the past months and edit only the current month?

[missinglinq]

I agree with n8kindt; you need to give us a more detailed explanation of what you're trying to do in order for us to help you.

Welcome to Bytes!

Linq ;0>

[lulu123]

Thanks for reply, guys.

The combo box is for the admin, and it will be on a different form. So, the admin could select the mont she want to lock/not lock from the combo box.

any suggestions?

[lulu123]

n8kindt , thanks.

yea, the idea is to let the employee view the past month only , and able to edit current month. However, the purpose of the combo box is for an admin, so, she/he could choose which month to lock/not lock.

thanks.


Re: How to lock specific records based on selection of Month?
--------------------------------------------------------------------------------

you are the one specifying the month right? not the employees? if so, how are u able to specify the month while keeping them from doing likewise? i'm confused about what the purpose of the combo box is. are you wanting them to be able to view the past months and edit only the current month?

[n8kindt]

Thanks for reply, guys.

The combo box is for the admin, and it will be on a different form. So, the admin could select the mont she want to lock/not lock from the combo box.

any suggestions?

there are many ways to go about doing this. the easiest way i can think of would be to create a continuous form based on a query such as this:

SELECT tblTimeLog.*
FROM tblTimeLog
WHERE ((([tblTimeLog].[Year]*12+[tblTimeLog].[Month])=
[forms]![form1].[AdminComboBoxYear]*12+[forms]![form1].[AdminComboBoxMonth]));
 

and make sure the to have the combo box in the admin form (admincomboboxmonth) be bound to a number corresponding with the month 1 thru 12. for the year, make sure it is bound to a number in the 4 digit year format (eg: 2006). you're going to have to add a year field to your table but that won't be hard.

then make a subform out of the time log table, and place it in the main form's header or footer but lock the data so it can't be edited.

you will probably also want to include a docmd.requery command somewhere so the data the user just added/changed in the main form will show up in the subform.

[n8kindt]

there are many ways to go about doing this. the easiest way i can think of would be to create a continuous form based on a query such as this:
(all one line)

SELECT tblTimeLog.*
FROM tblTimeLog
WHERE ((([tblTimeLog].[Year]*12+[tblTimeLog].[Month])=
[forms]![form1].[AdminComboBoxYear]*12+[forms]![form1].[AdminComboBoxMonth]));
 

and make sure the to have the combo box in the admin form (admincomboboxmonth) be bound to a number corresponding with the month 1 thru 12. for the year, make sure it is bound to a number in the 4 digit year format (eg: 2006). you're going to have to add a year field to your table but that won't be hard.

then make a subform out of the time log table, and place it in the main form's header or footer but lock the data so it can't be edited.

you will probably also want to include a docmd.requery command somewhere so the data the user just added/changed in the main form will show up in the subform.

sorry, the where statement for the month on the admin form AND the previous month should be like this:

(all one line)

WHERE (((Year([tblTimeLog].[Date])*12+DatePart("m",[tblTimeLog].[Date]))=
[forms]![form1].[AdminComboBoxYear]*12+[forms]![form1].[AdminComboBoxMonth] Or (Year([tblTimeLog].[Date])*12+DatePart("m",[tblTimeLog].[Date]))=
([forms]![form1].[AdminComboBoxYear]*12+[forms]![form1].[AdminComboBoxMonth])-1));
 
 
 

[lulu123]

thanks for you help , n8kindt
but , i dont' really quite understand....
in this way, does this filter out everything that was not selected...so , how could user view the month that wasn't selected by the admin

please hlep ..thanks...

sorry, the where statement for the month on the admin form AND the previous month should be like this:

(all one line)

WHERE (((Year([tblTimeLog].[Date])*12+DatePart("m",[tblTimeLog].[Date]))=
[forms]![form1].[AdminComboBoxYear]*12+[forms]![form1].[AdminComboBoxMonth] Or (Year([tblTimeLog].[Date])*12+DatePart("m",[tblTimeLog].[Date]))=
([forms]![form1].[AdminComboBoxYear]*12+[forms]![form1].[AdminComboBoxMonth])-1));
 
 
 

[n8kindt]

sorry, i am going to amend my post here as i think it got confusing (i even confused myself when i read it lol). originally i had set up your form to have all months viewable, but when i saw you only wanted 2 months visible, i backtracked and made a goof. here is the entire post the way it should have read.


there are many ways to go about doing this. the easiest way i can think of would be to create a regular (you won't need a continuous form--just a regular one. my aplogies) form based on a query such as this:

SELECT tblTimeLog.*
FROM tblTimeLog
WHERE ((([tblTimeLog].[Year]*12+[tblTimeLog].[Month])=
[forms]![form1].[AdminComboBoxYear]*12+[forms]![form1].[AdminComboBoxMonth]));
 

this chooses only the month that is selected in your admin form combo box(es) so this is the part that the user will be editing.
and make sure the to have the combo box in the admin form (admincomboboxmonth) be bound to a number corresponding with the month 1 thru 12. for the year, make sure it is bound to a number in the 4 digit year format (eg: 2006). you're going to have to add a year field to your table but that won't be hard.

then make ANOTHER query such as this:

SELECT tblTimeLog.*
FROM tblTimeLog
WHERE (((Year([tblTimeLog].[Date])*12+DatePart("m",[tblTimeLog].[Date]))=
[forms]![form1].[AdminComboBoxYear]*12+[forms]![form1].[AdminComboBoxMonth] Or (Year([tblTimeLog].[Date])*12+DatePart("m",[tblTimeLog].[Date]))=
([forms]![form1].[AdminComboBoxYear]*12+[forms]![form1].[AdminComboBoxMonth])-1));
 

this will display the month selected and the previous month. place this subform in the main form's header or footer but lock the data so it can't be edited.

you will probably also want to include a docmd.requery command somewhere so the data the user just added/changed in the main form will show up in the subform. alternatively, you could configure the where statement for the subform query so that it only shows the previous month. if u choose this option and need help doing this let me know.

ps - paste that code into a query by selecting SQL view. then replace the "tblTimeLog", "form1", "AdminComboBoxYear", and "AdminComboBoxMonth" parts to whatever the name your table, form, and combo box names are. not sure if u knew that or not