Very simple (I think) form..

[snipe]

Hey guys,


Here is a snippet of the form I am making. I am trying to make it as simple as possible. It is built on its own table [T_Nicknames]. This is used so that mailing that go to a customer come from a salesperson's common name, instead of his official name that is in our system.

The desired workflow for this form would be a user selects the store, then a salesperson (the salesperson drop down is built a query that will only show salespeople at the store that is selected), the first and last names get populated via a dlookup to our live database, then the user types in the nickname they want to use. Everything works great now, except 1 thing. Say a salesperson, Anne, (code: AA1234) already had a nickname put in. A customer brings in one of the mailers and her name was spelled incorrectly. Currently, when a user selects something from the drop, the program is creating a new record. I would rather it search to see if AA1234 has a record first. If the record exists, edit that one. If it doesn't exist, create a new one.

I know of other ways I could accomplish this, but I am trying to keep the front end as simple as possible.

[ranman256]

Do NOT use dlookup for the record. (slow and cumbersom)
Do:
the form is connected to a table
then the user picks the person, FILTER the data on the form

me.filter = "[emp] = ' " & cboEmpcode & "'"
me.filteron = true

It will show existing record, not add new ones.

[John_G]

Why are you using a separate table for the nickname? Would it be easier to just add a nickname field in the main employee table?

If you are using the dropdown as a search value, don't bind it to the EmpCode field, otherwise you will get unwanted changes to your data. Use the code that Ranman256 shows to find the record you want to edit (After Update event of the combo box).

[snipe]

Why are you using a separate table for the nickname? Would it be easier to just add a nickname field in the main employee table?

If you are using the dropdown as a search value, don't bind it to the EmpCode field, otherwise you will get unwanted changes to your data. Use the code that Ranman256 shows to find the record you want to edit (After Update event of the combo box).

The main employee table format is not editable. The nickname table is in the same program that all of our market is done through.

I will try the code from ranman. Thanks for the help!