Data Entry Problem

[Khartoum]

I am designing a database to manage staff absences. I have a number of forms
(one for each manager) feeding one table for company reports etc. I have a
'member of staff' field that is a comby on each form to restrict entry for
managers to their team members only.
My problem is that i set the data entry property to 'Yes'. This clears the
form on open but prevents me from finding records (obviously because the form
is empty). If i set the data entry to 'No', all records from other managers
are showing on all managers forms also. Due to data protection i need to
overcome this.
What i need is 10 forms feeding one table but i need the manager to be able
to see alll their entries but not everybody elses. has anybody any advice?
Thanks John aka Khartoum

 

[Allen Browne]

Instead of creating 10 forms, or using Data Entry mode, set the RecordSource
of the form so it shows only entries for one manager.

Presumably you have tables like this:
Staff table, with fields:
StaffID AutoNumber Primary key
Surname Text
FirstName Text
ManagerID Number relates to the key field in manager
table.

Absence table, with fields:
AbsenceID AutoNumber Primary key
StaffID Number relates to Staff.StaffID
AbsenceDate Date/Time when person was absent.

And presumably you have a way to determine which MangerID is using the
database (so you know which records to show.) If so, use the Open event
procedure of the form to:
a) limit the form so it shows only absences for the manager's staff;
b) limit the combo so it only shows staff for this manager.

This kind of thing:

Private Sub Form_Open(Cancel As Integer)
Dim strSql As String

strSql = "SELECT Absence.* FROM Absence INNER JOIN Staff ON
Absence.StaffID = Staff.StaffID WHERE ManagerID = " & SomeNumber & " ORDER
BY AbsenceID;"
Me.RecordSource = strSql

strSql = "SELECT StaffID, Surname & "", "" + FirstName AS FullName FROM
Staff WHERE ManagerID " & SomeNumber & " ORDER BY Surname, FirstName,
StaffID;"
Me.StaffID.RowSource = strSql
End Sub

Hints:
a) Watch the line wrap above.
b) You can mock up a query using your fields, switch to SQL View, and get an
example of the SQL strings you need to create.
c) You cannot use a control on *this* form to get the manager: the code runs
before the data gets loaded.

 

[Khartoum]

Thanks Allen, tht has put me on the right track

Instead of creating 10 forms, or using Data Entry mode, set the RecordSource
of the form so it shows only entries for one manager.

Presumably you have tables like this:
Staff table, with fields:
StaffID AutoNumber Primary key
Surname Text
FirstName Text
ManagerID Number relates to the key field in manager
table.

Absence table, with fields:
AbsenceID AutoNumber Primary key
StaffID Number relates to Staff.StaffID
AbsenceDate Date/Time when person was absent.

And presumably you have a way to determine which MangerID is using the
database (so you know which records to show.) If so, use the Open event
procedure of the form to:
a) limit the form so it shows only absences for the manager's staff;
b) limit the combo so it only shows staff for this manager.

This kind of thing:

Private Sub Form_Open(Cancel As Integer)
Dim strSql As String

strSql = "SELECT Absence.* FROM Absence INNER JOIN Staff ON
Absence.StaffID = Staff.StaffID WHERE ManagerID = " & SomeNumber & " ORDER
BY AbsenceID;"
Me.RecordSource = strSql

strSql = "SELECT StaffID, Surname & "", "" + FirstName AS FullName FROM
Staff WHERE ManagerID " & SomeNumber & " ORDER BY Surname, FirstName,
StaffID;"
Me.StaffID.RowSource = strSql
End Sub

Hints:
a) Watch the line wrap above.
b) You can mock up a query using your fields, switch to SQL View, and get an
example of the SQL strings you need to create.
c) You cannot use a control on *this* form to get the manager: the code runs
before the data gets loaded.