time limit on empty fields

[jomara via AccessMonster.com]

first i would like to say thanks to everyone that has helped me with past
posts. I have a customer database and 10 salespeople I am having a problem
with salespeople not filling in all fields on the form. I know i could make
them required fields but about a third of the fields wouldnt be filled out
till around the third call. Is there a way to either put a time limit say
30 days on a field or if that contact has been opened 5 times all fields
become required. If the fields are not filled out i would like the contact to
be removed from that salespersons database.

 

[Allen Browne]

There's quite a bit you're asking to achieve in this questions, so you will
need some experience with VBA and queries (SQL statements) to achieve it.

To know how long since the record was created, you will need to add a
date/time field to your table, and set its Default Value to:
=Date()
You can then create a query to identify the records where this field is:
< Date() - 30
and the other fields are Null.

To know when the contact has been opened 5 times, you would need to track
each time the record is visited. This would involve creating a logging
table, and using the Current event of the form to record the primary key
value of the record each time it is viewed.

To remove the records that are more than 30 days old and have fields that
are Null, execute a Delete query. You can create a query in design view, and
change it to a Delete query (Delete on Query menu.)

To automatically delete these records, you would need to run this query when
the database opens. You could use the AutoExec macro, or the Open event of
your start up form.

(Naturally, you need to be cautious about deleting stuff automatically.)

 

[jomara via AccessMonster.com]

Thanks for the help My only question on the record created part would be the
1000 records that were created before i add that field to the table will this
corrept them in any way

There's quite a bit you're asking to achieve in this questions, so you will
need some experience with VBA and queries (SQL statements) to achieve it.

To know how long since the record was created, you will need to add a
date/time field to your table, and set its Default Value to:
=Date()
You can then create a query to identify the records where this field is:
< Date() - 30
and the other fields are Null.

To know when the contact has been opened 5 times, you would need to track
each time the record is visited. This would involve creating a logging
table, and using the Current event of the form to record the primary key
value of the record each time it is viewed.

To remove the records that are more than 30 days old and have fields that
are Null, execute a Delete query. You can create a query in design view, and
change it to a Delete query (Delete on Query menu.)

To automatically delete these records, you would need to run this query when
the database opens. You could use the AutoExec macro, or the Open event of
your start up form.

(Naturally, you need to be cautious about deleting stuff automatically.)

first i would like to say thanks to everyone that has helped me with past
posts. I have a customer database and 10 salespeople I am having a

[quoted text clipped - 8 lines]

to
be removed from that salespersons database.

 

[Allen Browne]

It won't attempt to correct them if you leave the new field blank for the
existing records.

If you want them corrected also, use an Update query to put an older value
into the new field (e.g. 1/1/2006.)

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Thanks for the help My only question on the record created part would be
the
1000 records that were created before i add that field to the table will
this
corrept them in any way

There's quite a bit you're asking to achieve in this questions, so you
will
need some experience with VBA and queries (SQL statements) to achieve it.

To know how long since the record was created, you will need to add a
date/time field to your table, and set its Default Value to:
=Date()
You can then create a query to identify the records where this field is:
< Date() - 30
and the other fields are Null.

To know when the contact has been opened 5 times, you would need to track
each time the record is visited. This would involve creating a logging
table, and using the Current event of the form to record the primary key
value of the record each time it is viewed.

To remove the records that are more than 30 days old and have fields that
are Null, execute a Delete query. You can create a query in design view,
and
change it to a Delete query (Delete on Query menu.)

To automatically delete these records, you would need to run this query
when
the database opens. You could use the AutoExec macro, or the Open event of
your start up form.

(Naturally, you need to be cautious about deleting stuff automatically.)

first i would like to say thanks to everyone that has helped me with
past
posts. I have a customer database and 10 salespeople I am having a

[quoted text clipped - 8 lines]

to
be removed from that salespersons database.