Solved

Update One record

Posted on 2004-09-17
9
242 Views
Last Modified: 2012-05-05
I have an update query that I need to run on a cuurent and open record.  I have two forms.  The customer form and the deal deatail form.  I need to update the Customer Name on the tbl_Deals.  Here is what I have and I can't get it to run.

CurrentDb.Execute UPDATE tbl_Deals SET Legal_Name=Forms![frm_Customer]![Legal_Name] WHERE Deal_Rec_ID=Me!Deal_Rec_ID"
0
Comment
Question by:garland29
  • 6
  • 3
9 Comments
 
LVL 7

Assisted Solution

by:Jonathan Kelly
Jonathan Kelly earned 125 total points
ID: 12084096
do u get an error ?
is tbl_Deals deff not readonly ?

open your form and select the record

hit Ctrl+G to open the immedidate window

paste in
? Forms![frm_Customer]![Legal_Name]
and hit return
do u get a value ?

do the same for Me!Deal_Rec_ID


if u get values paste

? UPDATE tbl_Deals SET Legal_Name=Forms![frm_Customer]![Legal_Name] WHERE Deal_Rec_ID=Me!Deal_Rec_ID
into the immediate and use the result as the SQL for a new query - does the query run ?

0
 
LVL 7

Expert Comment

by:Jonathan Kelly
ID: 12084152
try

"UPDATE tbl_Deals SET Legal_Name= " & chr(34) & Forms![frm_Customer]![Legal_Name] & chr(34) & " WHERE Deal_Rec_ID=Me!Deal_Rec_ID


if deal_rec is not numeric type then wrap that in chr(34) also
0
 
LVL 7

Expert Comment

by:Jonathan Kelly
ID: 12084162
that should prob be

"UPDATE tbl_Deals SET Legal_Name= " & chr(34) & Forms![frm_Customer]![Legal_Name] & chr(34) & " WHERE Deal_Rec_ID=" & Me!Deal_Rec_ID
0
The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

 

Author Comment

by:garland29
ID: 12084186
I get a Compile Error:
Expected: End of statement

0
 
LVL 7

Expert Comment

by:Jonathan Kelly
ID: 12084246
paste the exact code her
0
 

Author Comment

by:garland29
ID: 12084270
I put this on the form open event:  The customer form is already open.

CurrentDb.Execute UPDATE tbl_Deals SET Legal_Name=Forms![frm_Customer]![Legal_Name] WHERE Deal_Rec_ID=Me!Deal_Rec_ID"
0
 
LVL 7

Accepted Solution

by:
Jonathan Kelly earned 125 total points
ID: 12084359
should it be

currentdb.execute "UPDATE tbl_Deals SET Legal_Name= " & chr(34) & Forms![frm_Customer]![Legal_Name] & chr(34) & " WHERE Deal_Rec_ID=" & Me!Deal_Rec_ID"


are u sure u are reading correct parameters ?

the on open may fire before the parameters have had a chance to pick their values


firstly get it to compile

then open the form and past
 ? "UPDATE tbl_Deals SET Legal_Name= " & chr(34) & Forms![frm_Customer]![Legal_Name] & chr(34) & " WHERE Deal_Rec_ID=" & Me!Deal_Rec_ID"

into the immediate and hit enter - u should get the actual SQL being run by jet

0
 

Author Comment

by:garland29
ID: 12084441
Got it to work!!  Thanks!!
0
 
LVL 7

Expert Comment

by:Jonathan Kelly
ID: 12084485
great
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
Phishing attempts can come in all forms, shapes and sizes. No matter how familiar you think you are with them, always remember to take extra precaution when opening an email with attachments or links.
Familiarize people with the process of utilizing SQL Server stored procedures from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Micr…
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…

685 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question