Update and Append Query will NOT update

[GessTechie]

Good Afternoon...

I want to update a table based upon a docmd.runsql command from a formm

The table has two text field, and two date fields, as well as a primary key.

The form runs great but when it attempts the docmd.runsql statement, it says 0 records to add.

Here is the code:

stUserLogUpdate = "INSERT INTO [User Log] ( [User ID], StartDateTime, EndDateTime, [Account Worked] )SELECT [User Log].[User ID], [User Log].StartDateTime, [User Log].EndDateTime, [User Log].[Account Worked] FROM [User Log]WHERE ((([User Log].[User ID]) = [Forms]![Credit Letter Dispute Starting Window]![Text11]) And (([User Log].StartDateTime) = [Forms]![Credit Letter Dispute Starting Window]![Text19]) And (([User Log].EndDateTime) = Now()) And (([User Log].[Account Worked]) = [Forms]![Collection Dept Credit Report Letter Disputes Resolution Form]![Customer Account Number]))"

DoCmd.RunSQL stUserLogUpdate


I've tried to create a macro to do it and a function but nothing seems to work.

Also, I dont want the user to have an option of the update. How can I get it to just do it without prompts?

 

[mikevh]

Part of your criteria in the WHERE clause is

(([User Log].EndDateTime) = Now())

Now() returns the exact date and time at that moment
(when the SQL executes).
The field in your table will never be equal to that.
Therefore your query returns no records.

To turn off the warnings "You are about to update", etc.
use "DoCmd.Setwarnings False". Be sure to turn them
back on again after the update with "DoCmd.Setwarnings True".

 

[GessTechie]

I inserted the two lines of code to turn off/on the warnings, but the table isn't updated.

Still baffled.....

 

[mikevh]

Did you do anything about the SQL?

 

[GessTechie]

I changed the EndDateTime variable and I now read the Now() from a text box in the form.

Still no update......

AAARRRRGGGGHHHH!!!!