Automate Updates in Access

[Guest]

I want to configure my database so that when I click the option on my
Switchboard to open the main form, the database will also automatically
cut-and-paste (in the souce table) information from Field1 to replace
information in Field2 for records where the date in Field3 is equal to or
before today's date, but only when Field1 and Field3 are not null.

 

[Dave M]

Try this in the procedure that opens the main form:

DoCmd.RunSQL("UPDATE [MyTable] SET [Field2] = [Field1] WHERE [Field1] Is Not
Null AND [Field2] Is Not Null AND [Field3]<=Date()")

 

[John Vinson]

I want to configure my database so that when I click the option on my
Switchboard to open the main form, the database will also automatically
cut-and-paste (in the souce table) information from Field1 to replace
information in Field2 for records where the date in Field3 is equal to or
before today's date, but only when Field1 and Field3 are not null.

Run an Update query (much better than cut and paste!), if (as it
appears) you want to change the contents of an existing record or
records. Guessing here, air code, since I don't know the structure of
your tables:

DoCmd.RunSQL "UPDATE Sourcetable SET [Field2] = [Field1] " _
& "WHERE [Field3] = Date() AND [Field1] IS NOT NULL;"

If Field3 is equal to Date() then it's obviously not NULL so no
separate test is needed.

John W. Vinson[MVP]
Join the online Access Chats
Tuesday 11am EDT - Thursday 3:30pm EDT
http://community.compuserve.com/msdevapps