Solved

Update One record

Posted on 2004-09-17
9
207 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
 

Author Comment

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

0
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
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

Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

Join & Write a Comment

This article is a continuation or rather an extension from Cascading Combos (http://www.experts-exchange.com/A_5949.html) and builds on examples developed in detail there. It should be understandable alone, but I recommend reading the previous artic…
In the previous article, Using a Critera Form to Filter Records (http://www.experts-exchange.com/A_6069.html), the form was basically a data container storing user input, which queries and other database objects could read. The form had to remain op…
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…
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …

705 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

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now