How do I auto enter a field based on another field's value?

[Rollersmithy]

I have read a thru 20 pages of threads and I have come up with some close problems but I am still confused how to do this. The database I have records two sets of time parameters for a technical school. The first is teaching time, the second is machinery time. Everything is working well except the data entry screen is not idiot proof enough to prevent silly entries. Each staff and student has a unique 6 digit identifier (Primary key in their tables). Each teaching task has two unique identifiers. The first is a unique common name (Primary key in its table) and the second is an identifier from the schools student records database. What I would like to do is have access auto enter the staff/student reference number on selection of a First/surname. Also I would like the program to auto enter the school records identifier upon user selection of the Common name. Any ideas?

Regards

Greg Smith
Coolangatta

 

[tunsarod]

You can use a drop combo box that displays the student or staff member names. Use a Query as the Row Source for the drop-list and use the After_Update event for the drop-combo to insert the individual's ID from the Drop-combo box into the required field on the form.

If the staff and students names are kept in a single table then their personal ID's are bound to be unique but if staff and student names are in separate tables you may need to identify that in your time card record.

Assuming a table called STAFF and a table called STUDENTS the following SQL Union query could be used to select a person and update the PersonalID field on your time logging form.

SELECT STAFF.PersonalID, "Staff" As Type,STAFF.Firstname, STAFF.Lastname
FROM STAFF UNION SELECT STUDENTS.PersonalID, "Student" As Type,STUDENTS.Firstname, STUDENTS.Lastname
FROM STUDENTS;

Use this to create a Union query that you can use as the Row Source for a drop-box.

If Students and Staff records are in a single table called , say PERSONNEL, then use:

SELECT PERSONNEL.PersonalID, PERSONNEL.Firstname, PERSONNEL.Lastname
FROM PERSONNEL


You could use the following SQL to create the combo-box Row Source in which case you could store the composite Type/ID to create a single reference with T for teacher and S for student... just a thought.


SELECT STAFF.PersonalID, "T" As Type & STAFF.PersonalID,STAFF.Firstname, STAFF.Lastname
FROM STAFF UNION SELECT STUDENTS.PersonalID, "S" As Type & STUDENTS.PersonalID,STUDENTS.Firstname, STUDENTS.Lastname
FROM STUDENTS;


I hope this helps.

Rod

 

[Steve101]

Greg,

I'll assume that you have a Staff table which has the fields Surname, FirstName, StaffNumber in it, and that when you populate your "transaction table" with staff data, you use a combo box to select from a list of staff names, and wish to then populate the transaction table with the resulting record, using the StaffNumber field as part of its foreign key.

In this scenario, Set up the combo box with the following properties:

Name: cmbStaffNumber
ContolSource: StaffNumber
RowSourceType: TableQuery
RowSource: SELECT Surname, FirstName, StaffNumber
FROM tblStaff
ORDER BY Surname, FirstName, StaffNumber
ColumnCount: 3
ColumnWidths: 3;3;0
BoundColumn: 3

I assume that this form is bound to the table which contains the transactions which are being maintained by the form, and this table has a field called StaffNumber.
In this case, when you open the form with the above combo box, when you select the combo box and pull it down, the list of surnames, firstnames will be displayed in alphabetical A to Z order. The StaffNumber will not be visible, as it is given a width of zero. Nevertheless it will be used to write back to the transaction table, as the BoundColumn setting of 3 ensures that it is this column of the query, and not the Surname or Firstname columns (1 and 2) of the query which are bound back to the table.

Using this method, you expose the Surname and Firstname to the end user to make his selection, but use the StaffNumber field as the identity field back to the bound table associated with the form.

I hope that this makes sense to you, and that you can extrapolate it back to your solution.

Cheers,
Steve

 

[Rollersmithy]

Thanks Rod and Steve, both solutions work well. You have been a great help as I have been pulling my hair out.

Cheers

Greg