Inserting a foreign key. Excel VBA and MS Access 2003

Question

Hi, I have two tables: Department(Dept_ID,Dept_Name). and table Employee(Empl_ID, Empl_Name,Salary,Dept_ID). The IDs are auto-numbers. Dept_ID in table Employee is a foreign key refering to Dept_ID in Table Department. Now, I want to insert a record to table Employee using Excel VBA which is connected to Access 2003 database where my tables reside. How can I get the right value of Dept_ID and insert it to table Employee given that the user will only submit the Dept_Name that belongs to the submitted employee ? I use a simple insert Append query :

insert into table Employee (Empl_Name,Salary,Dept_ID)
values (Parameter_Empl_Name, Parameter_Salary, Parameter_Dept_ID)

I have all the values except the value of Parameter_Dept_ID which referes to the foreign key Dept_ID. I don't know how to generate the correct Dept_ID and insert it to table Employee given that I know the Dept_Name for that ID. Is the problem clear? Please help,

Answer 1

I would guess that the best bet would be to look up the dept id first, this should allow you to check that it exists and prompt the user if it does not, so, something on the lines of:

Set rs=CreateObject("ADODB.Recordset")

sSQL="SELECT DeptID FROM Depts WHERE DeptName='" & <Cell with Dept Name> & "'"

rs.Open sSQL, <connection>

DeptID=rs!DeptID