How to get key for drop-down list

[mitchmcc]

I have a relatively simple Access 2007 question, but as a newbie I am not sure how to get what I need. I should also note that I inherited an existing application that I am modifying.



I have a simple "Add Employee" form that allows the employee's group to be selected via a drop-down list that is bound to the "Groups" table. So far, so good, and the drop-down works fine.

Now, when the user presses the "Save" button, I can take the rest of the textboxes and use them for the insert into the Employees table, but at this point I now need to translate the Group name to be the Group ID, which is what is stored in the Employees table.

Currently, I created a query called queryActiveGroups which is what the drop-down is bound to. It does NOT grab the Group ID, just the name and status.

Is there a way to make the Group ID available to my VBA code if I include it in the query but mark it as "not shown" (or something like that)?

I have used embedded selects in Oracle, where you could do the following (SQL pseudocode):

insert into employees(name, group_id) values (lstEmpName, (select group_id from Groups where Group_Name = cboGroupName));

Thanks,

Mitch

[aytee111]

Grab the GroupID from the Groups table for your dropdown. Make it the first field. Set the column count to 2, the bound column to 1, and when setting the column widths set the first column to 0. Now any time you reference the dropdown you will be referencing the ID instead of the name - use xxx.column(1) to reference the name in VBA (numbering starts at 0 for the first column).

It is also a good idea to bind your form directly to the Employees table instead of using SQL to insert the record afterwards. The GroupID can then be bound directly to the table.

[mitchmcc]

Thanks, aytee111... I think I understand now.