Pulling information from one table with criteria & create new record.

[hathman23]

Hello all. Here is my situation if you can help me. I currently have a table called employees and a table called surplusletter. I have created a query/form/code that does the following. Each employee has a certain score. The query when run asks for the number of bottom scores you want to update associated to a specific classification. It then updates the date in the surplus letter table. The problem I have is that I want the query to still ask for the bottom number of scores and the classification but I want it to pull that information including a SSN and create a new record in the surplus letter table. Currently it will only update the date field if there is a SSN already listed in the Surplus letter table. Following is the code for it to ask for the scores and such:

Private Sub Command2_Click()
Dim db As DAO.Database
Set db = CurrentDb
db.QueryDefs("surplusdate&quot.SQL = "Select TOP " & _
InputBox("Enter # of bottom scores to be updated: ", "Parameter Prompt&quot & _
Mid$(db.QueryDefs("surplusdate&quot.SQL, InStr(1, db.QueryDefs("surplusdate&quot.SQL, "SurplusLetter&quot - 1)
db.Close
DoCmd.OpenQuery "updatedate"
DoCmd.RunMacro "closeformtest"
End Sub

In the Employees table the fields are:
SSN (primary key)
Name
Classification
Score
Address
Unit
Agency

In the SurplusLetter table the fields are:
SSN (which is the related field from the employees table)
date sent
letter undeliverable
letter # (primary key) its an autonumber.

Any help would be greatly appreciated.