Display "Current" Accounts in "Followup" Form

[MwTV]

Have a form with the following fields displayed;

Name
AccountNo
AmtReceived
DateReceived
FollowupDate

The employees populate AmtReceived, DateReceived, and FollowupDate fields.

Scenario - Employee A has reviewed several accounts over the last few days and has entered "March 28, 2007" as the FollowupDate. Employee B reviewed several accounts over the last few days and entered "March 29, 2007" as the FollowupDate. On March 28, 2007 I would like to display those accounts that have a March 28, 2007 FollowupDate on a form that is presented to the Employee A when he/she logs into the database on March 28, 2007?

On March 29, 2007, I would like to display those accounts that have a March 29, 2007 FollowupDate on a form that is presented to the Employee B when he/she logs into the database on March 29, 2007?

Is this possible? If so, how would this be implemented?

Another option just thought about is to display the "FollowupDate" field in descending order in a listbox and requery the listbox in the form's on open event.

Any additional thoughts greatly appreciated.

 

[pbail1]

Hi MwTv

One way to do this is to store the username of the person that put the account into follow-up and when you open the database you can display a form whose contents is determined by a query that takes todays date and the username as parameters.

It is easy to set up, but I need your table definition and whether your forms are bound or unbound before I can suggest what solution is easiest for you to implement. Also what version of access are you running?

 

[MwTV]

pbail1,

The database is in Access 2000 format and I currently only have Access 2003 loaded on my workstation.

Main form, frmMain, is a multi-tabbed form that is bound to a query. I setup the form whereby the end-user can only view his/her assigned accounts upon logging in every day.

The security of the database at this point is the use of the multi-tabbed form where certain pages are restricted for viewing based on the access level of the person logging in and the fact that the employee can only see his/her assigned accounts.

In essence, the following code is used to facilitate this;

Private Sub cmdLogin_Click()
'************************ login sequence
'On Error GoTo local_err
DoCmd.RunCommand acCmdSaveRecord
'
GBL_Access_Level = "X"
GBL_Access_Level = Nz(DLookup("Access_Level", "Employees", "Username='" & Nz(Me.UserName, " ") & "' and password='" & Nz(Me.Password, " ") & "'"), "X")
'
If GBL_Access_Level = "X" Then
MsgBox "Invalid Username or Password... try again."
Exit Sub
End If
'
GBL_Employee_ID = Nz(DLookup("Employee_ID", "Employees", "Username='" & Nz(Me.UserName, " ") & "' and password='" & Nz(Me.Password, " ") & "'"), "X")
'
Me.TabCtl0.Pages.Item(0).Caption = "Welcome " & Nz(DLookup("Username", "Employees", "Employee_ID=" & get_global("Employee_ID")), "Invalid Login")

Call set_privs
'
Forms!frmMain.Requery
Me.UserName = ""
Me.Password = ""
Me.TabCtl0.Pages.Item(2).SetFocus
Me.List2.Requery
cmdLogin.Enabled = False
Exit Sub
End Sub

Public Sub set_privs()

Forms!frmMain.AllowAdditions = False
Forms!frmMain.AllowDeletions = False
Forms!frmMain.AllowEdits = False
Forms!frmMain.AllowFilters = False


Select Case GBL_Access_Level
Case "M" ' manager
Me.TabCtl0.Pages.Item(1).Visible = True
Me.TabCtl0.Pages.Item(2).Visible = True
Me.TabCtl0.Pages.Item(3).Visible = True
Me.TabCtl0.Pages.Item(4).Visible = True

Forms!frmMain.AllowAdditions = True
Forms!frmMain.AllowDeletions = True
Forms!frmMain.AllowEdits = True
Forms!frmMain.Requery

Case "E" ' employee
Me.TabCtl0.Pages.Item(2).Visible = True
Me.TabCtl0.Pages.Item(4).Visible = True
Forms!frmMain.AllowAdditions = True
Forms!frmMain.AllowEdits = True
Forms!frmMain.Requery

End Select
End Sub

Note, the employee table contains the following fields;

Employee_ID (Autonumber)
Username
Password
Access Level ("E" or "M")

The accounts are assigned to the employee based on the contract id field. In other words, the six employees have predetermined contracts that he/she are responsible for. For example,

Name------------Contract ID
Sally-----------A45
John------------A46
Bill------------A50
Sheila----------A55
Jan-------------A58
Cynthia---------B25

When the employee populates the "AmtReceived", "DateReceived", and "FollowupDate" fields, the values are stored in the main table.

 

[MwTV]

pbail1,

Any further thoughts?

 

[MwTV]

Still, trying to successfully implement a form whose contents is determined by a query that takes today's date and the username as parameters.

Using Access 2000.

Ideally, I would like this form to "greet" the end-users in the morning as he/she logs in. It would contain the accounts that the end-user should followup on for the day.

Has anyone created something like this in the past? If so, what are the advantages? disadvantages? How is this done?