Experts View needed... search and fill function in a form... (1 Viewer)

[yhchen]

Hello experts….I have created a log system recording the usage of student services which is currently working just fine but I am looking at making some improvements….

The log form is called “Log” which writes data to a query called “Log_Data” (contained two tables: “Log_Table_Enquiry_Data” and “Ticketing Data”), there is a section which requires user to enter “Student_Number” and “Student_Name”.

As we are getting quite a few returning students, I thought I would like a function when users enter Student Number, the system will:

1. Search the existing data and fill the field “Student_Name” if there is a matched data (and actually write both fields to the system)
2. If there is no matched data, it will accept data entered and write into the system

I am not sure if this is achievable but any expert views will be very much appreciated…



Many thanks in advance for any possible help that you can offer...

 

[DCrake]

What you need is a table containing the details of the students. Each student has presumably been given a student number, so when they enter it is searches the students table for the matching number, if found it uses the found details, if not it should ask for verification on the number, in case entered incorrectly. Then if not found ask if you want to add it to the student details table. Whereby you will be able to add further details about the student.

 

[yhchen]

What you need is a table containing the details of the students. Each student has presumably been given a student number, so when they enter it is searches the students table for the matching number, if found it uses the found details, if not it should ask for verification on the number, in case entered incorrectly. Then if not found ask if you want to add it to the student details table. Whereby you will be able to add further details about the student.

Thank you DCrake.

In that case, would you mind give an example how the script should be written on the student number field please?

 

[DCrake]

first of all do you already have a table with a list of all the students in it? and if so what fields have you got?

If not you need to create a table that will hold each students name and Student number wich must be unique to each student.

 

[yhchen]

first of all do you already have a table with a list of all the students in it? and if so what fields have you got?

If not you need to create a table that will hold each students name and Student number wich must be unique to each student.

creating a table with a list of all students in it will not be a problem, and of course the student number will be unique to each student. the table will simply contain two fields (student_number and student_name)

I wonder if this approach will also provide a usage log which links to each student?

For example, student A (number 0001) should have three usage logs against his id if he used the service three times?

 

[thunder]

the answer is beside making the table is the DLOOKUP for the name

 

[yhchen]

sorry...but I am lost...

shall I start over again?

The form wrote to a query "Log_data", which wirtes data to two linked tables "query" and "ticketing" - and only the table "ticketing" contains student numbers and student names, where many students have more than one visting record (thus student A might have three records)

I am struggling to move the student number and student name to an individual table as what thunder and DCrake suggested, this table seems to be where the database store the student record and thus each student should have one number under one record

In this case, I don't know how this would work as I still need the system continue to record their usage history and enable me to retirve their name when entering the student number where applicable

I am not sure if I am making sense here...but if anyone get my question please help....

 

[thunder]

your question is - as i think is answered here http://www.access-programmers.co.uk/forums/showthread.php?t=167327
you can make the name field as a query and the number or code or what ever is the criteria

 

[yhchen]

I put this script as *control source* in the Student_Name field

DLookup("[Student_Name]", "[Ticketing_Data]", _"[Student_Number]='" & Forms![Log]![Student_Number] & "'")

I was hoping that it will then look for the name stored in the Ticketing_Data table which mataches the student number entered in the Form (called Log)

It is not working, as soon as I enter student number, the Student Name field appears #Name

Any suggestion? Anyone??