Form RecordSource.From 2 Tables = Possible ?

[edisonl]

Hi,

1. Like to find out how do I compare login name with value from table 1,
results search lists in table to
in query criteria ?

2. Eg: Leave Application> Supervisor Login> Display Lists of Names FROM
Personnel_Table (ONLY) Where Dept= '" & LoginID!Dept & "'" & " AND
AccessRights > '" & LoginIDAccessRights'" FROM PendingLeave_Table [PS:

Higher Authority = Lower AccessRights Number]

3A. Personnel_Table:
__________________________________________________________________
UserID Name Dept AccessRights
016 Ann Accounts 3
018 Mike Accounts 4
014 Zen Accounts 4

017 June Accounts 2
024 John Production 4

3B. PendingLeave_Table:
__________________________________________________________________
UserID From To
AppliedDate
019 22/11/08 22/11/08
13/11/08
018 28/12/09 31/12/08
30/10/08
014 28/12/09 31/12/08
30/10/08
024 13/11/08 14/11/08
09/09/08
017 13/11/08 14/11/08
09/09/08

Therefore , When Ann (UserID:16) Login She Can only view Zen(UserID:14),
Mike (UserID:18)
leave request. Others due to different access rights & department constraint
she wouldnt be able to view.

 

[Damon Heron]

I use a global variable in a Global Module:

Option Compare Database
Option Explicit
Public curUser As Long
Public UserPermissions As Long

At logon the curUser would be the id of the person logging on, and the
UserPermissions would be the number of their "Access Rights",
this coming from a log table that has ID, name, userpermission, password.
Id you put it in a combobox, the UserID would be .column(0), the
userpermission would be .column(2)
Then, in various forms, I test if the curUser has permission to open or
view... that is up to you how you want to implement that.

Damon

 

[edisonl via AccessMonster.com]

Hi Damon,

1. Thanks for replying. I had no problem controlling access rights and the
authorisation for viewing and actions.

2. Practically thats what i did using a textbox to capture loginid and
compare it in database table (Personnel Table consists of access rights,
their approving superior id, gender bla bla bla..)

3.However things get a litle out of hand when I tried second functions-
Superior Login
Eg: This functions suppose to perform a check on PendingLeave_Table for lists
of userid, This userid in Personnel_Table
had the Superiorid as their Leave Approval Superior.

4.Put it simply, you are my Leave Approval Superior under Personnel_Table. I
Apply for Leave> My Request goes into PendingLeave_table> When You Login,
System is suppose to alert you for An Approving Request. (Base on my Leave
request)

5.If there are whole lists of names under our deparment requires your
approval, you should be able to navigates through form to approve them.






________________________________________________________________________________________________

Hi,

1. Like to find out how do I compare login name with value from table 1,
results search lists in table to
in query criteria ?

2. Eg: Leave Application> Supervisor Login> Display Lists of Names FROM
Personnel_Table (ONLY) Where Dept= '" & LoginID!Dept & "'" & " AND
AccessRights > '" & LoginIDAccessRights'" FROM PendingLeave_Table [PS:

Higher Authority = Lower AccessRights Number]

3A. Personnel_Table:
__________________________________________________________________
UserID Name Dept AccessRights
016 Ann Accounts 3
018 Mike Accounts 4
014 Zen Accounts 4

017 June Accounts 2
024 John Production 4

3B. PendingLeave_Table:
__________________________________________________________________
UserID From To
AppliedDate
019 22/11/08 22/11/08
13/11/08
018 28/12/09 31/12/08
30/10/08
014 28/12/09 31/12/08
30/10/08
024 13/11/08 14/11/08
09/09/08
017 13/11/08 14/11/08
09/09/08

Therefore , When Ann (UserID:16) Login She Can only view Zen(UserID:14),
Mike (UserID:18)
leave request. Others due to different access rights & department constraint
she wouldnt be able to view.

_________________________________________________________________________

 

[Damon Heron]

I am having a little problem with the pending leave table. What happens
when leave is approved? Does the record get deleted?

Better to have a LeaveRequest table with a checkbox field for pending. Then
you could have a subroutine:
This is so the User can check or uncheck the leave request on the form.

First, create a query like this: (you may have to tweak it some for your
tablenames, etc.)

SELECT PersonnelTable.UserID, PersonnelTable.EmpName, PersonnelTable.Dept,
PersonnelTable.AccessRights, LeaveRequestsTable.From, LeaveRequestsTable.To,
LeaveRequestsTable.Pending
FROM PersonnelTable INNER JOIN LeaveRequestsTable ON PersonnelTable.UserID =
LeaveRequestsTable.UserID
WHERE (((PersonnelTable.Dept)=[forms]![frmlogon]![txtDept]) AND
((PersonnelTable.AccessRights)>[forms]![frmlogon]![txtAR]) AND
((LeaveRequestsTable.Pending)=True));

save as qryLeave

then in a logon form:

Private Sub Combo0_AfterUpdate()' I used a combobox to list names, 'etc.,
but you could use an afterupdate event in a textbox. 'Note the two textboxes
in the above query that hold Dept (txtDept) and AccessRights '(txtAR)
PendingLeave ' call subroutine
End Sub

Public Sub PendingLeave()
'this opens a form user would see pending leave requests.
If Nz(DCount("UserID", "LeaveRequestsTable", "Pending = true"), 0) > 0 Then
DoCmd.OpenForm "frmLeaveReq", acNormal

End If
End Sub

finally, on the frmLeaveReq, whose recordsource is qryLeave, we check if
there are any records:

Private Sub Form_Load()

' Check the RecordCount of the form.
If Me.RecordsetClone.RecordCount = 0 Then
DoCmd.Close acForm, "frmLeaveReq"
End If
' this closes the form immediately if there are no records from the
qryLeave
End Sub

Damon

Hi Damon,

1. Thanks for replying. I had no problem controlling access rights and the
authorisation for viewing and actions.

2. Practically thats what i did using a textbox to capture loginid and
compare it in database table (Personnel Table consists of access rights,
their approving superior id, gender bla bla bla..)

3.However things get a litle out of hand when I tried second functions-
Superior Login
Eg: This functions suppose to perform a check on PendingLeave_Table for
lists
of userid, This userid in Personnel_Table
had the Superiorid as their Leave Approval Superior.

4.Put it simply, you are my Leave Approval Superior under Personnel_Table.
I
Apply for Leave> My Request goes into PendingLeave_table> When You Login,
System is suppose to alert you for An Approving Request. (Base on my Leave
request)

5.If there are whole lists of names under our deparment requires your
approval, you should be able to navigates through form to approve them.






________________________________________________________________________________________________

Hi,

1. Like to find out how do I compare login name with value from table 1,
results search lists in table to
in query criteria ?

2. Eg: Leave Application> Supervisor Login> Display Lists of Names FROM
Personnel_Table (ONLY) Where Dept= '" & LoginID!Dept & "'" & " AND
AccessRights > '" & LoginIDAccessRights'" FROM PendingLeave_Table
[PS:

Higher Authority = Lower AccessRights Number]

3A. Personnel_Table:
__________________________________________________________________
UserID Name Dept AccessRights
016 Ann Accounts 3
018 Mike Accounts 4
014 Zen Accounts 4

017 June Accounts 2
024 John Production 4

3B. PendingLeave_Table:
__________________________________________________________________
UserID From To
AppliedDate
019 22/11/08 22/11/08
13/11/08
018 28/12/09 31/12/08
30/10/08
014 28/12/09 31/12/08
30/10/08
024 13/11/08 14/11/08
09/09/08
017 13/11/08 14/11/08
09/09/08

Therefore , When Ann (UserID:16) Login She Can only view Zen(UserID:14),
Mike (UserID:18)
leave request. Others due to different access rights & department
constraint
she wouldnt be able to view.

_________________________________________________________________________

 

[Damon Heron]

Oh, forgot one thing. since the query is not updateable, you will need a
small subform on the frmLeaveReq, with the recordsource as the LeaveRequest
Table, and one field: Pending. The link between the frm and subform would
be UserID. The pending box could then be checked or unchecked and the table
updated.

Damon

I am having a little problem with the pending leave table. What happens
when leave is approved? Does the record get deleted?

Better to have a LeaveRequest table with a checkbox field for pending.
Then you could have a subroutine:
This is so the User can check or uncheck the leave request on the form.

First, create a query like this: (you may have to tweak it some for your
tablenames, etc.)

SELECT PersonnelTable.UserID, PersonnelTable.EmpName, PersonnelTable.Dept,
PersonnelTable.AccessRights, LeaveRequestsTable.From,
LeaveRequestsTable.To, LeaveRequestsTable.Pending
FROM PersonnelTable INNER JOIN LeaveRequestsTable ON PersonnelTable.UserID
= LeaveRequestsTable.UserID
WHERE (((PersonnelTable.Dept)=[forms]![frmlogon]![txtDept]) AND
((PersonnelTable.AccessRights)>[forms]![frmlogon]![txtAR]) AND
((LeaveRequestsTable.Pending)=True));

save as qryLeave

then in a logon form:

Private Sub Combo0_AfterUpdate()' I used a combobox to list names, 'etc.,
but you could use an afterupdate event in a textbox. 'Note the two
textboxes in the above query that hold Dept (txtDept) and AccessRights
'(txtAR)
PendingLeave ' call subroutine
End Sub

Public Sub PendingLeave()
'this opens a form user would see pending leave requests.
If Nz(DCount("UserID", "LeaveRequestsTable", "Pending = true"), 0) > 0
Then
DoCmd.OpenForm "frmLeaveReq", acNormal

End If
End Sub

finally, on the frmLeaveReq, whose recordsource is qryLeave, we check if
there are any records:

Private Sub Form_Load()

' Check the RecordCount of the form.
If Me.RecordsetClone.RecordCount = 0 Then
DoCmd.Close acForm, "frmLeaveReq"
End If
' this closes the form immediately if there are no records from the
qryLeave
End Sub

Damon

Hi Damon,

1. Thanks for replying. I had no problem controlling access rights and
the
authorisation for viewing and actions.

2. Practically thats what i did using a textbox to capture loginid and
compare it in database table (Personnel Table consists of access rights,
their approving superior id, gender bla bla bla..)

3.However things get a litle out of hand when I tried second functions-
Superior Login
Eg: This functions suppose to perform a check on PendingLeave_Table for
lists
of userid, This userid in Personnel_Table
had the Superiorid as their Leave Approval Superior.

4.Put it simply, you are my Leave Approval Superior under
Personnel_Table. I
Apply for Leave> My Request goes into PendingLeave_table> When You Login,
System is suppose to alert you for An Approving Request. (Base on my
Leave
request)

5.If there are whole lists of names under our deparment requires your
approval, you should be able to navigates through form to approve them.






________________________________________________________________________________________________

Hi,

1. Like to find out how do I compare login name with value from table 1,
results search lists in table to
in query criteria ?

2. Eg: Leave Application> Supervisor Login> Display Lists of Names FROM
Personnel_Table (ONLY) Where Dept= '" & LoginID!Dept & "'" & " AND
AccessRights > '" & LoginIDAccessRights'" FROM PendingLeave_Table [PS:

Higher Authority = Lower AccessRights Number]

3A. Personnel_Table:
__________________________________________________________________
UserID Name Dept
AccessRights
016 Ann Accounts 3
018 Mike Accounts 4
014 Zen Accounts 4

017 June Accounts 2
024 John Production 4

3B. PendingLeave_Table:
__________________________________________________________________
UserID From To
AppliedDate
019 22/11/08 22/11/08
13/11/08
018 28/12/09 31/12/08
30/10/08
014 28/12/09 31/12/08
30/10/08
024 13/11/08 14/11/08
09/09/08
017 13/11/08 14/11/08
09/09/08

Therefore , When Ann (UserID:16) Login She Can only view Zen(UserID:14),
Mike (UserID:18)
leave request. Others due to different access rights & department
constraint
she wouldnt be able to view.

_________________________________________________________________________

 

[Damon Heron]

Sorry that should read : the link between the frm and subform would be the
Primary keyID of the LeaveRequest Table.
That way you can account for more than one leave request per employee and
only uncheck pending on each one separately .

Damon

Oh, forgot one thing. since the query is not updateable, you will need a
small subform on the frmLeaveReq, with the recordsource as the
LeaveRequest Table, and one field: Pending. The link between the frm and
subform would be UserID. The pending box could then be checked or
unchecked and the table updated.

Damon

I am having a little problem with the pending leave table. What happens
when leave is approved? Does the record get deleted?

Better to have a LeaveRequest table with a checkbox field for pending.
Then you could have a subroutine:
This is so the User can check or uncheck the leave request on the form.

First, create a query like this: (you may have to tweak it some for your
tablenames, etc.)

SELECT PersonnelTable.UserID, PersonnelTable.EmpName,
PersonnelTable.Dept, PersonnelTable.AccessRights,
LeaveRequestsTable.From, LeaveRequestsTable.To,
LeaveRequestsTable.Pending
FROM PersonnelTable INNER JOIN LeaveRequestsTable ON
PersonnelTable.UserID = LeaveRequestsTable.UserID
WHERE (((PersonnelTable.Dept)=[forms]![frmlogon]![txtDept]) AND
((PersonnelTable.AccessRights)>[forms]![frmlogon]![txtAR]) AND
((LeaveRequestsTable.Pending)=True));

save as qryLeave

then in a logon form:

Private Sub Combo0_AfterUpdate()' I used a combobox to list names, 'etc.,
but you could use an afterupdate event in a textbox. 'Note the two
textboxes in the above query that hold Dept (txtDept) and AccessRights
'(txtAR)
PendingLeave ' call subroutine
End Sub

Public Sub PendingLeave()
'this opens a form user would see pending leave requests.
If Nz(DCount("UserID", "LeaveRequestsTable", "Pending = true"), 0) > 0
Then
DoCmd.OpenForm "frmLeaveReq", acNormal

End If
End Sub

finally, on the frmLeaveReq, whose recordsource is qryLeave, we check if
there are any records:

Private Sub Form_Load()

' Check the RecordCount of the form.
If Me.RecordsetClone.RecordCount = 0 Then
DoCmd.Close acForm, "frmLeaveReq"
End If
' this closes the form immediately if there are no records from the
qryLeave
End Sub

Damon

Hi Damon,

1. Thanks for replying. I had no problem controlling access rights and
the
authorisation for viewing and actions.

2. Practically thats what i did using a textbox to capture loginid and
compare it in database table (Personnel Table consists of access rights,
their approving superior id, gender bla bla bla..)

3.However things get a litle out of hand when I tried second functions-
Superior Login
Eg: This functions suppose to perform a check on PendingLeave_Table for
lists
of userid, This userid in Personnel_Table
had the Superiorid as their Leave Approval Superior.

4.Put it simply, you are my Leave Approval Superior under
Personnel_Table. I
Apply for Leave> My Request goes into PendingLeave_table> When You
Login,
System is suppose to alert you for An Approving Request. (Base on my
Leave
request)

5.If there are whole lists of names under our deparment requires your
approval, you should be able to navigates through form to approve them.






________________________________________________________________________________________________

edisonl wrote:
Hi,

1. Like to find out how do I compare login name with value from table 1,
results search lists in table to
in query criteria ?

2. Eg: Leave Application> Supervisor Login> Display Lists of Names FROM
Personnel_Table (ONLY) Where Dept= '" & LoginID!Dept & "'" & " AND
AccessRights > '" & LoginIDAccessRights'" FROM PendingLeave_Table [PS:

Higher Authority = Lower AccessRights Number]

3A. Personnel_Table:
__________________________________________________________________
UserID Name Dept AccessRights
016 Ann Accounts 3
018 Mike Accounts 4
014 Zen Accounts 4

017 June Accounts 2
024 John Production 4

3B. PendingLeave_Table:
__________________________________________________________________
UserID From To
AppliedDate
019 22/11/08 22/11/08
13/11/08
018 28/12/09 31/12/08
30/10/08
014 28/12/09 31/12/08
30/10/08
024 13/11/08 14/11/08
09/09/08
017 13/11/08 14/11/08
09/09/08

Therefore , When Ann (UserID:16) Login She Can only view Zen(UserID:14),
Mike (UserID:18)
leave request. Others due to different access rights & department
constraint
she wouldnt be able to view.

_________________________________________________________________________

 

[edisonl via AccessMonster.com]

Hi Damon,

Yup ve think of that, using subform name (PendingLeave_Table) Linking
PendingLeave_Request,
however just wondering can i do it in a more GUI friendly style ? Eg:
Bounding all controls (Text Boxes)
in the form to PendingLeave_Table ?

But challenge here is only filter display instead of showing all records in
PendingLeave_table.

Is that possible to generate base on query ?

Sorry that should read : the link between the frm and subform would be the
Primary keyID of the LeaveRequest Table.
That way you can account for more than one leave request per employee and
only uncheck pending on each one separately .

Damon

Oh, forgot one thing. since the query is not updateable, you will need a
small subform on the frmLeaveReq, with the recordsource as the

[quoted text clipped - 133 lines]

 

[Damon Heron]

A filter might be the way to go! Set the leave req form bound to the table.
Add unbound textboxes for name, dept, AccessRights.
In the control source of each, add the expression:
(this one is for the Dept - txtUserID is a textbox bound to UserId on the
mainform)

=DLookUp("Dept","PersonnelTable","UserID = " & [txtUserID]).

In the Logon form,
Public Sub PendingLeave()
'this opens a form user would see pending leave requests.
If Nz(DCount("UserID", "LeaveRequestsTable", "Pending = true"), 0) > 0
Then
DoCmd.OpenForm "frmLeaveReq", acNormal, , , , , True
End If
End Sub

The True at the end of the OpenForm is OpenArgs that you are passing to the
leavereq form.

In the LeaveReq form:

Private Sub Form_Load() 'this checks to see if the form was opend by the
logon, otherwise no filter
If Me.OpenArgs = True Then
Me.Filter = "(Dept=[forms]![frmlogon]![txtDept]) AND
(AccessRights>[forms]![frmlogon]![txtAR]) AND (Pending=True)"
Me.FilterOn = True
Else
Me.FilterOn = False
End If
End Sub

This way the form LvReq can be used to add leave requests for all employees
or filter and approve based on employee log on.
Actually much simpler than the query, but hinges on the use of the dlookup
textboxes.

Damon

Hi Damon,

Yup ve think of that, using subform name (PendingLeave_Table) Linking
PendingLeave_Request,
however just wondering can i do it in a more GUI friendly style ? Eg:
Bounding all controls (Text Boxes)
in the form to PendingLeave_Table ?

But challenge here is only filter display instead of showing all records
in
PendingLeave_table.

Is that possible to generate base on query ?

Sorry that should read : the link between the frm and subform would be the
Primary keyID of the LeaveRequest Table.
That way you can account for more than one leave request per employee and
only uncheck pending on each one separately .

Damon

Oh, forgot one thing. since the query is not updateable, you will need
a
small subform on the frmLeaveReq, with the recordsource as the

[quoted text clipped - 133 lines]

_________________________________________________________________________