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 KellyConnect With a Mentor Commented:
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 KellyConnect With a Mentor Commented:
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
Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

 
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
All Courses

From novice to tech pro — start learning today.