SQL Update Query

This is driving me bonkers.   Any suggestions?

Thanks in advance...

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

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?

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

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.

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

IPT

Change the recordsource

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

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

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

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