combo box data not being inputted into table field

[valgore]

Hello, I have an AddEmployee form that is linked to my employee table. it has simple text boxes for the user to input information. i created two combo boxes, one pointing to the department table and the other pointing to the projects table. im using this code to add a new record, and it inputs it into the table correctly, but the CurrentDepartment and CurrentProjects fields in the employee table are always blank, even if the user selected a department or project from the combo box.

Private Sub cmdSave_Click()
Dim rs As Recordset
Dim fld As Field
Dim db As Database

Set db = CurrentDb
Set rs = db.OpenRecordset("Employees")


If IsNull(FirstName) Then
MsgBox "You must enter a First Name.", vbOKOnly
Exit Sub
End If
With rs
.AddNew
.Fields("FirstName").Value = FirstName
.Fields("LastName").Value = LastName
.Fields("PhoneNumber").Value = PhoneNumber
.Fields("Address").Value = Address
.Fields("City").Value = City
.Fields("State").Value = State
.Fields("ZIP").Value = ZIP
.Fields("DateHired").Value = DateHired
.Fields("DateTerminated").Value = DateTerminated
.Fields("CurrentDepartment").Value = Combo76.Value
.Fields("CurrentProject").Value = Combo78.Value
.Fields("Salary").Value = Salary
End With
rs.Close
Set rs = Nothing
Forms!Employees.List72.Requery
DoCmd.Close


Exit Sub
End Sub

i tried:
.Fields("CurrentDepartment").Value = Combo76
.Fields("CurrentProject").Value = Combo78
and it still doesnt work
thanks for the help!

 

[boblarson]

Why are you using code for this when you can just bind the controls to the fields if your form is bound? There are ways (not hard) to use a bound form and not save if the button for save isn't pressed.

And, also do either of your tables that the combos are getting their data from use lookups directly in the field at table level (in the departments table or projects table)?

 

[valgore]

my form is bound to the employees table so when i bind the CurrentDepartment combo box to the Department table, i get a #Name and i can't select anything. Do i need to bind my form to three different tables? If so, how do i do that.

as to your second question, no, none of those tables use lookups

 

[boblarson]

my form is bound to the employees table so when i bind the CurrentDepartment combo box to the Department table, i get a #Name and i can't select anything. Do i need to bind my form to three different tables? If so, how do i do that.

as to your second question, no, none of those tables use lookups

1. The combo is supposed to be BOUND to the employees table, not the department table. (unless I am not understanding your data structure).

2. Your combos get their ROW SOURCE from the other tables but they are to be BOUND to the table where that data is being stored.

3. If the data to be stored is in anothe table, you need a subform.

 

[valgore]

got it to work thanks!
i think i was trying to do too much by using the ADO stuff when Access pretty much does it for you. thanks for clearing things up!