data validation (perhaps DLookUp?) 1

[uscitizen]

i am developing a 'screening log' which will allow the telephone screener to screen the same person for more than one experiment.

last name, first name, mi and study_number are the members of the pk in that order (so that the same person can be logged as having been screened for more than one experiment). the preceding are obviously required.

a medical_record number which is akin to ssn (in the sense that it's unique to an individual) is also logged on this.

so here's my question -- how can i generate a l/u list which brings to the data enterer's attention the names of all entries which have already been logged as well as their corresponding medical record numbers when any of the aforesaid fields have been already entered? the idea's to minimize data entry errors.

“The philosophy of the school room in one generation will be the philosophy of government in the next." --- Abraham Lincoln

 

[willir]

uscitizen

Let's step back a bit so I can understand...

You conduct telephone interviews to see what experiments a candidate is eligible for?? They have a name and an assigned medical record number. Both name and record number are unique to the "member" but you will be using the medical_record number (for confidentiality and "blind" objectivity I suspect)?

So this means you will have a "member" or "subject" table. (Or maybe both to truely divorce the member's name from their medical_record number)

But then I get a bit lost...
You want a look up list box which "flags" the end user of existing entries.

Do you mean that you want to prevent the system from assigning more than one medical_record number to a subject?


I guess the first approach would be to ask if they have a medical_record number.


But then you have to use their name to filter out or select a subject.


I usually use an unbound combo box but a list box would work just as well.

There are four basic steps...

Step 1)
Use the wizard for the combo or list box to create the basics. The key here is not to have the field bound to the form. A good field to use would be the subject's last name.

After the list / comb box is created, you have to modify the select query and column widths.

Step 2)
First the Select clause
Select the combo /list box and open up the property window (right click and select property at the bottom of the list). Select the data tab, high light the "Row source" field, click on the "..." graphic to the right of the field to open up the Query builder.

Assuming you created comb / list box using the last name, and the first name and middle initial to the query. Sort in ascending order the last name and then the first name. The following example gives you an idea of the actual SQL statement...

SELECT DISTINCTROW contact_table_qry.contact_id, contact_table_qry.last_name, contact_table_qry.first_name, contact_table_qry.mid_initial
FROM contact_table_qry
ORDER BY contact_table_qry.last_name, contact_table_qry.first_name;

In the above example, I have a unique identifier contact_id. You may be able to use the medical_record here, or create your own ID field. (The medical_record, I am sure will be used elsewhere, and a lot.

Close the query builder and save it when prompted.

Next, under the record source field the data property, make sure the cound column is correct. In my example, I use the contact_id and set the value to 1.


Step 3)
Now switch to the format tab in the property window.

Set the column widths appropriate to your needs. The one exception is that to hide a field, you use 0". In my example, I do not want to display the first column.

Example:
0";1";0.7";1"

Specify units " for inches for each number, separate each column number with a : semicolon. No spaces. The number of entries matches the variables in the select query.

Move up to the Column count and make sure the number matches the column count. In this case 4.

Move down to List width. Set it to an appropriate size. In my example, I use 2.7"


Step 4)
Now setup an event. Select the event tab in th eproperty window. High light "After Update", enter [Event Procedure], and then click on the "..." graphic to the right of the field to open up visual basic. (Don't worry, this is pretty painless)

Type in some code

    intContact = Me.qry_last_name

    If Not IsNull(intContact) Then
        Me.Filter = "contact_id = " & intContact
        Me.FilterOn = True
    End If

You will have to modify this to meet your needs -- field names, etc.

The key is you are assigning a value to a filter.
Me.Filter = "contact_id = " & intContact

And then applying the filter.
Me.FilterOn = True

If the record exists, it will be displayed to the form including the Medical_record number. If not, the user will get an error and have to hit the ESC escape key to clear the error. (Or you can add code to gracefully handle the "not found" error. For example set the form up for a new record entry.)

DLookup would work but you may loose some of the user friendly features important to the end user such as sorting.

 

[uscitizen]

at present there is only one table, the 'screening log', which has text boxes for last, first, mi names and also med record number! mr number is a bit like ssn, unique to the individual. although the person who will be using the application can be counted upon to be quite conscientious, since the same person can be entered as having been screened on more than one occasion, i thought it might be a neat idea to have some failsafer way to ensure that when the data entry person needed to enter 'John Q Public' (MR # 12345678) a second, third, ...., time that the data entry person have a way of using a combobox or some other mechanism that would minimize the possibility of similar looking names from creeping into the 'screening log' because of typos or whathaveyou.

i gave you a red star 'cause i think you really believe what you contributed'll work, i don't know that for a fact quite yet until after i've reviewed it and a bit of experimentation.

in any event, thanks

“The philosophy of the school room in one generation will be the philosophy of government in the next." --- Abraham Lincoln