Solved

SQL Update Query

Posted on 2003-12-08
16
128,587 Views
Last Modified: 2011-08-18
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
0
Comment
Question by:iptrader
  • 5
  • 3
  • 3
  • +4
16 Comments
 
LVL 2

Accepted Solution

by:
rthomsen earned 150 total points
ID: 9898640
Try this

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

Author Comment

by:iptrader
ID: 9898645
This is driving me bonkers.   Any suggestions?

Thanks in advance...
0
 

Author Comment

by:iptrader
ID: 9898648
rthomsen, I have already tried that without success.  thanks...
0
 
LVL 2

Expert Comment

by:rthomsen
ID: 9898652
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?
0
 

Author Comment

by:iptrader
ID: 9898717
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
0
 
LVL 48

Assisted Solution

by:Mikal613
Mikal613 earned 50 total points
ID: 9898793
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?

0
 
LVL 2

Expert Comment

by:rthomsen
ID: 9898840
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.
0
 
LVL 3

Assisted Solution

by:serbach
serbach earned 100 total points
ID: 9898875
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
0
Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

 

Author Comment

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

IPT
0
 
LVL 5

Assisted Solution

by:Atropa
Atropa earned 100 total points
ID: 9899133
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
0
 
LVL 48

Expert Comment

by:Mikal613
ID: 9899167
Change the recordsource
0
 
LVL 3

Expert Comment

by:serbach
ID: 9899313
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
0
 
LVL 23

Assisted Solution

by:heer2351
heer2351 earned 100 total points
ID: 9900531
>> 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]

0
 
LVL 3

Expert Comment

by:serbach
ID: 9900857
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
0
 

Author Comment

by:iptrader
ID: 9907108
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
0
 
LVL 10

Expert Comment

by:LukeChung-FMS
ID: 26040226
Here's a paper we wrote specifically on Update Query syntax with examples and situations where it fails. Hope it's helpful:
http://www.fmsinc.com/MicrosoftAccess/query/snytax/update-query.html
 
0

Featured Post

How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

Join & Write a Comment

QuickBooks® has a great invoice interface that we were happy with for a while but that changed in 2001 through no fault of Intuit®. Our industry's unit names are dictated by RUS: the Rural Utilities Services division of USDA. Contracts contain un…
I originally created this report in Crystal Reports 2008 where there is an option to underlay sections. I initially came across the problem in Access Reports where I was unable to run my border lines down through the entire page as I was using the P…
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…

707 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

18 Experts available now in Live!

Get 1:1 Help Now