Thanks tunsarod

[rkmorrow]

Thanks for the reply rod,

The couselors are in the same database as the clients without any distinction(not the way I would have done it).

It is all displayed in a single form.

There is a seperate additional table with the couselors name and counputer name, that allows the receptionist (who looks at one of the search forms)to notify a particular counselor that a client is here for an appointment.

The counselors can pull up the search form and look at their own data, another counselors, or a client's. It is also a 1-1 relationship, if i am understanding correctly, there are no groups of clients associated with any single counselor.

Thanks for any input.

rkmorrow

 

[tunsarod]

This is a bit wordy, but I'm going to set out a general approach which I hope will be a help to you.

Firstly the question of databases and tables. You said:

"The counsellors are in the same database as the clients without any distinction...

It is all displayed in a single form.

There is a seperate additional table with the cousellors name and computer name..."

I had assumed that information about counsellors and clients would be held in the same 'database'. The question was whether the client's details AND the counsellors details were all lumped together or kept in separate tables.

To illustrate:

I would expect there to be a table which holds each client's name address phone#, SS# and other info that is personal to the client: 'tbl_Clients'. The unique Primary Key field for such a table would naturally be called ClientID.

I have, what I thought was a reasonablly unusual combination of names and yet once I found myself at a house-party talking to a guy whose name turned out to be identical to mine. Methinks a unique ClientID field is an essential item - read a phone-book for confirmation of this.


Now I assume this august institute has several if not dozens of counsellors, any of whom might be assigned to counsel a particular client. I would assume therefore that a table would exist (tbl_Counsellors, for example) to hold the counsellor's name, address, phone number, hourly rate, hat size, inside leg, etc, and that this table would have as a Primary Key the field CounsellorID.

If the Clients table also contains a field called CounsellorID then placing a particular counsellor's ID number in a tbl_Clients!CounsellorID field would create a 1:1 relationship between the two tables for that particular Client/Counsellor pair.

If a form existed called frm_Clients with tbl_Clients as its data-source (Control Source), and if there was a textbox called txtCounsellorID which had CounsellorID as its Control Source, and a textbox called txtCousellorName had as its Control Source:

=DLookup("CounsellorName","tbl_Counsellors", _
"([CounsellorID] = [Forms]![frm_Clients]![txtCounsellorID]&quot

Then each time the recoprd changes the CojnsellorName field will refresh and providing that txtCounsellorID isn't empty the Dlookup function will return the Counsellor's name.


I would have thought that each counsellor is assigned to counsel MANY clients albeit one at a time. Therefore a 1:MANY relationship must exist between Counsellors and Clients and to my mind a distinct table for counsellors is called for.

However, if the only information held about a counsellor is his/her name then it is perfectly popssible to maintain accurate records by simply typing a counsellor's name into a field in the tbl_Clients via a textbox on the frm_Clients, although this is precisely the reason for the existence of relational databases; to avoid repetetive entries.

In the latter case a single form frm_Clients would draw all its displayed data from a single Clients table, whereas the former concept would require the form to gather at least a little information from a second table(tbl_Counsellors).

The receptionist needs to be able to call up any client and see at a glance the name of the assigned counsellor. This gives him/her access to all client records by default and by implication all counsellor records too, though not necessarily all the data in the records. Clearly if counsellors have the same forms on their PC's they too will have identical access to records.

The simple solution is to give the receptionist and counsellors different forms to view records with. The receptionist could have access to ALL records but have only those fields to view which are necessary.

In the case of the counsellors they could have a form which displays all the necessary fields but which has a query as its data-source.

Example:

Create a SELECT query which returns all the required fields. In the criteria box beneath the ID field in the query type:

Like [Forms]![CounsellorsForm]![txtMyID]

Use the 'Like' operator rather than '='. Try '=' then type a letter in the txtMyID box to see why.

Now using the Wizard create a columnar form based on the new Query. Now add a textbox and a command button in the form's header called txtMyID and cmdRequery.

In the click event for the command button type:

Private Sub cmdQuery_Click()
On Error GoTo Err_cmdQuery_Click

If txtMyID = "*" Then
MsgBox "You can't use wildcards."
txtMyID = Null
End If

Me.Form.Requery

Exit_cmdQuery_Click:
Exit Sub

Err_cmdQuery_Click:
MsgBox Err.Description
Resume Exit_cmdQuery_Click

End Sub


In the Enter event for the textbox type:

Private Sub txtMyID_Enter()
Me.txtMyID = Null
End Sub


Set the textBox Input Mask to 'Password'.

Set the following properties for the form:

AllowFilters = No
AllowAdditions = No
AllowDeletions = No

Leave AllowEdits = Yes otherwise you won't be able to use the textbox.

When a counsellor enters his Personal ID (which I suggest should be at least four digits long) into the textbox and clicks on the button only those records with that ID will be available to him/her. No other records will be accessed.

If you want to give access to say a Memo field for the counsellor to add session notes but prevent him/her from altering any other data then set AllowEdits to Yes but set all fields except the Memo field to Enabled = False and Locked = True.

You cannot use filters because the counsellor could too easliy switch off the filter to gain access to other counsellor's records. A query returns just the right records so he/she can't get access to other counsellor's records without knowledge of their individual ID's.

You should set the ID to No Duplicates but set the type to number or text but not autonumber.

Lets assume that a data-entry form is created specifically to add new counsewllor records you could either allocate a new ID manually or use a loop to repetitively create a random number of appropriate size until a number not previously used is found.

You can't use an autonumber field since if I was a counsellor with an ID of say 022 I would know that any number between 001 and 021 would be likely to get me access to another counsellor's records.

Now all this is fine and dandy but you will also have to do everything you can to prevent users from accessing the Database Window and thereby the tables, or if you want them to have access to the Database Window then perhaps you should hide the table names. Creating an MDE file might be the best solution but do keep a copy in MDB format.

I know this is a bit windy but hopefully it's clear enough for you to adapt the ideas to your current application.

Regards
Rod