[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 219
  • Last Modified:

From INSERT to UPDATE

Experts,
I am having problems converting this from and INSERT to UPDATE query

strDM = "INSERT INTO tblDesignatedEmployee
 (StoreID,EmployeeID,EmployeeTypeID) 
VALUES(" & Me.StoreID.Column(0) & "," 
& Me.cboDistrictMgr.Column(0) & "," 
& Me.cboDistrictMgr.Column(2) & ")"
CurrentDb.Execute strDM, 128

Open in new window

0
Frank Freese
Asked:
Frank Freese
  • 5
  • 4
  • 2
  • +1
3 Solutions
 
peter57rCommented:
?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..
0
 
mbizupCommented:
Give this a try:

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

OM Gang
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
mbizupCommented:
Pete's correct - The query I posted will update ALL records with the values present on your form.
0
 
Frank FreeseAuthor Commented:
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.
0
 
omgangCommented:
From your explanation I believe I may have guessed correctly as to what you are wanting.  Give my update query a try.
OM Gang
0
 
mbizupCommented:
< 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.
0
 
peter57rCommented:
You still haven't said how you identify the record to be updated.
You imply that it is one of the records on the screen.  The issue is what field or fields identifies the required record from all the others?
0
 
Frank FreeseAuthor Commented:
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;

Open in new window

I have four text boxes whose control source reads from the query as follows:
=StoreID.column(3)

Open in new window

=StoreID.column(6)

Open in new window

=StoreID.column(4)

Open in new window

=StoreID.column(3)

Open 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;

Open 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
0
 
mbizupCommented:
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.
0
 
Frank FreeseAuthor Commented:
mbizup:
I've been getting some static on my design. Right now I revisiting it. Can we wait a while please?
0
 
mbizupCommented:
Absolutely.   :-)
0
 
Frank FreeseAuthor Commented:
folks,
I'm closing this thread to open a new one. To be fair, I'll ditribute points equally
0

Featured Post

Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

  • 5
  • 4
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now