clear fields

  • Thread starter Thread starter Robert Morris
  • Start date Start date
R

Robert Morris

Hi, I'm trying to clear certain fields only in a form so
that each month new info would need to be added without
having to delete all the info of each record. Suggestions?

Thanks

Robert
 
Hi,
Forms only display data from tables so you will have to delete the data in the
relevant table. I'm going to assume that the db is used on a daily basis, if it's not,
the date comparison will not work and you will need a more elaborate method
to see if a month has gone by since the fields were last cleared. The code below
will delete the data on the 1st of each month.

You can put this code in the form's Open event:

If DateSerial(Year(Date()), Month(Date()), 1) = Date() Then
Dim db As Database
Dim strSql As String

strSql = "Update yourTable Set [field1] = Null, [field2] = null"
db.Execute strSql, dbFailOnError
Set db = Nothing
End If

Substitute the correct names for yourTable and the fields.
 
Hi Dan,

Thanks for the tip, but will this clear select fields or
all fields of the table? Essentially what I need it to do
is clear just some of the fields after a specific date
say the 15th of each month, any help you can provide
would be greatly appreciated.

Thanking You again

Robert
-----Original Message-----
Hi,
Forms only display data from tables so you will have to delete the data in the
relevant table. I'm going to assume that the db is used on a daily basis, if it's not,
the date comparison will not work and you will need a more elaborate method
to see if a month has gone by since the fields were last cleared. The code below
will delete the data on the 1st of each month.

You can put this code in the form's Open event:

If DateSerial(Year(Date()), Month(Date()), 1) = Date() Then
Dim db As Database
Dim strSql As String

strSql = "Update yourTable Set [field1] = Null, [field2] = null"
db.Execute strSql, dbFailOnError
Set db = Nothing
End If

Substitute the correct names for yourTable and the fields.

--
HTH
Dan Artuso, Access MVP


Hi, I'm trying to clear certain fields only in a form so
that each month new info would need to be added without
having to delete all the info of each record. Suggestions?

Thanks

Robert
Click to expand...


.
Click to expand...
 
Hi Robert,
Sorry, the logic in my previous post won't work.
Yuo would have to have a date field in the new table.
The date value would indicate the date you last cleared the fields.

I don't have time right now to work out the logic with the dates to make
sure you clear the fields at the right time. I'll post back later.



--
HTH
Dan Artuso, Access MVP


Dan Artuso said:
Hi Robert,
It will clear whatever fields you put in the Update statement.
Again, is the database used on a daily basis?
If it's not, you will have to have some sort of indicator as to
whether the fields have been cleared for the specified interval.

The easiest would be to have a table with just one record, one field, that
contains a boolean value. So you'd end up with something like this:

If Date() > DateSerial(Year(Date()), Month(Date()), 15) _
And DLookup("[yourField]","yourNewTable") = True Then
Dim db As Database
Dim strSql As String

strSql = "Update yourTable Set [field1] = Null, [field2] = null"
db.Execute strSql, dbFailOnError
strSql = "Update yourNewTable Set yourField = 0"
db.Execute strSql, dbFailOnError
Set db = Nothing
End If


--
HTH
Dan Artuso, Access MVP


Robert Morris said:
Hi Dan,

Thanks for the tip, but will this clear select fields or
all fields of the table? Essentially what I need it to do
is clear just some of the fields after a specific date
say the 15th of each month, any help you can provide
would be greatly appreciated.

Thanking You again

Robert
-----Original Message-----
Hi,
Forms only display data from tables so you will have to delete the data in the
relevant table. I'm going to assume that the db is used on a daily basis, if it's not,
the date comparison will not work and you will need a more elaborate method
to see if a month has gone by since the fields were last cleared. The code below
will delete the data on the 1st of each month.

You can put this code in the form's Open event:

If DateSerial(Year(Date()), Month(Date()), 1) = Date() Then
Dim db As Database
Dim strSql As String

strSql = "Update yourTable Set [field1] = Null, [field2] = null"
db.Execute strSql, dbFailOnError
Set db = Nothing
End If

Substitute the correct names for yourTable and the fields.

--
HTH
Dan Artuso, Access MVP


Hi, I'm trying to clear certain fields only in a form so
that each month new info would need to be added without
having to delete all the info of each record. Suggestions?

Thanks

Robert


.
Click to expand...
Click to expand...
Click to expand...
 
Hi Dan,

Sorry! Yes the database is used on a daily basis, to
update and revise records.

Thanks again

Robert
-----Original Message-----
Hi Robert,
It will clear whatever fields you put in the Update statement.
Again, is the database used on a daily basis?
If it's not, you will have to have some sort of indicator as to
whether the fields have been cleared for the specified interval.

The easiest would be to have a table with just one record, one field, that
contains a boolean value. So you'd end up with something like this:

If Date() > DateSerial(Year(Date()), Month(Date()), 15) _
And DLookup("[yourField]","yourNewTable") = True Then
Dim db As Database
Dim strSql As String

strSql = "Update yourTable Set [field1] = Null, [field2] = null"
db.Execute strSql, dbFailOnError
strSql = "Update yourNewTable Set yourField = 0"
db.Execute strSql, dbFailOnError
Set db = Nothing
End If


--
HTH
Dan Artuso, Access MVP


Hi Dan,

Thanks for the tip, but will this clear select fields or
all fields of the table? Essentially what I need it to do
is clear just some of the fields after a specific date
say the 15th of each month, any help you can provide
would be greatly appreciated.

Thanking You again

Robert
-----Original Message-----
Hi,
Forms only display data from tables so you will have
Click to expand...
to
delete the data in the
relevant table. I'm going to assume that the db is
Click to expand...
used
on a daily basis, if it's not,
the date comparison will not work and you will need a more elaborate method
to see if a month has gone by since the fields were
Click to expand...
last
cleared. The code below
will delete the data on the 1st of each month.

You can put this code in the form's Open event:

If DateSerial(Year(Date()), Month(Date()), 1) = Date() Then
Dim db As Database
Dim strSql As String

strSql = "Update yourTable Set [field1] = Null, [field2] = null"
db.Execute strSql, dbFailOnError
Set db = Nothing
End If

Substitute the correct names for yourTable and the fields.

--
HTH
Dan Artuso, Access MVP


Hi, I'm trying to clear certain fields only in a
Click to expand...
form
so
that each month new info would need to be added without
having to delete all the info of each record. Suggestions?

Thanks

Robert


.
Click to expand...
Click to expand...


.
Click to expand...
 
Hi Dan,
Not a problem I certainly appreciate your patience with
me.

Robert
-----Original Message-----
Hi Robert,
Sorry, the logic in my previous post won't work.
Yuo would have to have a date field in the new table.
The date value would indicate the date you last cleared the fields.

I don't have time right now to work out the logic with the dates to make
sure you clear the fields at the right time. I'll post back later.



--
HTH
Dan Artuso, Access MVP


"Dan Artuso" <[email protected]> wrote in
Click to expand...
message news:u#[email protected]...
Hi Robert,
It will clear whatever fields you put in the Update statement.
Again, is the database used on a daily basis?
If it's not, you will have to have some sort of indicator as to
whether the fields have been cleared for the specified interval.

The easiest would be to have a table with just one record, one field, that
contains a boolean value. So you'd end up with something like this:

If Date() > DateSerial(Year(Date()), Month(Date()), 15) _
And DLookup("[yourField]","yourNewTable") = True Then
Dim db As Database
Dim strSql As String

strSql = "Update yourTable Set [field1] = Null, [field2] = null"
db.Execute strSql, dbFailOnError
strSql = "Update yourNewTable Set yourField = 0"
db.Execute strSql, dbFailOnError
Set db = Nothing
End If


--
HTH
Dan Artuso, Access MVP


Hi Dan,

Thanks for the tip, but will this clear select fields or
all fields of the table? Essentially what I need it to do
is clear just some of the fields after a specific date
say the 15th of each month, any help you can provide
would be greatly appreciated.

Thanking You again

Robert
-----Original Message-----
Hi,
Forms only display data from tables so you will have to
delete the data in the
relevant table. I'm going to assume that the db is used
on a daily basis, if it's not,
the date comparison will not work and you will need a
more elaborate method
to see if a month has gone by since the fields were last
cleared. The code below
will delete the data on the 1st of each month.

You can put this code in the form's Open event:

If DateSerial(Year(Date()), Month(Date()), 1) = Date ()
Then
Dim db As Database
Dim strSql As String

strSql = "Update yourTable Set [field1] = Null,
[field2] = null"
db.Execute strSql, dbFailOnError
Set db = Nothing
End If

Substitute the correct names for yourTable and the
fields.

--
HTH
Dan Artuso, Access MVP


Hi, I'm trying to clear certain fields only in a form
so
that each month new info would need to be added without
having to delete all the info of each record.
Suggestions?

Thanks

Robert


.
Click to expand...
Click to expand...


.
Click to expand...
 
Thanks for your help I will let you know if it works for
me or not once again thank you.

Robert
-----Original Message-----
Hi Robert,
That makes things simpler.
You will still need a table that holds a boolean value (yes/no) to indicate
whether the fields have been cleared.

The basic logic would be:
1) if the date is anything other than the 15th, set the yes/no field to
false
2) check to if it is the 15th and if the yes/no field is false. this is because I'm
assuming the database could be opened and closed numerous times in one day and
this code will run in the open event on whichever form you choose. Once the
fields have been cleared, set the yes/no flag to true so it won't run again
on the 15th of the month.

So it would look something like this:
**air code**

Dim db As Database
Dim strSql As String
Set db = CurrentDb()
If Date() <> DateSerial(Year(Date()), Month(Date()), 15) Then
strSql = "Update yourNewTable Set yesnoField = 0"
db.Execute strSql, dbFailOnError
Else
If DLookup("[yesnoField]","yourNewTable") = 0 Then
strSql = "Update yourTable Set [field1] = Null, [field2] = null"
db.Execute strSql, dbFailOnError
strSql = "Update yourNewTable Set yesnoField = - 1"
db.Execute strSql, dbFailOnError
End If
End If



--
HTH
Dan Artuso, Access MVP


Hi Dan,

Sorry! Yes the database is used on a daily basis, to
update and revise records.

Thanks again

Robert
-----Original Message-----
Hi Robert,
It will clear whatever fields you put in the Update statement.
Again, is the database used on a daily basis?
If it's not, you will have to have some sort of indicator as to
whether the fields have been cleared for the specified interval.

The easiest would be to have a table with just one record, one field, that
contains a boolean value. So you'd end up with
Click to expand...
something
like this:
If Date() > DateSerial(Year(Date()), Month(Date()),
Click to expand...
15)
_
And DLookup("[yourField]","yourNewTable") = True Then
Dim db As Database
Dim strSql As String

strSql = "Update yourTable Set [field1] = Null, [field2] = null"
db.Execute strSql, dbFailOnError
strSql = "Update yourNewTable Set yourField = 0"
db.Execute strSql, dbFailOnError
Set db = Nothing
End If


--
HTH
Dan Artuso, Access MVP


Hi Dan,

Thanks for the tip, but will this clear select
Click to expand...
fields
or
all fields of the table? Essentially what I need it
Click to expand...
to
do
is clear just some of the fields after a specific date
say the 15th of each month, any help you can provide
would be greatly appreciated.

Thanking You again

Robert
-----Original Message-----
Hi,
Forms only display data from tables so you will
Click to expand...
have
to
delete the data in the
relevant table. I'm going to assume that the db is used
on a daily basis, if it's not,
the date comparison will not work and you will need a
more elaborate method
to see if a month has gone by since the fields were last
cleared. The code below
will delete the data on the 1st of each month.

You can put this code in the form's Open event:

If DateSerial(Year(Date()), Month(Date()), 1) = Date()
Then
Dim db As Database
Dim strSql As String

strSql = "Update yourTable Set [field1] = Null,
[field2] = null"
db.Execute strSql, dbFailOnError
Set db = Nothing
End If

Substitute the correct names for yourTable and the
fields.

--
HTH
Dan Artuso, Access MVP


Hi, I'm trying to clear certain fields only in a form
so
that each month new info would need to be added without
having to delete all the info of each record.
Suggestions?

Thanks

Robert


.



.
Click to expand...
Click to expand...


.
Click to expand...
 
Back
Top