Add if not in table

[NISMOJim]

I've been away from Access for about a year, and have forgotten everything I didn't know too well to begin with. Here's what I have...



tblEmp is full of employee names and other information. frmReq is my main form for completing a parts requisition, which has a record source of tblReq. On frmReq is a combo box (cboName with a control source of ReqName to be saved into tblReq) that drops down the list of names of all users, and their related information will display in other textboxes via DLookups in those textboxes. cboName uses the row source of tblEmp.

All of this works great, with the exception of two problems...

A new employee that isn't in the table yet will need to complete the information in all of the textboxes and save the record in tblEmp. I believe this can be done with something like
CurrentDb.Execute "UPDATE tblEmp SET tblEmp.EmpName = " & Me.cboName
and so on for each field.

The other problem comes when a user is in the table, but needs to edit one of the other fields from tblEmp. The code needs to determine if the employee name is already in the table, so a duplicate record isn't created for that employee.
Once determined that his record is in the table, it will only need to update the fields that need editing.

Here is my thought process...

If the name typed in the combobox, or selected from the dropdown list is already in tblEmp, then only update the fields that are changed on the form.
If nothing is changed, only add the name in a new record for tblReq (other fields further down the form will have a control source of tblReq as part of this record.
If the name is not already in tblEmp, then the name along with the other fields need to be added as a new record.

I'm guessing this will be a loop through the records, then an if statement, but it's beyond anything I know how to do.

Thanks for any & all help!

[ranman256]

No, you don't add 1 field at a time with code.
You open a form and the user enters ALL fields for that 1 employee record. No code needed.

now, you can start with a search to see if the employee already exists. If no records found,open the entry screen to add new record in the docmd.OpenForm command.

[orange]

I agree with ranman256.

Check for Employee existence first. If not found, you need an Append(INSERT) query to add/insert a new Record in the table.