Create Access Drop Combo Box to display one table field but save an associated field

[gurmet]

I have two tables;

1. Called "Courses" that contains two fields "Course ID" and "Course Name".

2. Called "Sessions" that contains a field called "Course ID".

Then I have a form with a combo box that uses the "Course_Nam e" field for its list and stores the item you select from that list into the "Course ID" field on the "Sessions" table.

What I want it to do is use the "Course_Nam e" as its drop down list but store the associated "Course ID" from that table into the "Course ID" field into Sessions table.

For Example:

I select MS Access from the drop down list

Course_Name = MS Access
Course_ID = 1

If I select "MS Access" from the drop down list I want it to store "1" instead of "MS Access" into the "CourseiD" field in Sessions table.

By the way Sessions table the field CourseID is set to number.

Is this possible?

[hjozinovic]

Hi Gurmet!
Yes this is very possible and quite easy too.
Step 1:
In properties of combo control cboCourse put:
Row Source Type: Table/Query
Row Source: Query containing CourseName and CourseID in this order!!!
Column Count: 2
Column Widths: 3cm;0cm (make sure the second value is zero!!! This way second column will be present but will not be displayed when you open the drop-down list)
Step 2:
Now let's go to coding :-) In After Update event of the same combo control you should write code like this:

Me!SessionCourseID = Me!cboCourse.Column(1)

Note that Column(0) is reffering to first column in drop-down list (CourseName) and Column(1) is reffering to the second column (CourseID) whose width is set to zero!

This code is correct if the control SessionCourseID is on the same form like combo box control, and if its source property is set to CourseID in table Session.

regards, H.

[NeoPa]

Nice answer H :)

You may want to take it a step further though. It's possible to bind the ComboBox control to the field itself in the form. This automatically ties the value in to the underlying table field.

Benefits are :

• No code required.
• When editing existing records the value shown automatically shows the related line in the ComboBox.

[hjozinovic]

Hi.
Thanks. I was thinking the same, but he wanted to store the value in a different table:

What I want it to do is use the "Course_Nam e" as its drop down list but store the associated "Course ID" from that table into the "Course ID" field into Sessions table.

With my solution control source property of control CourseID is tied to Sessions.Course ID and is automatically populated with values from Combo box.

H.

[NeoPa]

Good answer H, but I think the "other" table in this instance was [Sessions]. IE It was NOT the table populating the ComboBox ([Courses]). However, it is, unless I've misunderstood the original post, the table the main form is built upon.

Anyway, both solutions are now available to any reader (with explanations of which to use in which circumstances), so that's a bonus :D

[gurmet]

Hi H, NeoPa

I did what you suggested.
I m able to view my drop down with the values, which mean just display the course code, the course id did not appear.
Upon my selection of course code from the dropdown, i get this error message:

The value you entered isn't valid for this field.
For example, you may entered text in a numeric field or a number that is larger than then FieldSize setting permits.

My table (Sessions) the field is course_id as numeric because i only want to keep the course_id not the course code, because a users can edit the course code but not the course_id and also due to data redundancy.

Pls help.
Gurmet

[gurmet]

Hi Guyz...while waiting ...try to figure it out from your explanation step by step again, and i did was to change the code below becuase my couse_id is first column, course_code was second column,

Me!course_id = Me!course_id.Co lumn(0)
and now it works.

Thank you very much.
Regards.Gurmet

[hjozinovic]

That's great Gurmet!
Obviously you understand it better now and you're able to solve minor problems yourself. That's great!
Welcome to bytes ;-)

[NeoPa]

...
I did what you suggested.
I m able to view my drop down with the values, which mean just display the course code, the course id did not appear.
Upon my selection of course code from the dropdown, i get this error message:
...

If you want to use this technique then you will need to make the .BoundColumn of the ComboBox match the column that matches the field that you want it bound to.

From your post #7 I would guess that would be 0, but I don't have the info to say for sure I'm afraid.

Anyway, you have the other way working, so don't worry about this unless you particularly want to do it this way.

Well done, BTW, it's always gratifying to see members getting solutions for themselves after learning a little about what's available :)