Need code to make a Timer based on date- please help

[Mike]

Hi,

I was wondering if I can have a message created, based on
a date or any other criteria in access application? I
need to have a popup msgbox based on the date of the last
service on a peice of equipment on one form and also be
able to update certain feilds in other forms based on
that. Please help if this is a possibility.

Thanks much.

Mike

 

[Larry Linson]

I was wondering if I can have a
message created, based on a date
or any other criteria in access appli-
cation?

Access can create a text message and pop up a MsgBox to display it to the
user, as described in this excerpt from Access 97 Help:

MsgBox(prompt[, buttons] [, title] [, helpfile, context])

The MsgBox function syntax has these named arguments:

Part Description
prompt -- Required. String expression displayed as the message in the dialog
box. The maximum length of prompt is approximately 1024 characters,
depending on the width of the characters used. If prompt consists of more
than one line, you can separate the lines using a carriage return character
(Chr(13)), a linefeed character (Chr(10)), or carriage return - linefeed
character combination (Chr(13) & Chr(10)) between each line.

buttons -- Optional. Numeric expression that is the sum of values specifying
the number and type of buttons to display, the icon style to use, the
identity of the default button, and the modality of the message box. If
omitted, the default value for buttons is 0.

title -- Optional. String expression displayed in the title bar of the
dialog box. If you omit title, the application name is placed in the title
bar.

helpfile -- Optional. String expression that identifies the Help file to use
to provide context-sensitive Help for the dialog box. If helpfile is
provided, context must also be provided.

context -- Optional. Numeric expression that is the Help context number
assigned to the appropriate Help topic by the Help author. If context is
provided, helpfile must also be provided.

need to have a popup msgbox based
on the date of the last service on a peice
of equipment on one form and also be
able to update certain feilds in other
forms based on that. Please help if this
is a possibility.

Certainly it is a _possibility_. Unfortunately, your description could use
some clarification. For example:

Do you want to inform the user when they show that particular piece of
equipment in the form, or is there some other criteria? Do you want to
"update controls (not fields) in other forms", or do you want to "update
fields in particular tables that aren't the RecordSource of this form"?

If the first is true, then you can put code in the OnCurrent event to
compare the date in the record (assuming the service date is in the record
that is in the table or query that is the RecordSource for the form), create
the message, update the other tables, and pop up the MsgBox.

Now, do you expect a response from the user when the MsgBox pops up other
than just cliking OK? Would showing the same message on the form itself be
just as good (in which case, the user wouldn't have to click to acknowledge,
but if you pop up the MsgBox, it's sure to get the user's attention because
they'll have to acknowledge in order to proceed)?

If I didn't guess correctly what you want to do, please clarify.

Larry Linson
Microsoft Access MVP

 

[Mike]

Larry,

Thank you for your prompt and thoughtfull response. To
clarify, I'm creating this ticketing system where, by the
mean of a form and then a report, shop creates a ticket
for a peice of equipment that needs to be serviced. I
have been asked to provide ways so that when a ticket has
created, the application brows through the records in a
table and bring up all the equipment that may need to be
serviced as well. Currently, I have a table, a from (to
enter data into the table), and a query, which is used to
enter data with regard to Trucks, Compressors,
generators, etc. Truck, a unique identifying Numbner,
Serial Number (VIN in case of Truck), Service date, Last
MIleage in which the equipment was serviced, actual
Mileage. So , I guess I need a routin that looks into the
records and bring up all equipment which possibly need to
be serviced based on either date of last service, Last
service Mileage, or some other criteria and then update
either controls on another form (by bringing up that
form) or feilds on another table. I do have some
scattered ideas but being not Eloquent in writting VB
codes, I need help. Help in writing the codes and also,
guide on how to appreach this idea.

Thank you in advance.

Mike,

-----Original Message-----

I was wondering if I can have a
message created, based on a date
or any other criteria in access appli-
cation?

Access can create a text message and pop up a MsgBox to display it to the
user, as described in this excerpt from Access 97 Help:

MsgBox(prompt[, buttons] [, title] [, helpfile, context])

The MsgBox function syntax has these named arguments:

Part Description
prompt -- Required. String expression displayed as the message in the dialog
box. The maximum length of prompt is approximately 1024 characters,
depending on the width of the characters used. If prompt consists of more
than one line, you can separate the lines using a carriage return character
(Chr(13)), a linefeed character (Chr(10)), or carriage return - linefeed
character combination (Chr(13) & Chr(10)) between each line.

buttons -- Optional. Numeric expression that is the sum of values specifying
the number and type of buttons to display, the icon style to use, the
identity of the default button, and the modality of the message box. If
omitted, the default value for buttons is 0.

title -- Optional. String expression displayed in the title bar of the
dialog box. If you omit title, the application name is placed in the title
bar.

helpfile -- Optional. String expression that identifies the Help file to use
to provide context-sensitive Help for the dialog box. If helpfile is
provided, context must also be provided.

context -- Optional. Numeric expression that is the Help context number
assigned to the appropriate Help topic by the Help author. If context is
provided, helpfile must also be provided.

need to have a popup msgbox based
on the date of the last service on a peice
of equipment on one form and also be
able to update certain feilds in other
forms based on that. Please help if this
is a possibility.

Certainly it is a _possibility_. Unfortunately, your description could use
some clarification. For example:

Do you want to inform the user when they show that particular piece of
equipment in the form, or is there some other criteria? Do you want to
"update controls (not fields) in other forms", or do you want to "update
fields in particular tables that aren't the RecordSource of this form"?

If the first is true, then you can put code in the OnCurrent event to
compare the date in the record (assuming the service date is in the record
that is in the table or query that is the RecordSource for the form), create
the message, update the other tables, and pop up the MsgBox.

Now, do you expect a response from the user when the MsgBox pops up other
than just cliking OK? Would showing the same message on the form itself be
just as good (in which case, the user wouldn't have to click to acknowledge,
but if you pop up the MsgBox, it's sure to get the user's attention because
they'll have to acknowledge in order to proceed)?

If I didn't guess correctly what you want to do, please clarify.

Larry Linson
Microsoft Access MVP




.

 

[Larry Linson]

Actually, you can probably do what you want with one or two Queries and one
Report based on one of those Queries, that will actually print the Tickets,
with very little VB code needed. And, some of the VB code that you do need
will be created for you by the Command Button Wizard.

The following discussion assumes that (1) the date of last service is in the
record, and (2) there are no other criteria (like location -- "I want
tickets for all the equipment at the Dallas Terminal" would not prevent
doing what you want, but might complicate it a little). You can create a
Query that retrieves all the information needed to print a Ticket for each
piece of equipment by using the Date() function in an expression in the
Criteria line under the date of last service field, to choose all equipment
whose date of last service is the requisite number of days earlier.

I'd also guess that the time between service will vary depending on the
equipment, or, indeed, on the particular piece of equipment -- a dump truck
may need service on a different schedule than a pickup truck, so the service
interval might also need to be stored in the record and used in the
selection -- that would present no problem, either.

Then you simply use that Query as the Record Source of a Report that you
structure so each Ticket begins a new page. The VBA command to run a report
is: DoCmd.OpenReport, which is covered in Help (Help is better in some
versions of Access than in others, but DoCmd.OpenReport is well-covered in
all versions).

In fact, you don't have to do anything but open the database to have this
work -- you can specify either a Startup Form, and put the code in the Open
or Load event of that Form, or you can create an AutoExec macro (that will
be executed at startup unless you take action to prevent it).

Now, I'd complicate this a bit -- after I run the report, I'd run a query
that sets a Ticket Printed field in the Record for each of the selected
pieces of equipment, and that, too, would enter into selecting equipment for
the Report... I wouldn't include a piece of equipment if the Ticket had been
printed and less than a certain number of days had passed.

Of course, once a piece of equipment _is_ serviced, you need to have a form
so someone can update the date of last service (and, behind the scenes clear
the Ticket Printed field). You can't _assume_ that just because the ticket
was printed that the equipment was actually serviced.

If I haven't made myself clear, or if you have more clarification about what
you have and need, feel free to follow up.

Larry Linson
Microsoft Access MVP

Larry,

Thank you for your prompt and thoughtfull response. To
clarify, I'm creating this ticketing system where, by the
mean of a form and then a report, shop creates a ticket
for a peice of equipment that needs to be serviced. I
have been asked to provide ways so that when a ticket has
created, the application brows through the records in a
table and bring up all the equipment that may need to be
serviced as well. Currently, I have a table, a from (to
enter data into the table), and a query, which is used to
enter data with regard to Trucks, Compressors,
generators, etc. Truck, a unique identifying Numbner,
Serial Number (VIN in case of Truck), Service date, Last
MIleage in which the equipment was serviced, actual
Mileage. So , I guess I need a routin that looks into the
records and bring up all equipment which possibly need to
be serviced based on either date of last service, Last
service Mileage, or some other criteria and then update
either controls on another form (by bringing up that
form) or feilds on another table. I do have some
scattered ideas but being not Eloquent in writting VB
codes, I need help. Help in writing the codes and also,
guide on how to appreach this idea.

Thank you in advance.

Mike,

-----Original Message-----

I was wondering if I can have a
message created, based on a date
or any other criteria in access appli-
cation?

Access can create a text message and pop up a MsgBox to display it to the
user, as described in this excerpt from Access 97 Help:

MsgBox(prompt[, buttons] [, title] [, helpfile, context])

The MsgBox function syntax has these named arguments:

Part Description
prompt -- Required. String expression displayed as the message in the dialog
box. The maximum length of prompt is approximately 1024 characters,
depending on the width of the characters used. If prompt consists of more
than one line, you can separate the lines using a carriage return character
(Chr(13)), a linefeed character (Chr(10)), or carriage return - linefeed
character combination (Chr(13) & Chr(10)) between each line.

buttons -- Optional. Numeric expression that is the sum of values specifying
the number and type of buttons to display, the icon style to use, the
identity of the default button, and the modality of the message box. If
omitted, the default value for buttons is 0.

title -- Optional. String expression displayed in the title bar of the
dialog box. If you omit title, the application name is placed in the title
bar.

helpfile -- Optional. String expression that identifies the Help file to use
to provide context-sensitive Help for the dialog box. If helpfile is
provided, context must also be provided.

context -- Optional. Numeric expression that is the Help context number
assigned to the appropriate Help topic by the Help author. If context is
provided, helpfile must also be provided.

need to have a popup msgbox based
on the date of the last service on a peice
of equipment on one form and also be
able to update certain feilds in other
forms based on that. Please help if this
is a possibility.

Certainly it is a _possibility_. Unfortunately, your description could use
some clarification. For example:

Do you want to inform the user when they show that particular piece of
equipment in the form, or is there some other criteria? Do you want to
"update controls (not fields) in other forms", or do you want to "update
fields in particular tables that aren't the RecordSource of this form"?

If the first is true, then you can put code in the OnCurrent event to
compare the date in the record (assuming the service date is in the record
that is in the table or query that is the RecordSource for the form), create
the message, update the other tables, and pop up the MsgBox.

Now, do you expect a response from the user when the MsgBox pops up other
than just cliking OK? Would showing the same message on the form itself be
just as good (in which case, the user wouldn't have to click to acknowledge,
but if you pop up the MsgBox, it's sure to get the user's attention because
they'll have to acknowledge in order to proceed)?

If I didn't guess correctly what you want to do, please clarify.

Larry Linson
Microsoft Access MVP




.

 

[Mike]

Larry,

You have been more than helpful. I have so far done half
of what you suggested using queries, but needed to add
more automation into it and make it soem what a global
ticketing systems as far as many equipment are concern.
Right now I do have a form where user inouts the data and
the mileage at last service. I wanted to have include a
reference point for these equipments, such as 3000 miles
for Trucks and 250 hours for compressors and generators,
then based on the date of last service or the Mileage at
last service and the refrence points have a message popup
and upon clicking the OK button, a form or a report of
all those matches appear on the screen. At this point we
can start creting Tickets for those equipment that we
think need to be serviced. Can these points be done using
just quesries and reports based on queries or I need to
have codes?

Thank much,

Mike

-----Original Message-----
Actually, you can probably do what you want with one or two Queries and one
Report based on one of those Queries, that will actually print the Tickets,
with very little VB code needed. And, some of the VB code that you do need
will be created for you by the Command Button Wizard.

The following discussion assumes that (1) the date of last service is in the
record, and (2) there are no other criteria (like location -- "I want
tickets for all the equipment at the Dallas Terminal" would not prevent
doing what you want, but might complicate it a little). You can create a
Query that retrieves all the information needed to print a Ticket for each
piece of equipment by using the Date() function in an expression in the
Criteria line under the date of last service field, to choose all equipment
whose date of last service is the requisite number of days earlier.

I'd also guess that the time between service will vary depending on the
equipment, or, indeed, on the particular piece of equipment -- a dump truck
may need service on a different schedule than a pickup truck, so the service
interval might also need to be stored in the record and used in the
selection -- that would present no problem, either.

Then you simply use that Query as the Record Source of a Report that you
structure so each Ticket begins a new page. The VBA command to run a report
is: DoCmd.OpenReport, which is covered in Help (Help is better in some
versions of Access than in others, but DoCmd.OpenReport is well-covered in
all versions).

In fact, you don't have to do anything but open the database to have this
work -- you can specify either a Startup Form, and put the code in the Open
or Load event of that Form, or you can create an AutoExec macro (that will
be executed at startup unless you take action to prevent it).

Now, I'd complicate this a bit -- after I run the report, I'd run a query
that sets a Ticket Printed field in the Record for each of the selected
pieces of equipment, and that, too, would enter into selecting equipment for
the Report... I wouldn't include a piece of equipment if the Ticket had been
printed and less than a certain number of days had passed.

Of course, once a piece of equipment _is_ serviced, you need to have a form
so someone can update the date of last service (and, behind the scenes clear
the Ticket Printed field). You can't _assume_ that just because the ticket
was printed that the equipment was actually serviced.

If I haven't made myself clear, or if you have more clarification about what
you have and need, feel free to follow up.

Larry Linson
Microsoft Access MVP

Larry,

Thank you for your prompt and thoughtfull response. To
clarify, I'm creating this ticketing system where, by the
mean of a form and then a report, shop creates a ticket
for a peice of equipment that needs to be serviced. I
have been asked to provide ways so that when a ticket has
created, the application brows through the records in a
table and bring up all the equipment that may need to be
serviced as well. Currently, I have a table, a from (to
enter data into the table), and a query, which is used to
enter data with regard to Trucks, Compressors,
generators, etc. Truck, a unique identifying Numbner,
Serial Number (VIN in case of Truck), Service date, Last
MIleage in which the equipment was serviced, actual
Mileage. So , I guess I need a routin that looks into the
records and bring up all equipment which possibly need to
be serviced based on either date of last service, Last
service Mileage, or some other criteria and then update
either controls on another form (by bringing up that
form) or feilds on another table. I do have some
scattered ideas but being not Eloquent in writting VB
codes, I need help. Help in writing the codes and also,
guide on how to appreach this idea.

Thank you in advance.

Mike,

-----Original Message-----

I was wondering if I can have a
message created, based on a date
or any other criteria in access appli-
cation?

Access can create a text message and pop up a MsgBox

to
display it to the

user, as described in this excerpt from Access 97 Help:

MsgBox(prompt[, buttons] [, title] [, helpfile, context])

The MsgBox function syntax has these named arguments:

Part Description
prompt -- Required. String expression displayed as the message in the dialog
box. The maximum length of prompt is approximately

1024
characters,

depending on the width of the characters used. If

prompt
consists of more

than one line, you can separate the lines using a carriage return character
(Chr(13)), a linefeed character (Chr(10)), or carriage return - linefeed
character combination (Chr(13) & Chr(10)) between each line.

buttons -- Optional. Numeric expression that is the

sum
of values specifying

the number and type of buttons to display, the icon style to use, the
identity of the default button, and the modality of

the
message box. If

omitted, the default value for buttons is 0.

title -- Optional. String expression displayed in the title bar of the
dialog box. If you omit title, the application name is placed in the title
bar.

helpfile -- Optional. String expression that

identifies
the Help file to use

to provide context-sensitive Help for the dialog box.

If
helpfile is

provided, context must also be provided.

context -- Optional. Numeric expression that is the

Help
context number

assigned to the appropriate Help topic by the Help author. If context is
provided, helpfile must also be provided.

need to have a popup msgbox based
on the date of the last service on a peice
of equipment on one form and also be
able to update certain feilds in other
forms based on that. Please help if this
is a possibility.

Certainly it is a _possibility_. Unfortunately, your description could use
some clarification. For example:

Do you want to inform the user when they show that particular piece of
equipment in the form, or is there some other

criteria?
Do you want to

"update controls (not fields) in other forms", or do

you
want to "update

fields in particular tables that aren't the

RecordSource
of this form"?

If the first is true, then you can put code in the OnCurrent event to
compare the date in the record (assuming the service date is in the record
that is in the table or query that is the RecordSource for the form), create
the message, update the other tables, and pop up the MsgBox.

Now, do you expect a response from the user when the MsgBox pops up other
than just cliking OK? Would showing the same message

on
the form itself be

just as good (in which case, the user wouldn't have to click to acknowledge,
but if you pop up the MsgBox, it's sure to get the user's attention because
they'll have to acknowledge in order to proceed)?

If I didn't guess correctly what you want to do,

please
clarify.

Larry Linson
Microsoft Access MVP




.

.

 

[Mike]

Larry,

Also, I cannot understand what you mean by the Ticket
Printed Feild when a report was printed and a second
query to be run. Let me explain further:

1- I will have a form to enter record of every Trcuck and
Compressors, etc into a table.

2- I will have a form that will contain information on a
piece of equipment to be serviced and on this form a
click command button that says "Create Ticket" and it
creates a report which is based on a query - in fact this
is what I do have currently. what we need to do is to
have the command button to also look for any other piece
of equipment that may need to be serviced based on a
criteria, i.e. 3000 miles for Trucks and 250 hours for
Compressors, generators , etc.

3- i also have anothe table and a form that act as a
catalouge of filters, brand, number, cost and its
association to a particular equipment (TRuck,
Compressors, etc.), so i also need the ticket to look for
the machine, look for filters for that machine, cost,
etc. and include it in the report.

I think I am making things to be complicated but the only
things that I haven 't been able to do so far is the
number 3. How to have the report created to also lookup
the filters associated with machines that matches the
criteria and include it in the report.

Thank you in advance for you help and patient to bearing
with me on this.

Regards,

Mike

-----Original Message-----
Actually, you can probably do what you want with one or two Queries and one
Report based on one of those Queries, that will actually print the Tickets,
with very little VB code needed. And, some of the VB code that you do need
will be created for you by the Command Button Wizard.

The following discussion assumes that (1) the date of last service is in the
record, and (2) there are no other criteria (like location -- "I want
tickets for all the equipment at the Dallas Terminal" would not prevent
doing what you want, but might complicate it a little). You can create a
Query that retrieves all the information needed to print a Ticket for each
piece of equipment by using the Date() function in an expression in the
Criteria line under the date of last service field, to choose all equipment
whose date of last service is the requisite number of days earlier.

I'd also guess that the time between service will vary depending on the
equipment, or, indeed, on the particular piece of equipment -- a dump truck
may need service on a different schedule than a pickup truck, so the service
interval might also need to be stored in the record and used in the
selection -- that would present no problem, either.

Then you simply use that Query as the Record Source of a Report that you
structure so each Ticket begins a new page. The VBA command to run a report
is: DoCmd.OpenReport, which is covered in Help (Help is better in some
versions of Access than in others, but DoCmd.OpenReport is well-covered in
all versions).

In fact, you don't have to do anything but open the database to have this
work -- you can specify either a Startup Form, and put the code in the Open
or Load event of that Form, or you can create an AutoExec macro (that will
be executed at startup unless you take action to prevent it).

Now, I'd complicate this a bit -- after I run the report, I'd run a query
that sets a Ticket Printed field in the Record for each of the selected
pieces of equipment, and that, too, would enter into selecting equipment for
the Report... I wouldn't include a piece of equipment if the Ticket had been
printed and less than a certain number of days had passed.

Of course, once a piece of equipment _is_ serviced, you need to have a form
so someone can update the date of last service (and, behind the scenes clear
the Ticket Printed field). You can't _assume_ that just because the ticket
was printed that the equipment was actually serviced.

If I haven't made myself clear, or if you have more clarification about what
you have and need, feel free to follow up.

Larry Linson
Microsoft Access MVP

Larry,

Thank you for your prompt and thoughtfull response. To
clarify, I'm creating this ticketing system where, by the
mean of a form and then a report, shop creates a ticket
for a peice of equipment that needs to be serviced. I
have been asked to provide ways so that when a ticket has
created, the application brows through the records in a
table and bring up all the equipment that may need to be
serviced as well. Currently, I have a table, a from (to
enter data into the table), and a query, which is used to
enter data with regard to Trucks, Compressors,
generators, etc. Truck, a unique identifying Numbner,
Serial Number (VIN in case of Truck), Service date, Last
MIleage in which the equipment was serviced, actual
Mileage. So , I guess I need a routin that looks into the
records and bring up all equipment which possibly need to
be serviced based on either date of last service, Last
service Mileage, or some other criteria and then update
either controls on another form (by bringing up that
form) or feilds on another table. I do have some
scattered ideas but being not Eloquent in writting VB
codes, I need help. Help in writing the codes and also,
guide on how to appreach this idea.

Thank you in advance.

Mike,

-----Original Message-----

I was wondering if I can have a
message created, based on a date
or any other criteria in access appli-
cation?

Access can create a text message and pop up a MsgBox

to
display it to the

user, as described in this excerpt from Access 97 Help:

MsgBox(prompt[, buttons] [, title] [, helpfile, context])

The MsgBox function syntax has these named arguments:

Part Description
prompt -- Required. String expression displayed as the message in the dialog
box. The maximum length of prompt is approximately

1024
characters,

depending on the width of the characters used. If

prompt
consists of more

than one line, you can separate the lines using a carriage return character
(Chr(13)), a linefeed character (Chr(10)), or carriage return - linefeed
character combination (Chr(13) & Chr(10)) between each line.

buttons -- Optional. Numeric expression that is the

sum
of values specifying

the number and type of buttons to display, the icon style to use, the
identity of the default button, and the modality of

the
message box. If

omitted, the default value for buttons is 0.

title -- Optional. String expression displayed in the title bar of the
dialog box. If you omit title, the application name is placed in the title
bar.

helpfile -- Optional. String expression that

identifies
the Help file to use

to provide context-sensitive Help for the dialog box.

If
helpfile is

provided, context must also be provided.

context -- Optional. Numeric expression that is the

Help
context number

assigned to the appropriate Help topic by the Help author. If context is
provided, helpfile must also be provided.

need to have a popup msgbox based
on the date of the last service on a peice
of equipment on one form and also be
able to update certain feilds in other
forms based on that. Please help if this
is a possibility.

Certainly it is a _possibility_. Unfortunately, your description could use
some clarification. For example:

Do you want to inform the user when they show that particular piece of
equipment in the form, or is there some other

criteria?
Do you want to

"update controls (not fields) in other forms", or do

you
want to "update

fields in particular tables that aren't the

RecordSource
of this form"?

If the first is true, then you can put code in the OnCurrent event to
compare the date in the record (assuming the service date is in the record
that is in the table or query that is the RecordSource for the form), create
the message, update the other tables, and pop up the MsgBox.

Now, do you expect a response from the user when the MsgBox pops up other
than just cliking OK? Would showing the same message

on
the form itself be

just as good (in which case, the user wouldn't have to click to acknowledge,
but if you pop up the MsgBox, it's sure to get the user's attention because
they'll have to acknowledge in order to proceed)?

If I didn't guess correctly what you want to do,

please
clarify.

Larry Linson
Microsoft Access MVP




.

.