SQL Update Query : update, query, sql

May 17, 2008 05:50am pdt

1. capricorn1 1,301,290
2. DatabaseMX 890,113
3. LSMConsu... 482,407
4. boag2000 335,153
5. harfang 313,826
6. peter57r 272,376
7. TextReport 229,085
8. GRayL 209,614
9. ee_rlee 170,073
10. cactus_data 154,536
11. jimhorn 151,903
12. matthews... 147,637
13. JezWalters 137,435
14. jefftwilley 126,171
15. puppydog... 102,935

1. capricorn1 8,958,328
2. nico5038 5,685,831
3. rockiroads 5,398,237
4. peter57r 3,716,871
5. LPurvis 3,361,835
6. shanesue... 3,288,317
7. LSMConsu... 3,217,997
8. harfang 2,930,659
9. stevbe 2,838,604
10. DatabaseMX 2,773,449
11. jimhorn 2,571,495
12. GRayL 2,369,458
13. flavo 2,104,083
14. mbizup 1,976,668
15. jadedata 1,963,977

12.08.2003 at 11:00AM PST, ID: 20819731

	[x] Attachment Details

[x]

The Solution Rating System

With so many solutions, how can you tell which solutions are most likely to help you and which ones are not? To provide you with a tool to use, we rate our solutions based on various elements that most accurately determine if a solution is a quality solution. To explain what factors affect the solution rating, here are the elements we take into consideration when formulating our solution rating.

The Grade of the Solution
The Zone Rank of the Expert Providing the Solution
The Number of Author and Expert Comments
The Number of Experts Contributing
The Feedback of the Community

Your Input Matters
Because of the way the system is set up, the most important variable in this equation is you. As a member of Experts Exchange, you are able to cast your vote on the quality of the solutions in regard to how complete, accurate, helpful and easy to understand each solution is. When you provide your feedback, each rating is adjusted accordingly. So, if you see a solution that has a poor rating that you think is a good solution, let us know by rating it. As you do, the rating will be adjusted and will become more accurate for other members of our site.

If you have any suggestions that you would like to make for our rating system, please ask a question in the Suggestions Zone of Community Support.

Thank you!

6.6

SQL Update Query

Zone: Microsoft Access Database

Tags: update, query, sql

I am running the following Update query:

UPDATE RejectSub SET RejectSub.ValidValue = "Y"
WHERE ((([RejectSub]![UID])=[Forms]![Form3]![Form4]![PostReFlowSub1]![UID]));

Please notice the WHERE clause. This procedure works fine when called from it's main form (PostReFlowSub1). However, I now have to call it from its subform, and it breaks everytime, prompting for parameter value. I have tried various things without success. Any help here would be greatly appreciated, since after this is done my project is basically finished :)

Thanks. Please let me know if you have any questions.

IPT

Start your free trial to view this solution

Question Stats

Zone: Microsoft

Question Asked By: iptrader

Solution Provided By: rthomsen

Participating Experts: 5

Solution Grade: A

Translate:

Loading Advertisement...

Microsoft

Internet Protocols
Applications

Development
OS

Hardware
Windows Security

Apple

Operating Systems
Hardware

Programming
Networking

Software

Internet

Search Engines
File Sharing
WebTrends / Stats
Spy / Ad Blockers

Web Browsers
New Net Users
Web Development
Chat / IM

Anti Spam
Web Servers
Anti-Virus
Email Clients

Gamers

Tips
Online / MMORPG
Puzzle
Emulators

Action / Adventure
Role Playing
Consoles
Game Programming

Strategy
Sports
Misc
Computer Games

Digital Living

Hardware
New Net Users
New Users

Software
Digital Music
Gaming World

Home Security
Apple
Networking Hardware

Virus & Spyware

Vulnerabilities
IDS
Encryption
Anti-Virus

Operating Systems Security
Software Firewalls
WebApplications
Cell Phones

Operating Systems
Internet
Hardware Firewalls

Hardware

Handhelds / PDAs
Displays / Monitors
Components
Networking Hardware

Peripherals
Laptops/Notebooks
Storage
Servers

Desktops
New Users
Misc
Apple

Software

System Utilities
Industry Specific
Network Management
Photos / Graphics
Page Layout
VMWare
Misc
Web Development

OS
CYGWIN
Voice Recognition
Message Queue
Quality Assurance
Security
Firewalls
MultiMedia Applications

Development
Database
Office / Productivity
Business Management
OS/2 Apps
Server Software
Internet / Email

ITPro

OS
Storage
Encryption
Operating Systems Security
Apple Hardware
Laptops & Notebooks
Servers
Networking Hardware
Peripherals

Devices
Displays / Monitors
WebTrends / Stats
Search Engines
Firewalls
WebApplications
IDS
Vulnerabilities
Email Clients

File Sharing
Spy / Ad Blockers
Web Browsers
Web Servers
Networking
Anti-Virus
Chat / IM
Anti Spam

Developer

Web Servers
Web Browsers
Game Programming
Dev Tools
Industry Specific
Office / Productivity

Database
CYGWIN
Web Development
Search Engines
File Sharing
WebTrends / Stats

Programming
Content Management
Application Servers
Protocols

Storage

Removable Backup Media
Storage Technology
Servers

Grid
Remote Access
Backup / Restore

Misc
Hard Drives

Miscellaneous
Security
Development
Linux
VMWare

MainFrame OS
Unix
Apple
OS / 2
AS / 400

BeOS
Microsoft
VMS / OpenVMS

Database

Oracle
Miscellaneous
MySQL
Software
Sybase
Contact Management
PostgreSQL
Data Manipulation
Clarion
InterSystems Cache

Siebel
MUMPS
OLAP
SQLBase
SAS
GIS & GPS
4GL
Berkeley DB
DB2
Informix

Interbase / Firebird
FoxPro
Reporting
LDAP
Filemaker Pro
MS SQL Server
dBase
MS Access

Security

Misc
Web Browsers
Software Firewalls
Operating Systems Security
File Sharing

Spy / Ad Blockers
Vulnerabilities
WebApplications
IDS
Anti-Virus

Encryption
Anti Spam
Email Clients
VPN
Chat / IM

Programming

Editors IDEs
Installation
Handhelds / PDAs
Multimedia Programming
System / Kernel

Algorithms
Game
Signal Processing
Project Management
Open Source

Database
Misc
Languages
Processor Platforms
Theory

Web Development

Scripting
Blogs
Web Servers
Software
Search Engines
Web Graphics
Images

Internet Marketing
Images and Photos
Components
Document Imaging
Web Languages/Standards
Illustration
WebApplications

Fonts
WebTrends / Stats
Authoring
Digital Camera Software
Miscellaneous

Networking

Protocols
Apple Networking
Network Management
Message Queue
Application Servers
Content Management
File Servers
Email Servers
Misc
Java Editors & IDEs

Wireless
Networking Hardware
Backup / Restore
System Utilities
ISPs & Hosting
Web Servers
Storage Technology
Removable Backup Media
Servers
Broadband

Grid
OS / 2
Novell Netware
Unix Networking
Windows Networking
Security
Telecommunications
Operating Systems
Linux Networking

Other

Community Advisor
Lounge
Community Support
New Net Users

Philosophy / Religion
Math / Science
Miscellaneous
URLs

Expert Lounge
Politics
Puzzles / Riddles

Community Support

Suggestions
New to EE
New Topics
Community Advisor

CleanUp
Announcements
General

Feedback
Input
EE Bugs

12.08.2003 at 11:31AM PST, ID: 9898640

rthomsen:

Try this

UPDATE RejectSub SET RejectSub.ValidValue = "Y"
WHERE ((([RejectSub]![UID])=[Forms]![PostReFlowSub1]![UID]));

Accepted Solution

12.08.2003 at 11:32AM PST, ID: 9898645

iptrader:

This is driving me bonkers. Any suggestions?

Thanks in advance...

12.08.2003 at 11:33AM PST, ID: 9898648

iptrader:

rthomsen, I have already tried that without success. thanks...

12.08.2003 at 11:34AM PST, ID: 9898652

rthomsen:

Are you using this SQL statement in Code are in a Query Object? Where are you calling it?

What is the name of the form where the UID field resides?

12.08.2003 at 11:42AM PST, ID: 9898717

iptrader:

I'm using it in a query and I'm calling it with:

Dim StDocName as String
StDocName = "Update"
DoCmd.OpenQuery StDocName

I am trying to call this in two different forms. On the first form (PostReFlowSub1) it runs fine. That is where UID resides. The second time I am trying to call it from a subform nested in a different form (PostReFlowSub2), and the UID resides on PostReFlowSub2. When I call it from the subform, it does not work.

Btw, both PostReFlowSub1 and PostReFlowSub2 are on tab controls, nested on a form (Form4) nested on a main tab control, which, in turn, is nested on Form3). Both Form3 and Form4 are unbound and are there strictly to support the tab controls.

Hope this helps. Please let me know if you have any questions, I hope my explanation hasn't thoroughly confused you. Thanks for your help.

IPT

12.08.2003 at 11:51AM PST, ID: 9898793

Mikal613:

CAn you make a Global Variable , Set it on the First Form and call the query modified with the Variable

Is there an error generated?
If so what is it?

Assisted Solution

12.08.2003 at 11:56AM PST, ID: 9898840

rthomsen:

You are going to need 2 seperate queries as your criteria depends on 2 different fields.
Also, I don't think you need to reference tab controls when getting a form control value.

You might want to try executing the SQL statement in code like the following:

Public Sub CallUpdate (UIDVal as integer)
Dim SQL

SQL = "UPDATE RejectSub SET RejectSub.ValidValue = 'Y'
WHERE UID = " & Cstr(UIDVal)
CurrentDb.execute(SQL)

End Sub

You can place this function in a module and call it with any control value containing a user id that you need.

For Example calling it from the subform
CallUpdate me.uid.value
or on another form
Callupdate Forms("FormName").controlName.Value

I Hope this helps.

12.08.2003 at 12:00PM PST, ID: 9898875

serbach:

IPT,

It looks like you not only have a table named RejectSub but you have an object named RejectSub, is that correct? I've never created a query that has a WHERE clause that references objects on both sides of the WHERE condition. I can't see how a query using this WHERE clause would work. Might this be the problem?

Otherwise it might be in the deeply embedded object on the right-hand side of the WHERE clause. You might need to add to that string of qualifiers that describe the UID object on the right-hand side of the WHERE statement; that is, add a .Controls or .Form at the appropriate place in your object string. I know that .Controls is the default collection, but I've found it useful in certain situations where the string of subforms and objects is long.

Sincerely,

Steve Erbach
Scientific Marketing
Neenah, WI

Assisted Solution

12.08.2003 at 12:07PM PST, ID: 9898929

iptrader:

Steve, RejectSub is only a table, not object. Thanks for your suggestion, I'll post back and let you know how it worked.

IPT

12.08.2003 at 12:26PM PST, ID: 9899133

Atropa:

Try this instead..

strSQL = "UPDATE RejectSub SET RejectSub.ValidValue = 'Y'
WHERE ((([RejectSub]![UID])=" & [Forms]![Form3]![Form4]![PostReFlowSub1]![UID] & "));"

DoCmd.RunSQL strSQL

I believe that you need a break in your text stream to look up the value from your main form. Give it a go anyway, I think that it should work. /Atropa

Assisted Solution

12.08.2003 at 12:29PM PST, ID: 9899167

Mikal613:

Change the recordsource

12.08.2003 at 12:45PM PST, ID: 9899313

serbach:

IPT,

>> RejectSub is only a table, not object <<

The reason I thought it was an object, too, was because you use bang notation for the RejectSub UID field in the WHERE clause. I've just never seen that before. Shouldn't it read:

WHERE ([RejectSub].UID = ...

Sincerely,

Steve Erbach
Scientific Marketing
Neenah, WI

12.08.2003 at 03:46PM PST, ID: 9900531

Rank: Genius

heer2351:

>> The second time I am trying to call it from a subform nested in a different form (PostReFlowSub2), and the UID resides on PostReFlowSub2.

So should, in that case, your query not be:

UPDATE RejectSub SET RejectSub.ValidValue = "Y"
WHERE [UID]=[Forms]![Form3]![Form4]![PostReFlowSub2]![UID]

But like rthomsen already stated I would build the statement dynamically:

docmd.runsql "UPDATE RejectSub SET RejectSub.ValidValue = 'Y'
WHERE [UID]=" & [Forms]![Form3]![Form4]![PostReFlowSub1]![UID]

for your first form and

docmd.runsql "UPDATE RejectSub SET RejectSub.ValidValue = 'Y'
WHERE [UID]=" & [Forms]![Form3]![Form4]![PostReFlowSub2]![UID]

for your second form.

If the button/event that calls the code is on the subform you can even use the following line in both occasions:

docmd.runsql "UPDATE RejectSub SET RejectSub.ValidValue = 'Y'
WHERE [UID]=" & Me![UID]

Assisted Solution

12.08.2003 at 04:57PM PST, ID: 9900857

serbach:

IPT,

May I call you IP?

You said in your first message, "However, I now have to call it from its subform, and it breaks everytime, prompting for parameter value." What parameter does it ask for? That should point to the bogus part of your query.

The UID values on PostReFlowSub2 and PostReFlowSub1: are they different or is the same value in both instances of the field on the different subforms?

Otherwise it sure looks like you'd need two queries, if you want to continue executing the query by DoCmd.OpenQuery stDocName. That is, stDocName needs to be set to a different query name for the PostReFlowSub2 subform...and this seems like it should work in that situation:

UPDATE RejectSub SET RejectSub.ValidValue = 'Y'
WHERE ([RejectSub].[UID]=[Forms]![Form3]![Form4]![PostReFlowSub2]![UID]);

Sincerely,

Steve Erbach
Scientific Marketing
Neenah, WI

12.09.2003 at 12:24PM PST, ID: 9907108

iptrader:

So stupid of me. It was actually in the form's properties. I duplicated a form, and bound it to different tables, but when I used it as subform I didn't change its name in subform properties. As soon as I made the change the original query worked.

Thanks to all of you guys for the help. I only wish EE allowed me to disburse more points, your help was very much appreciated.

Best,

IPT

20080236-EE-VQP-29