Populating a ComboBox with values but enter a related value in a table

[nvwildfire]

Here is my problem. I want my users to be able to enter a new student without dealing with all the other students in the table, so I have an unbound form that will be used for input of new student information into the stud_info table. I have no problems with the code to put the data from text boxes into a table but I have combo boxes that are populated from another table by a related id number. For example I have the combo box listing degree types (Masters, and PhD) but the value in my stud_info table is actually an id number in the degree table not the degree type (masters or PhD), the stud_info and degree tables are related on Deg_ID. So with that known how do I populate a combo box with the Degree type but have the Deg_ID value entered into the table when they hit the "add" button. I've done this several times in asp but am getting frustrated trying to figure this out in access and VBA. By the way I'm using ADO if that makes any difference.

Any help would be greatly appreciated.

thanks,

kgk

 

[Darrylles]

Hiya,

Punctuation and paragraphs mate.

VBA and ADO are great - just communicate the problem without frustrating me.

Regards,

Darrylle "Never argue with an idiot, he'll bring you down to his level - then beat you with experience."

 

[Rolliee]

Set your combo box column to 2 instead of 1 would be my guess tho you are making us guess.

What did one mind reader say to another???

















You are fine. How am I?

rollie@bwsys.net

 

[nvwildfire]

Point taken. What I have is a form, "frmStudent", that will be used to input data into a table called "tblStudent". The table "tblStudent" has the following fields: Stud_ID, FName, LName, FacultyID, DegreeID, and ProgramID. In the cases of FacultyID, DegreeID, and ProgramID, they are just ID numbers that relate to there respective table (i.e. tblStudent.FacultyID relates to tblFaculty.FacultyID). The tblFaculty has two fields FacultyID and FacultyName (i.e. a FacultyID value of 5 returns the value of "Miller, W" for FacultyName in the tblFaculty table).

I want my form (frmStudent) to be able to have a combobox that will supply the data for the tblStudent.FacultyID. If I were to setup a combobox based on tblStudent.FacultyID all the user would see is a number which is meaningless to them. What I want the user to see is the FacultyName text not the FacultyID number.

Is my question understandable now? If so is it possible to do this using VBA and ADO. As I stated in my first post I know how to do this using HTML forms and ASP with an access database, but am struggling with VBA.

Any help would be greatly appreciated.

kgk

 

[pgk]

Hi,
Once the user selects the degree type ( Masters or Ph.D) and clicks on the Add Button, have some code look up the Degree Id for the selected Degree Type and then enter this value into the record.

Or you could retrieve both the Degree Type as well as the Degree Id from the Degree table and display it in the Combo box. You could make the second column a zero length one so that the Degree Id is not visible to the user. Now whenever a new record is to be added, you can retrieve the Id from the combo box itself and use it in the new record.




Hope this helps. Let me know what happens.
With regards,
PGK