DoCmd.OpenQuery not working

[mcupito]

I am trying to run this code/query:

DoCmd.OpenQuery "ForfeitVestingDateUpdateQry"

The query updates some fields in a table based on a date comparison. (whether or not the date the user entered is > than a date in the table.)

When I run the query from design mode (the red "!"), it works. When I try to run it via this method, it does not work.

The ONLY thing I can think of is that I am passing a parameter to it via a control on a subform in an if, then, else.

If([AwardTbl]![VestingDate]>Date(),[Forms]![AstProfileFrm]![AstProfileAwardFrm].[Form]![SepDateTxt],[AwardTbl]![VestingDate])

These are the 2 dates I am comparing. [AwardTbl]![AwardDate] and [Forms]![AstProfileFrm]![AstProfileAwardFrm].[Form]![SepDateTxt].

In VBA, I captured the value of SepDateTxt, and in debug mode it was correct. Also, like previously stated, it runs fine when ran manually.

Is this an issue with passing in a parameter or am I just doing something completely wrong? I cannot figure out why it won't work.

Edit: I tried the

db.Execute

method, and it threw an error saying "Too few parameters, expected 2". One parameter is the EmployeeID coming off of the parent form, and the other is the Date field SepDateTxt.

I tested the validity of both of them, via

Dim EmpID As Long
Dim SepDate As Date
 
EmpID = Me.Parent!EmployeeID
SepDate = Me!SepDateTxt

and they both had the correct values.

[zmbd]

mcupito

Would mind posting the full SQL, or is it just terribly long?

(^_^)

[mcupito]

Sure.

UPDATE AwardTbl
INNER JOIN AssociateTbl ON AwardTbl.EmployeeID = AssociateTbl.EmployeeID
SET 
AwardTbl.ForfeitedUnits = [AwardTbl]![AwardUnits], AwardTbl.SpecialVestedUnits = 0,
 AwardTbl.ForfeitDate = Date(),
 AwardTbl.ForfeitNAV = Format(DateAdd("s",-1,DateAdd("q",DateDiff("q","1/1/1900",Date()),"1/1/1900")),"Short Date"),
 AwardTbl.RepayUnits = [AwardTbl]![PaidOutUnits], AwardTbl.VestingDate = IIf([AwardTbl]![VestingDate]>Date(),[Forms]![AstProfileAwardFrm]![SepDateTxt],[AwardTbl]![VestingDate])
WHERE (((AwardTbl.EmployeeID)=[Forms]![AstProfileFrm]![EmployeeID])
       AND ((AwardTbl.AwardDate)>Date()));

I still can't figure out how to format that control on the subform if that's what is causing the error.

The field I am trying to refer to has a control source of a field in the query that supplies the data for that form. I'm not sure if that has anything to do with it. It's control source name is SeparationDate (I tried to put that in the query also and it didn't work.)

[mcupito]

I took off RecordLocks and now it works. Very interesting.

Thanks for your assistance, zmbd, as always!