Solved

Update One record

Posted on 2004-09-17
9
251 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
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!

 

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

NFR key for Veeam Agent for Linux

Veeam is happy to provide a free NFR license for one year.  It allows for the non‑production use and valid for five workstations and two servers. Veeam Agent for Linux is a simple backup tool for your Linux installations, both on‑premises and in the public cloud.

Question has a verified solution.

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

It’s been over a month into 2017, and there is already a sophisticated Gmail phishing email making it rounds. New techniques and tactics, have given hackers a way to authentically impersonate your contacts.How it Works The attack works by targeti…
In earlier versions of Windows (XP and before), you could drag a database to the taskbar, where it would appear as a taskbar icon to open that database.  This article shows how to recreate this functionality in Windows 7 through 10.
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …

623 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