Form conflict

[najah via AccessMonster.com]

I am really struggling with a database I'm constructing and its probably
because I'm a newbie. At this time I think I am burned out, so this dilemma
may appear trivial to you experts.

I have a mainform Timesheets and a subform TimesheetHours. This database
will be split with a BE copy on server and the Fe copies will be distributed
to the employees in order to enter their time. The supervisors will have
master copies having access to all employees forms and report, while the
employees will have access to only their info. Their is also a restriction
placed on the forms, where they cannot edit their timesheets after 30 days of
the initial date.

My problem

I've applied filters to the employees fe copies by way of using a query and a
VB onopen Do.Cmd, so when they go to the form(timesheet) to view or edit
their time they only see their data. In order to do this I made a copy of the
Timesheet form and changed the recordsource to match the query where the
criteria matched the employee's id and then renamed the Timesheet form
attaching their initials on the end. The problem occurs when I try to
implement the restriction code.

I did the coding this way

Main form:

If Date-[DateEntered]>30 then
Me.AllowEdits=False
Else
Me.AllowEdits=True

SubForm:

If Date-Forms!TimeSheet![DateEntered]>30 Then
Me.AllowEdits=False
Else
Me.AllowEdits=True

The filtering criteria is in the mainform(Timesheet) which is the Employeed
ID Field
When I apply the above code to the Subforms of each employees of course the
Form name changes to reference the filtered form i.e TimeSheetForDC. However
, the new reference overrides
the previous code for the subform(TimeSheetHours) therefore creating an error
when I try to open another form.

Sorry for the length and I hope I explained it well enough for someone to
give me a simple solution.

Help me please!!

 

[ManningFan]

You're the skinny chick who doesn't do fat chix, right? P

I think you set this all up incorrectly. You want to use one form, and
put a filter on the form. Don't use a seperate subform for each
employee, instead set the recordsource with a filter.

For instance, I use a listbox to show the auditors I work for their
hours. The code behind the form I use is:

Me.lstHours.RowSource = "SELECT AuditNum, Auditor, WorkDate, Hours FROM
AuditsHoursLog WHERE Auditor = '" & Identity.UserName & "' ORDER BY
Auditor, WorkDate, AuditNum;"

In my case I worte a class called Identity so that won't work for you,
but you can store their login somewhere and call it just like I did
above. You would use the RecordSource of the form instead of the
RowSource of the control. In your case, you would probably use
something like this on the OnLoad event of your form:

Set strSQL = "SELECT EmployeeID, Hours, Whatever FROM YourTable WHERE
EmployeeID = ' " & Me.EmpID & " ' "

Me.frmSubFormName.Form.RecordSource = strSQL

I am really struggling with a database I'm constructing and its probably
because I'm a newbie. At this time I think I am burned out, so this dilemma
may appear trivial to you experts.

I have a mainform Timesheets and a subform TimesheetHours. This database
will be split with a BE copy on server and the Fe copies will be distributed
to the employees in order to enter their time. The supervisors will have
master copies having access to all employees forms and report, while the
employees will have access to only their info. Their is also a restriction
placed on the forms, where they cannot edit their timesheets after 30 days of
the initial date.

My problem

I've applied filters to the employees fe copies by way of using a query and a
VB onopen Do.Cmd, so when they go to the form(timesheet) to view or edit
their time they only see their data. In order to do this I made a copy of the
Timesheet form and changed the recordsource to match the query where the
criteria matched the employee's id and then renamed the Timesheet form
attaching their initials on the end. The problem occurs when I try to
implement the restriction code.

I did the coding this way

Main form:

If Date-[DateEntered]>30 then
Me.AllowEdits=False
Else
Me.AllowEdits=True

SubForm:

If Date-Forms!TimeSheet![DateEntered]>30 Then
Me.AllowEdits=False
Else
Me.AllowEdits=True

The filtering criteria is in the mainform(Timesheet) which is the Employeed
ID Field
When I apply the above code to the Subforms of each employees of course the
Form name changes to reference the filtered form i.e TimeSheetForDC. However
, the new reference overrides
the previous code for the subform(TimeSheetHours) therefore creating an error
when I try to open another form.

Sorry for the length and I hope I explained it well enough for someone to
give me a simple solution.

Help me please!!

 

[Klatuu]

How can you write a Class when you have so little of it :^{()

You're the skinny chick who doesn't do fat chix, right? P

I think you set this all up incorrectly. You want to use one form, and
put a filter on the form. Don't use a seperate subform for each
employee, instead set the recordsource with a filter.

For instance, I use a listbox to show the auditors I work for their
hours. The code behind the form I use is:

Me.lstHours.RowSource = "SELECT AuditNum, Auditor, WorkDate, Hours FROM
AuditsHoursLog WHERE Auditor = '" & Identity.UserName & "' ORDER BY
Auditor, WorkDate, AuditNum;"

In my case I worte a class called Identity so that won't work for you,
but you can store their login somewhere and call it just like I did
above. You would use the RecordSource of the form instead of the
RowSource of the control. In your case, you would probably use
something like this on the OnLoad event of your form:

Set strSQL = "SELECT EmployeeID, Hours, Whatever FROM YourTable WHERE
EmployeeID = ' " & Me.EmpID & " ' "

Me.frmSubFormName.Form.RecordSource = strSQL

I am really struggling with a database I'm constructing and its probably
because I'm a newbie. At this time I think I am burned out, so this dilemma
may appear trivial to you experts.

I have a mainform Timesheets and a subform TimesheetHours. This database
will be split with a BE copy on server and the Fe copies will be distributed
to the employees in order to enter their time. The supervisors will have
master copies having access to all employees forms and report, while the
employees will have access to only their info. Their is also a restriction
placed on the forms, where they cannot edit their timesheets after 30 days of
the initial date.

My problem

I've applied filters to the employees fe copies by way of using a query and a
VB onopen Do.Cmd, so when they go to the form(timesheet) to view or edit
their time they only see their data. In order to do this I made a copy of the
Timesheet form and changed the recordsource to match the query where the
criteria matched the employee's id and then renamed the Timesheet form
attaching their initials on the end. The problem occurs when I try to
implement the restriction code.

I did the coding this way

Main form:

If Date-[DateEntered]>30 then
Me.AllowEdits=False
Else
Me.AllowEdits=True

SubForm:

If Date-Forms!TimeSheet![DateEntered]>30 Then
Me.AllowEdits=False
Else
Me.AllowEdits=True

The filtering criteria is in the mainform(Timesheet) which is the Employeed
ID Field
When I apply the above code to the Subforms of each employees of course the
Form name changes to reference the filtered form i.e TimeSheetForDC. However
, the new reference overrides
the previous code for the subform(TimeSheetHours) therefore creating an error
when I try to open another form.

Sorry for the length and I hope I explained it well enough for someone to
give me a simple solution.

Help me please!!

 

[najah via AccessMonster.com]

Huh? I was afraid you were going to say that.

Let me give you more details then maybe you'll be able to explain this as if
I was a preschooler.

Main Form (TimeSheet)Fields:
DateEntered; EmployeeID; EmployeeName;StartDateEndDAte
Recordsource: tblTimesheet PrimKey=TimesheetID

SubForm(TimeSheetHours) Fields
DateOfTask;TaskID,Hours,etc
Recordsource: tblTimeSheetHours--Linked to tblTimeSheet via TimeSheetID

I made 9 copies of this main form (Timesheet) attaching the Employee ID
(initials) to the end
i..e. TimesheetforDC
Then in each new form I Created an VB OpenForm event:

DoCmd.OpenForm "TimeSheetforDC",,,"[EmployeeID] ='dc'"

When I split the db and distributed the FE copies, the employee would only
receive their specific form. So when they click the switchboard button, that
is the form that would open. Is this right? apparently not.
So, explain to me again what I should do, from a beginners perspective

Thanx

You're the skinny chick who doesn't do fat chix, right? P

I think you set this all up incorrectly. You want to use one form, and
put a filter on the form. Don't use a seperate subform for each
employee, instead set the recordsource with a filter.

For instance, I use a listbox to show the auditors I work for their
hours. The code behind the form I use is:

Me.lstHours.RowSource = "SELECT AuditNum, Auditor, WorkDate, Hours FROM
AuditsHoursLog WHERE Auditor = '" & Identity.UserName & "' ORDER BY
Auditor, WorkDate, AuditNum;"

In my case I worte a class called Identity so that won't work for you,
but you can store their login somewhere and call it just like I did
above. You would use the RecordSource of the form instead of the
RowSource of the control. In your case, you would probably use
something like this on the OnLoad event of your form:

Set strSQL = "SELECT EmployeeID, Hours, Whatever FROM YourTable WHERE
EmployeeID = ' " & Me.EmpID & " ' "

Me.frmSubFormName.Form.RecordSource = strSQL

I am really struggling with a database I'm constructing and its probably
because I'm a newbie. At this time I think I am burned out, so this dilemma

[quoted text clipped - 50 lines]

 

[ManningFan]

Klatuu - I'll deal with you later. Þ

Najah -
Dump the different TimeSheetHours forms. It gets too confusing, and
if you accidentally give someone the wrong front end you're in for a
nightmare.
Instead, use something that can log the user. Have them log into a
form or something with a username/password combo and check to make sure
they match. Once you have a username, you can use a filter on the
database. This is much safer and more secure than your method of
passing initials from the front end.
Theoretically, if your code is correct, your method will work.
However, it is not the best way to accomplish the task. It may seem
like it's too much hassle for one function, but once you compound it a
few times you're going to end up with a database that is completely
unmanageable.

Huh? I was afraid you were going to say that.

Let me give you more details then maybe you'll be able to explain this asif
I was a preschooler.

Main Form (TimeSheet)Fields:
DateEntered; EmployeeID; EmployeeName;StartDateEndDAte
Recordsource: tblTimesheet PrimKey=TimesheetID

SubForm(TimeSheetHours) Fields
DateOfTask;TaskID,Hours,etc
Recordsource: tblTimeSheetHours--Linked to tblTimeSheet via TimeSheetID

I made 9 copies of this main form (Timesheet) attaching the Employee ID
(initials) to the end
i..e. TimesheetforDC
Then in each new form I Created an VB OpenForm event:

DoCmd.OpenForm "TimeSheetforDC",,,"[EmployeeID] ='dc'"

When I split the db and distributed the FE copies, the employee would only
receive their specific form. So when they click the switchboard button, that
is the form that would open. Is this right? apparently not.
So, explain to me again what I should do, from a beginners perspective

Thanx

You're the skinny chick who doesn't do fat chix, right? P

I think you set this all up incorrectly. You want to use one form, and
put a filter on the form. Don't use a seperate subform for each
employee, instead set the recordsource with a filter.

For instance, I use a listbox to show the auditors I work for their
hours. The code behind the form I use is:

Me.lstHours.RowSource = "SELECT AuditNum, Auditor, WorkDate, Hours FROM
AuditsHoursLog WHERE Auditor = '" & Identity.UserName & "' ORDER BY
Auditor, WorkDate, AuditNum;"

In my case I worte a class called Identity so that won't work for you,
but you can store their login somewhere and call it just like I did
above. You would use the RecordSource of the form instead of the
RowSource of the control. In your case, you would probably use
something like this on the OnLoad event of your form:

Set strSQL = "SELECT EmployeeID, Hours, Whatever FROM YourTable WHERE
EmployeeID = ' " & Me.EmpID & " ' "

Me.frmSubFormName.Form.RecordSource = strSQL

I am really struggling with a database I'm constructing and its probably
because I'm a newbie. At this time I think I am burned out, so this dilemma

[quoted text clipped - 50 lines]

 

[najah via AccessMonster.com]

Thanx,

I know it appears confusing. I initially wanted to assign username/password
but they rallied against it and that's why I attempted a differently route.
So username/password is not an option. The filtered forms work, however I
ran into the problem when I attempted to put the edit restriction on each
form. The main form TimeSheet worked, it was the subformTimeSheetHours that
caused the issue. I kept getting the msg "couldn't find the (formname)"
because I kept having to change that reference in my vb code from
TimesheetforDc, to Timesheetforedd, etc. I don't have different Subform
(TimesheetHours) It's the Main form that has the different forms. I'm stuck
because I'm not that savvy in Coding.

Klatuu - I'll deal with you later. Þ

Najah -
Dump the different TimeSheetHours forms. It gets too confusing, and
if you accidentally give someone the wrong front end you're in for a
nightmare.
Instead, use something that can log the user. Have them log into a
form or something with a username/password combo and check to make sure
they match. Once you have a username, you can use a filter on the
database. This is much safer and more secure than your method of
passing initials from the front end.
Theoretically, if your code is correct, your method will work.
However, it is not the best way to accomplish the task. It may seem
like it's too much hassle for one function, but once you compound it a
few times you're going to end up with a database that is completely
unmanageable.

Huh? I was afraid you were going to say that.

[quoted text clipped - 56 lines]

 

[ManningFan]

If it absolutely, positively has to be this way, then create a table
with 2 fields; Username and FormName. Then, say, on the first record
you would put DC in the UserName field and TimeSheetForDC in the
Formname field. You can call the FormName out of the table to
dynamically determine which form should load based on the person's
username, which you're already passing to the form.

Does that make sense?

Or, it may be even easier to just call "TimeSheetFor" & UserName.

 

[pietlinden]

What if you were to grab the username from the OS?
http://www.mvps.org/access/api/api0008.htm

then if you set the rowsource for your forms as

SELECT...
FROM...
WHERE SomeTable.Somefield=fOSUserName

Then each user can only see his own records.

 

[ManningFan]

Rather than an API call, you can call the username from WScript.Network
with less code.

 

[Douglas J. Steele]

It may be less code, but I don't believe it's as efficient, since you'd have
to instantiate a reference to WScript. Since you can copy the code directly
from "The Access Web", typing the code shouldn't be a consideration.

In general, API calls are going to be MUCH more efficient than incorporating
Scripting objects in VBA. For example, my experience with enumerating files
on a hard drive showed that using the FindFirstFile, FindNextFile and
FindClose API calls was about an order of magnitude faster than using the
built-in VBA Dir function in conjunction with a collection to allow you to
go through all subdirectories. However, the VBA Dir function was about an
order of magnitude faster than using FSO. For example, in one test
enumerating just under 5500 files took 0.191 seconds using the APIs, 1.222
seconds using Dir, and 10.285 seconds using FSO.

Granted, for a single call, the difference likely isn't going to be
noticeable.