HELP WTH AUTOEXEC MACRO

[Takeadoe]

Dear NG,

Can someone assist me with writing the little code that is needed to
run an update table query each time the database is opened? From what
I've been able to glean from this group, the Autoexec Macro looks like
the way to go. Could someone please assist?

Thank you very much!

Mike

[fredg]

On 28 Sep 2006 18:57:25 -0700, Takeadoe wrote:

Dear NG,

Can someone assist me with writing the little code that is needed to
run an update table query each time the database is opened? From what
I've been able to glean from this group, the Autoexec Macro looks like
the way to go. Could someone please assist?

Thank you very much!

Mike

Each time the database is opened?
Create a macro
Macro Action OpenQuery (Your Update query Name)

Name this macro AutoExec

Perhaps you meant, more logically, once a day when the database is
opened.
Add a table to your database.
Table Name "tblUpdated"
FieldName "DateUpdated" indexed, No Duplicates)

Create a macro to first check the table to see if the Update has
already been done.

Macro Condition
DCount("*","tblUpdated","[DateUpdated] # = " & Date() & "#") =0

If date has already been added, cancel the macro.
Macro Action StopMacro

If not, run the update query.
Macro Action OpenQuery (Update query name)

Then run an append query to add the current date to the table.

Macro Action OpenQuery (Append query name which adds the current date
to the table.)

Name the macro AutoExec

If you wish to avoid the Query "You are about to Update ...etc...."
warnings, place a SetWarnings False macro just before the first
OpenQuery, and a SetWarnings True macro at after the last OpenQuery.

--
Fred
Please respond only to this newsgroup.
I do not reply to personal e-mail

[Takeadoe]

Fred - Thanks so much for taking the time to respond. My reason for
wanting to run the update (once daily is probably sufficient as you
suggested) is to work around another problem that I've given up on
trying to conquer. Data captured at a scanner are automatically
appended to a database table. Problem is, I cannot for the life of me
figure out how to get Access to recognize a date/time value as a
date/time value. If I set the data type as date, the program crashes.
So, once in Access, I have to create 2 new variables of the date/time
format and parse the respective pieces from this text string. There
will be numerous occasions for folks to open this database while data
is being added. I don't want them to have to run the update query each
time they want to look at the data. When the table is complete, I will
likely run a make-table query and make the new variables permanent. As
you probably gathered, I know very little about Access. But, I'm
anxious to learn. I thought it would be neat if there was some way to
use the same functions that are used in the update query in the design
of the table so the new variables would be created automatically from
the original date/time text string. I'm all ears if you got
suggestions.

Again, thanks for taking YOUR time to help!

Mike

fredg wrote:

On 28 Sep 2006 18:57:25 -0700, Takeadoe wrote:

Dear NG,

Can someone assist me with writing the little code that is needed to
run an update table query each time the database is opened? From what
I've been able to glean from this group, the Autoexec Macro looks like
the way to go. Could someone please assist?

Thank you very much!

Mike

Each time the database is opened?
Create a macro
Macro Action OpenQuery (Your Update query Name)

Name this macro AutoExec

Perhaps you meant, more logically, once a day when the database is
opened.
Add a table to your database.
Table Name "tblUpdated"
FieldName "DateUpdated" indexed, No Duplicates)

Create a macro to first check the table to see if the Update has
already been done.

Macro Condition
DCount("*","tblUpdated","[DateUpdated] # = " & Date() & "#") =0

If date has already been added, cancel the macro.
Macro Action StopMacro

If not, run the update query.
Macro Action OpenQuery (Update query name)

Then run an append query to add the current date to the table.

Macro Action OpenQuery (Append query name which adds the current date
to the table.)

Name the macro AutoExec

If you wish to avoid the Query "You are about to Update ...etc...."
warnings, place a SetWarnings False macro just before the first
OpenQuery, and a SetWarnings True macro at after the last OpenQuery.

--
Fred
Please respond only to this newsgroup.
I do not reply to personal e-mail

[salad]

Takeadoe wrote:

Fred - Thanks so much for taking the time to respond. My reason for
wanting to run the update (once daily is probably sufficient as you
suggested) is to work around another problem that I've given up on
trying to conquer. Data captured at a scanner are automatically
appended to a database table. Problem is, I cannot for the life of me
figure out how to get Access to recognize a date/time value as a
date/time value. If I set the data type as date, the program crashes.

Are you saying there'd be no need to run the query if you could get the
program to recognize the date?

How is the data coming into the app? What's the format string? What
does it look like? Give us an example of a date that crashes in a date
field?

Is there an error message?

Look at DateValue, DateSerial, and CDate in help to see if there is a
way to update the date when appended.

How is data "automatically appended" from a scanner? Does the scanner
recognize Access tables and mdbs? I should think there's some code that
appends the images.

I think if you provided some example code that occurs when appending
images and where it bombs or the process that you are currently using
you'll get some help here to overcome the crash.

So, once in Access, I have to create 2 new variables of the date/time
format and parse the respective pieces from this text string. There
will be numerous occasions for folks to open this database while data
is being added. I don't want them to have to run the update query each
time they want to look at the data. When the table is complete, I will
likely run a make-table query and make the new variables permanent. As
you probably gathered, I know very little about Access. But, I'm
anxious to learn. I thought it would be neat if there was some way to
use the same functions that are used in the update query in the design
of the table so the new variables would be created automatically from
the original date/time text string. I'm all ears if you got
suggestions.

Again, thanks for taking YOUR time to help!

Mike

fredg wrote:

>>On 28 Sep 2006 18:57:25 -0700, Takeadoe wrote:

>>>Dear NG,

Can someone assist me with writing the little code that is needed to
run an update table query each time the database is opened? From what
I've been able to glean from this group, the Autoexec Macro looks like
the way to go. Could someone please assist?

Thank you very much!

Mike

Each time the database is opened?
Create a macro
Macro Action OpenQuery (Your Update query Name)

Name this macro AutoExec

Perhaps you meant, more logically, once a day when the database is
opened.
Add a table to your database.
Table Name "tblUpdated"
FieldName "DateUpdated" indexed, No Duplicates)

Create a macro to first check the table to see if the Update has
already been done.

Macro Condition
DCount("*","tblUpdated","[DateUpdated] # = " & Date() & "#") =0

If date has already been added, cancel the macro.
Macro Action StopMacro

If not, run the update query.
Macro Action OpenQuery (Update query name)

Then run an append query to add the current date to the table.

Macro Action OpenQuery (Append query name which adds the current date
to the table.)

Name the macro AutoExec

If you wish to avoid the Query "You are about to Update ...etc...."
warnings, place a SetWarnings False macro just before the first
OpenQuery, and a SetWarnings True macro at after the last OpenQuery.

--
Fred
Please respond only to this newsgroup.
I do not reply to personal e-mail

[Takeadoe]

Salad - I will get back to you on this early next week (perhaps
today!). If you really want to get into this, I would be happy to
enlist your help, but I've traveled part way down this dusty road once
and I was unsuccessful. However, I didn't have an offer to help from
experts like yourself. I'll fill you in on the details as soon as I
get a chance.

salad wrote:

Takeadoe wrote:

Fred - Thanks so much for taking the time to respond. My reason for
wanting to run the update (once daily is probably sufficient as you
suggested) is to work around another problem that I've given up on
trying to conquer. Data captured at a scanner are automatically
appended to a database table. Problem is, I cannot for the life of me
figure out how to get Access to recognize a date/time value as a
date/time value. If I set the data type as date, the program crashes.

Are you saying there'd be no need to run the query if you could get the
program to recognize the date?

How is the data coming into the app? What's the format string? What
does it look like? Give us an example of a date that crashes in a date
field?

Is there an error message?

Look at DateValue, DateSerial, and CDate in help to see if there is a
way to update the date when appended.

How is data "automatically appended" from a scanner? Does the scanner
recognize Access tables and mdbs? I should think there's some code that
appends the images.

I think if you provided some example code that occurs when appending
images and where it bombs or the process that you are currently using
you'll get some help here to overcome the crash.

So, once in Access, I have to create 2 new variables of the date/time
format and parse the respective pieces from this text string. There
will be numerous occasions for folks to open this database while data
is being added. I don't want them to have to run the update query each
time they want to look at the data. When the table is complete, I will
likely run a make-table query and make the new variables permanent. As
you probably gathered, I know very little about Access. But, I'm
anxious to learn. I thought it would be neat if there was some way to
use the same functions that are used in the update query in the design
of the table so the new variables would be created automatically from
the original date/time text string. I'm all ears if you got
suggestions.

Again, thanks for taking YOUR time to help!

Mike

fredg wrote:

>On 28 Sep 2006 18:57:25 -0700, Takeadoe wrote:
Dear NG,

Can someone assist me with writing the little code that is needed to
run an update table query each time the database is opened? From what
I've been able to glean from this group, the Autoexec Macro looks like
the way to go. Could someone please assist?

Thank you very much!

Mike

Each time the database is opened?
Create a macro
Macro Action OpenQuery (Your Update query Name)

Name this macro AutoExec

Perhaps you meant, more logically, once a day when the database is
opened.
Add a table to your database.
Table Name "tblUpdated"
FieldName "DateUpdated" indexed, No Duplicates)

Create a macro to first check the table to see if the Update has
already been done.

Macro Condition
DCount("*","tblUpdated","[DateUpdated] # = " & Date() & "#") =0

If date has already been added, cancel the macro.
Macro Action StopMacro

If not, run the update query.
Macro Action OpenQuery (Update query name)

Then run an append query to add the current date to the table.

Macro Action OpenQuery (Append query name which adds the current date
to the table.)

Name the macro AutoExec

If you wish to avoid the Query "You are about to Update ...etc...."
warnings, place a SetWarnings False macro just before the first
OpenQuery, and a SetWarnings True macro at after the last OpenQuery.

--
Fred
Please respond only to this newsgroup.
I do not reply to personal e-mail

[salad]

Takeadoe wrote:

Salad - I will get back to you on this early next week (perhaps
today!). If you really want to get into this, I would be happy to
enlist your help, but I've traveled part way down this dusty road once
and I was unsuccessful. However, I didn't have an offer to help from
experts like yourself. I'll fill you in on the details as soon as I
get a chance.

OK

I might recommend you start a new thread. People don't go back to far
checking old messages.

[Takeadoe]

Fair enough!
salad wrote:

Takeadoe wrote:

Salad - I will get back to you on this early next week (perhaps
today!). If you really want to get into this, I would be happy to
enlist your help, but I've traveled part way down this dusty road once
and I was unsuccessful. However, I didn't have an offer to help from
experts like yourself. I'll fill you in on the details as soon as I
get a chance.

OK

I might recommend you start a new thread. People don't go back to far
checking old messages.