A query has no effect when executed within a module, but does as a standalone query!

[Marko Stojovic]

Hi there.

I was wondering if you could help me. There is a certain query which simply doesn't work when I try it in a module. However, if I transfer it into the query window and press the exclamation mark, it DOES work. Here is the text (Access 97, by the way):

Dim qryN As QueryDef
Dim MyDate As Date
MyDate = Left(Now, 8)
Set qryN = CurrentDb.CreateQueryDef("", "update [q3-2000-01 response] set " & _
"[from (new)]=#" & MyDate & "# where project_id='" & Me.project_id.Value & "'")
MsgBox qryN.SQL
qryN.Execute
MsgBox qryN.RecordsAffected

(I had to get the current date that way because when I use Mydate=Date it gives a null. During execution, Mydate evaluates to 6/7/01, Me.project_id.value gives 'PR33', which is a project number which exists in the relevant table and has a text data type. Unfortunately, the QryN.RecordsAffected is always 0.)

I was wondering why this wouldn't work... My goal is that when a checkbox is ticked on a form, the current date is recorded in this background table. All the code is in the On Click bit of the checkbox.

Does anyone have any idea, and if so, I would be quite grateful.

Thank you.

Marko.

 

[Rich]

Are you saying that you just want to set/update the date in another table?

 

[Marko Stojovic]

Yes, essentially to update it at a fixed point in time, to show when a box has been ticked. At the moment, I have an invisible textbox on the form which has the relevant date field as its source. That way, I instruct Access to change the value of that box, and then refresh the tabledefs collection. However, I'm interested in why the query is not working!

Thanks. Marko.

 

[Pat Hartman]

If you are attempting to update the current record, you are going about it all wrong. Just put a single line of code in the BeforeUpdate event of the form to update the date/time field:
Me.YourLastUpdateDate = Now()

If you really are trying to update another table, what makes you think there is a record there for the project displayed on your form?

BTW, it really is poor practice to name fields or other objects with names like -from (new) -
Object names should not contain any spaces or special characters except the underscore(_).



[This message has been edited by Pat Hartman (edited 06-07-2001).]