Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 128639
  • Last Modified:

SQL Update Query

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
iptrader
Asked:
iptrader
  • 5
  • 3
  • 3
  • +4
5 Solutions
 
rthomsenCommented:
Try this

UPDATE RejectSub SET RejectSub.ValidValue = "Y"
WHERE ((([RejectSub]![UID])=[Forms]![PostReFlowSub1]![UID]));
0
 
iptraderAuthor Commented:
This is driving me bonkers.   Any suggestions?

Thanks in advance...
0
 
iptraderAuthor Commented:
rthomsen, I have already tried that without success.  thanks...
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
rthomsenCommented:
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
 
iptraderAuthor Commented:
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
 
Mikal613Commented:
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
 
rthomsenCommented:
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
 
serbachCommented:
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
 
iptraderAuthor Commented:
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
 
AtropaCommented:
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
 
Mikal613Commented:
Change the recordsource
0
 
serbachCommented:
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
 
heer2351Commented:
>> 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
 
serbachCommented:
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
 
iptraderAuthor Commented:
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
 
Luke ChungPresidentCommented:
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

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 5
  • 3
  • 3
  • +4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now