Too complex error message with update query

[Sam92Legacy]

I have a script which runs a make table query, then runs an update query, and finally prints a report. The query worked without a hitch on a the first computer I used (AccessXP), but I get an error message during the update query that says "The expression is typed incorrectly, or is too complex to be evaluated." on the computer I imported it to (Also running AccessXP). I can run the queries without a problem when I run them outside of the script, however.

The update query is:

UPDATE (Main INNER JOIN [Pledge Sum] ON Main.ContactID = [Pledge Sum].ContactID) INNER JOIN Events ON (Main.ContactID = Events.ContactID) AND ([Pledge Sum].ContactID = Events.ContactID) SET Events.EventCode = [Pledge Sum]![Amount]
WHERE (((Events.Event)=[Pledge Sum]![LastOfSource]));

and the script is:

Dim stDocName As String

DoCmd.SetWarnings True
'run the make table query
DoCmd.OpenQuery ("PledgeSummary&quot
'run the update query
DoCmd.OpenQuery ("EventCodeUpdate&quot
stDocName = "Events"
DoCmd.OpenReport stDocName, acPreview

Any suggestions on where I went wrong would be much appreciated.

Thanks,

Sam

 

[VBslammer]

Right off hand I don't see how it would work since you have:
[tt]
UPDATE Main ... SET Events.EventCode
[/tt]
If you're updating Events.EventCode shouldn't it be:
[tt]
UPDATE Events ... SET Events.EventCode
[/tt]
VBSlammer

 

[Sam92Legacy]

Your're right that I didn't need the Main table. I switched it to:
UPDATE Events INNER JOIN [Pledge Sum] ON Events.ContactID = [Pledge Sum].ContactID SET Events.EventCode = [Pledge Sum]![Amount]
WHERE (((Events.Event)=[Pledge Sum]![LastOfSource]));

However, I still am getting the same error message when I am it in my form.

 

[VBslammer]

Why would you set the EventCode to a dollar amount? Maybe you have a data type mismatch there. VBSlammer

 

[Sam92Legacy]

I think there is a type mismatch; fortunately, I figured out a union query that give the correct results and does away with both the action queries. Thanks for helping point out my novice errors.