Problems with Auto-filling Forms

[amandy]

I know that I am not the first to ask about auto-filling forms, but I
have tried many examples that I've found and none of them have seemed
to work.

I'm working with Access as part of a class project, and I've never
worked with it before. While I have programming experience, I have very
little knowledge of VB.

I have two tables in my Access Database; one is of Students, and one is
of Employers. I want to keep track of information about contact that I
have made with students or with employers. I have two forms; one for
each table, that will allow input of the student/employer information,
and the details of the contact that was made with them.

I want to be able to type the student's school ID number into the form,
and IF there is another record in the database matching that ID number
(which is only part of the primary key), I want to pull the student's
contact information and fill in the form. I want to do the same with
the employer's contact information on the employer form. If there isn't
already an entry into the database for that student id or that
employer, then the information will be entered for the first time.

All of the help that I have found on the matter seems to fill in the
forms based on the very previous entry, or it gives me some sort of
error, or it simply doesn't fill in the fields. Can someone help me
out, or point me in the direction of an answer that is out there that I
have missed?

Thanks in advance,
Mandy

 

[Guest]

Hi Mandy,

responses in order;

I know that I am not the first to ask about auto-filling forms, but I
have tried many examples that I've found and none of them have seemed
to work.

I'm working with Access as part of a class project, and I've never
worked with it before. While I have programming experience, I have very
little knowledge of VB.

I have two tables in my Access Database; one is of Students, and one is
of Employers. I want to keep track of information about contact that I
have made with students or with employers. I have two forms; one for
each table, that will allow input of the student/employer information,
and the details of the contact that was made with them.

You will probably need more than just the 2 tables, otherwise you will only
be able to record contact with 1 employer and 1 student, and won't be able to
cross-reference whether a student saw an employee, or vice versa simply.

I want to be able to type the student's school ID number into the form,
and IF there is another record in the database matching that ID number
(which is only part of the primary key), I want to pull the student's
contact information and fill in the form. I want to do the same with
the employer's contact information on the employer form. If there isn't
already an entry into the database for that student id or that
employer, then the information will be entered for the first time.

If you use a combobox containing all of the student ID numbers in your
student form, then as you type in the number it will automatically finish the
rest for you. If the Student ID exists, you can use the comboboxes
afterUpdate event to select the selected student - see Filters in help for
more info. If the student ID does not exist you can use the NotInList event
coupled with setting the limit to list property to Yes (in form Properties,
data tab) to add the record to the uderlying table.
Both methods require the form to be bound to the underlying table.

All of the help that I have found on the matter seems to fill in the
forms based on the very previous entry, or it gives me some sort of
error, or it simply doesn't fill in the fields. Can someone help me
out, or point me in the direction of an answer that is out there that I
have missed?

Thanks in advance,
Mandy

Hope this helps a little, post back if you have more questions, or post a
new thread if you get stuck on actual coding issues,

TonyT..

 

[amandy]

Thanks so much for your help! I'm beginning to get somewhere now, I
think.

I have three tables now; one that stores the student id number and
contact information, one that stores the record of contact with
student, and one that stores record of contact with employer (for now I
am focusing on pulling the student data for the form).

My form has the student id number in a drop-down menu, with the contact
information (ph. num, etc.) there as well, and then it contains a
subform with all of the records of contact with that student. It works
as far as selecting the id number from the dropdown menu and displaying
the proper contacts in the subform. However, I still cannot get the
form to display the student's name and phone number in the fields in
the main form. This information is stored in the same file as the
student's id number that is in the drop-down menu.

Is this something that I need to add to the AfterUpdate for the
drop-down menu? I do still want it to add the student if the user types
in an id that is not already in the list, so those fields would not be
populated in that case.

Thanks again for your help!

Mandy

 

[Guest]

Hi again amandy,

replies in order below;

Thanks so much for your help! I'm beginning to get somewhere now, I
think.

your welcome

I have three tables now; one that stores the student id number and
contact information, one that stores the record of contact with
student, and one that stores record of contact with employer (for now I
am focusing on pulling the student data for the form).

Sounds more logical, is there not a 4th table for the employer?

My form has the student id number in a drop-down menu, with the contact
information (ph. num, etc.) there as well, and then it contains a
subform with all of the records of contact with that student. It works
as far as selecting the id number from the dropdown menu and displaying
the proper contacts in the subform. However, I still cannot get the
form to display the student's name and phone number in the fields in
the main form. This information is stored in the same file as the
student's id number that is in the drop-down menu.

Is this something that I need to add to the AfterUpdate for the
drop-down menu? I do still want it to add the student if the user types
in an id that is not already in the list, so those fields would not be
populated in that case.

The afterupdate event of the combobox should contain a way of pointing to
main form to the desired record, can be done with a Filter or with code along
the lines of;
Me.Recordsource = "SELECT * FROM tblStudent WHERE StudentID = " &
Me.myCombobox & ""
obviously changing table and field names as required!
You can then use the NotInList event to change the form to DataEntry = True,
and copy the value from combobox into a textbox bound to StudentID (poss
Hidden) Or, better still, Insert a new record into the table with the new
student ID and then it will automatically use the same select statement in
the afterUpdate eventgood luck,

TonyT..