Update One record

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"
garland29Asked:
Who is Participating?
 
Jonathan KellyCommented:
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
 
Jonathan KellyCommented:
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
 
Jonathan KellyCommented:
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
Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

 
Jonathan KellyCommented:
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
 
garland29Author Commented:
I get a Compile Error:
Expected: End of statement

0
 
Jonathan KellyCommented:
paste the exact code her
0
 
garland29Author Commented:
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
 
garland29Author Commented:
Got it to work!!  Thanks!!
0
 
Jonathan KellyCommented:
great
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.