Seperate Data Entry forms for Each User

[VKKT]

Hi All
Greetings
I am back after a long time.. Hope all are doing fine

I have the attached database for Staff time sheet

Previously I created separate forms for each staff to enter their details as in the attachment, as that time there were only 5 staff, but there are 35 staffs now under 1 Project Manager. The data entered by one person should not be available to the other.

Is there any other way to do this without creating separate forms for each person.
Regards,
VKKT

[HansV]

How secure do you need this to be? Even with the current setup, users can simply open the TIME SHEET DATA table and view everyone's data...

[VKKT]

Hi Mr. Hans, Thanks for the immediate response.

Yes I know it is not fully secure, but I don't know how to do that. I need it to be completely secure.
I know to hide the "display navigation pane" is there any other way to do that, means restrict anybody from opening the forms/tables etc?
If you can help on this please, if it is not much complicated.

Regards
VKKT

[HansV]

Trying to create a secure system in which each user can only view and edit specific data is very complicated. Microsoft Access doesn't really have the tools for that. For a really secure system, the data would have to be stored in a SQL Server database, but setting up and maintaining SQL Server is not easy either.
You might be better off buying a ready-made staff management system.

[Wendell]

Hi Mr. Hans, Thanks for the immediate response.

Yes I know it is not fully secure, but I don't know how to do that. I need it to be completely secure.
I know to hide the "display navigation pane" is there any other way to do that, means restrict anybody from opening the forms/tables etc?
If you can help on this please, if it is not much complicated.

Regards
VKKT

I think Hans' suggestion of using SQL Server with user login is the right choice if you are using one of the recent versions of Access. If you are using an older version with an MDB file extension, the old Access User Security could be an alternative - but from 2007 on you can't do any of the administrative functions. Just looking for alternatives...

[VKKT]

Thanks a lot Mr. Hans & Mr. Wendell for your suggestions

Now I have splitted the files into back/front end to reduce the traffic as there are many users. When I try to secure the back end file with a password the front-end file is not opening ((Runtime error '3031' not a valid password) with the below script which I have used in the attachment login form. Is there any solution to this.

regards,
Vipin

_________________________________________
Private Sub Command4_Click()
'Dim UserLeve As Integer
Dim dbPassword As String
If IsNull(Me.UserID) Then
MsgBox "Please Enter LoginID", vbInformation, "LoginID Required"
Me.UserID.SetFocus
ElseIf IsNull(Me.PWord) Then
MsgBox "Please Enter Password", vbInformation, "Password Required"
Me.PWord.SetFocus
Else
'Process the Job
If (IsNull(DLookup("UserLogIn", "tblUser", "UserLogIn ='" & Me.UserID.Value & "'"))) Then

MsgBox "Incorrect username"

ElseIf (Me.PWord.Value <> DLookup("Password", "tblUser", "UserLogIn ='" & Me.UserID.Value & "'")) Then
MsgBox "Incorrect password entered"

Else
UserLevel = DLookup("UserSecurity", "tblUser", "UserLogIn ='" & Me.UserID.Value & "'")
DoCmd.Close
If UserLevel = "Admin" Then
DoCmd.OpenForm "ANavigation FORM-TRIAL"
ElseIf UserLevel = "User" Then
DoCmd.OpenForm "TIME SHEET DATA-AMA"

End If

End Sub

[HansV]

After password-protecting the backend, you should delete all linked tables from the frontend, then link to the tables in the password-protected backend. You'll be asked to provide the password.

[VKKT]

Thanks Mr. Hans for your immediate response with the solution to my query.
Regards,
VKKT

[VKKT]

Hi All,
Greetings,

In the attached sample database how can I create a query to show who has missed out to fill his time for any of the dates during the month.
regards,
VKKT

[HansV]

Do you want this for the current month, or for a month to be specified, or for all months?

[VKKT]

Hi Mr. Hans,
Thanks for the immediate response

No. Sir, I need it for all the months, now and coming..

regards,
VKKT

[HansV]

See the query qryFinal in the attached database

Staff TS.zip

[VKKT]

Thanks Mr. Hans for the Response,

What actually I required is to show all the "Dates" (not only month) missing in the months, for eg.

staff Date
Asharf 1/11/19
Ashraf 2/11/19
Ashraf 5/11/19
Ashraf 8/11/19
Ashraf 9/11/19

I want to show in the query the missing dates between 1/11/19 to 9/11/19 that is 3 & 4/11/19 and 6 & 8/11/19

Regards
VKKT

[HansV]

To do that, you need a table that lists all dates. I created tblDates for this purpose, and copied a list of dates from Excel into it.
See qryFinal in the new version. You can, of course, limit the date range by setting criteria for the field TheDate.

Staff TS.zip

[VKKT]

Hi Mr. Hans,

YES it is working great, Thanks a lot for the solution to my Query

I have another query related to the same, Is it possible to identify the holiday to exclude the same from showing in the query.

Regards,
VKKT

[HansV]

The easiest way is to manually delete holidays from tblDates. That will also remove holidays from the query result.

[Rudi]

Delete holidays! Now why would you ever want to do that!!!

[VKKT]

Greetings...
Hi Mr. Hans and Mr. Rudy, Thanks for your responses and solutions.

I cannot delete the holidays because for some reports I need holidays and for some not.

So what I did is added a new field as day which is created in excel and copied to Access and solved the issue.

Is it possible to add a criteria in a cross tab query to filter some specific information only., when I added it is not accepting.

Regards,
VKKT

[HansV]

Add the column on which you want to filter.
Enter the condition on which you want to filter in the Criteria row.
Set the Total row to Where. This will automatically clear the Show check box.

[VKKT]

Greetings...
Thanks for the response,

This is working for date and nos for greater than/less than etc

If I want filter between 2 dates it is not working. see the attachment

regards,
VKKT