From INSERT to UPDATE

?No ESP feature here, I'm afraid.
You'll have to explain what you want to update with wha and how you identify the correct record(s) to update..

strDM = 'UPDATE tblDesignatedEmployee SET EmployeeID = " & Me.cboDistrictMgr.Column(0) & ", EmployeeTypeID = " & Me.cboDistrictMgr.Column(2) & " WHERE StoreID = " & Me.StoreID.Column(0)

OM Gang

Pete's correct - The query I posted will update ALL records with the values present on your form.

all records? I used unique indexing to avoid duplicate records so I'd be only modifying the record in question?
Here's what happens. I call up four records from the tblDesignatedEmployee. If a record needs to be modified then I would like to modify the changes in that record while maintaing the values in the other three records when I UPDATE. What I present was one of for INSERTS that I would change to UPDATE.Let me try one of the UPDATE offered to see where that takes me.

< If a record needs to be modified then I would like to modify the changes in that record while maintaing the values in the other three records when I UPDATE.>

I think we'd need to know how to uniquesly identify that one record that needs to be updated...

Anyhow, as with any of these action query questions, make a backup before testing anything.

I did back-up before testing and did not get the results I was looking for. See if this helps:
In my form frmModifyDesignatedEmplyee I have a combo box labeled StoreID that executes the following query in the record source:

SELECT DISTINCT tblStore.StoreNumberID, tblStore.StoreNumber, "# " 
& [StoreNumber] & " " & [StoreAddressOne] AS Store, 
(Select [tblEmployee.EmployeeFirstName]&" "
&[tblEmployee.EmployeeLastName] AS DMName  
FROM tblEmployee INNER JOIN tblDesignatedEmployee  
ON tblEmployee.EmployeeID = tblDesignatedEmployee.EmployeeID 
Where tblDesignatedEmployee.StoreID = tblStore.StoreNumberID 
And tblDesignatedEmployee.EmployeeTypeID=1) AS DistrictManager, 
(Select [tblEmployee.EmployeeFirstName]&" "&[tblEmployee.EmployeeLastName]
 as SMName  FROM tblEmployee INNER JOIN tblDesignatedEmployee  
ON tblEmployee.EmployeeID = tblDesignatedEmployee.EmployeeID 
Where tblDesignatedEmployee.StoreID = tblStore.StoreNumberID
And tblDesignatedEmployee.EmployeeTypeID=2) AS StoreManager, (Select [tblEmployee.EmployeeFirstName]&" "& [tblEmployee.EmployeeLastName]
 AS AMName  FROM tblEmployee INNER JOIN tblDesignatedEmployee ON  tblEmployee.EmployeeID = tblDesignatedEmployee.EmployeeID Where
tblDesignatedEmployee.StoreID = 
tblStore.StoreNumberID And tblDesignatedEmployee.EmployeeTypeID=3)
AS AssistantManager, (Select [tblEmployee.EmployeeFirstName]&" "&[tblEmployee.EmployeeLastName] AS ASMName
 FROM tblEmployee INNER JOIN tblDesignatedEmployee
ON tblEmployee.EmployeeID = tblDesignatedEmployee.EmployeeID
Where tblDesignatedEmployee.StoreID = tblStore.StoreNumberID
And tblDesignatedEmployee.EmployeeTypeID=5) AS AreaSupportManager
FROM tblStore;

Select allOpen in new window

I have four text boxes whose control source reads from the query as follows:

=StoreID.column(3)

Select allOpen in new window

=StoreID.column(6)

Select allOpen in new window

=StoreID.column(4)

Select allOpen in new window

=StoreID.column(3)

Select allOpen in new window

Each corresponding text box has an associated combo bound control based on a query and the row source looks like this (I'll only post one unless you need them all):

SELECT tblEmployee.EmployeeID, [EmployeeFirstName]
 & " " & [EmployeeLastName] AS Employee, 
tblEmployee.EmployeeTypeID, tblEmployee.Active
FROM tblEmployee
WHERE (((tblEmployee.EmployeeTypeID)=1 Or 
(tblEmployee.EmployeeTypeID)=5) AND 
((tblEmployee.Active)=Yes))
ORDER BY tblEmployee.EmployeeFirstName;

Select allOpen in new window

The purpose for bound combo control is to identify who the replacement employee will be, thus updating the record in the table tblDesignatedEmployee.
I can download the db if that'll help. Thanks everyone

Go ahead and post a sample (pared down as much as possible to just the necessary forms and tables).

We do need to figure out how to single out the record that gets updated.  Its still not clear from your last post.

Maybe seeing the database would help out in that respect.

If you could also briefly explain specifically what was wrong with the results from the queries you tried (how your expected results differ), that would be helpful too.

mbizup:
I've been getting some static on my design. Right now I revisiting it. Can we wait a while please?

Absolutely.   :-)

folks,
I'm closing this thread to open a new one. To be fair, I'll ditribute points equally