Linked tables problem

Hello,

I have an Access 2000 database with three tables. One table with customer information a second table with employee information and a third table with incomming phone call information. When a phone call comes in, on my form the user enters in the call information such as call time and date, message, who took the call and who the call is for. The problem I am having is that on my form the user picks the employees name of who the call is for and who took the call from a combo box but I need to write the employees ID number ( primary key of employee table) instead of thier name to the table. can anyone help me? By the way I am using ado.


Here is my code:
Private Sub cmdOK_Click()

rsRecordsetCall.AddNew
'???rsRecordsetCall.Fields("Customer.FirstName").Value = cmbCallFor.DataMember
rsRecordsetCall.Fields("CallInDate").Value = dtpCallDate.Value
rsRecordsetCall.Fields("CallInTime").Value = dtpCallTime.Value
If txtMessage.Text = "" Then
MsgBox "Please Enter A Message"
Exit Sub
Else
rsRecordsetCall.Fields("CallInMessage").Value = txtMessage
End If
'Set up option button value
If OptSeverity1.Value = True Then
rsRecordsetCall.Fields("CallSeverity").Value = "1"
ElseIf OptSeverity2.Value = True Then
rsRecordsetCall.Fields("CallSeverity").Value = "2"
ElseIf OptSeverity3.Value = True Then
rsRecordsetCall.Fields("CallSeverity").Value = "3"
ElseIf OptSeverity4.Value = True Then
rsRecordsetCall.Fields("CallSeverity").Value = "4"
ElseIf OptSeverity5.Value = True Then
rsRecordsetCall.Fields("CallSeverity").Value = "5"
ElseIf OptSeverity1.Value = False And _
OptSeverity2.Value = False And _
OptSeverity3.Value = False And _
OptSeverity4.Value = False And _
OptSeverity5.Value = False Then
MsgBox "Please Select A Severity Level"
Exit Sub
End If
rsRecordsetCall.Update
'Clear bottom of form
txtMessage.Text = ""
OptSeverity1.Value = False
OptSeverity2.Value = False
OptSeverity3.Value = False
OptSeverity4.Value = False
OptSeverity5.Value = False
End Sub

Hi,
You could do a search of the employee table to find the employee ID that matches the name.
I think you can use the findfirst or seek methods to do the search. Then once you find the record save the Id in a variable and save it to your new table.

I hope that helps. If you need any clarification let me know.

Cynthia

Combo & list boxes have an index value associated with each item in the list.

When populating your list you should set the index to the ID of whatever the item is.

Eg:

In a table

ID Name
-------------------------------
24334 A Person
2342 2 Another Person

When populating your "box" say from a recordset you do the following

do while not rsPersons.EOF
cboCombo.AddItem rsPersons("Name")
cboCombo.ItemData(cboCombo.NewIndex) = rsPersons("ID")
rsPersons.movenext
loop

Then when a user selects an item in the combo you know what the ID is by using

lngSelectedID = cboCombo.itemData(cboCombo.ListIndex)


Solves the need to go off searching for IDs.

I like that idea better. I never thought about the index property.