Set a Field to Zero

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

This is an Access 2003 question relating to a Main Form that has a Sub Form
(which contains all of the data entry information)

On the sub form I have a field I want to reset to zero at the start of each
month for all of the records. This is the only field that changes each month
so I want the user to be able to set all the records to zero before she
starts input. Rather than zapping the database clean I only want this one
field to be re-set to zero before she begins the new month's data input.

The rest of the fields for the record remain the same each month, only the
dollar field will change, which is why.

I tried this myself using a command button with simplistic code. keep in
mind that my knowledge of programming and etc is very limited when responding.

Thus, what I did was I put a button on the form using the onclick event
telling it to set that particular field to zero. Unfortunately, all that
happens when you click my button is the record you are physically sitting on
will set to zero.

Is there some code I can type for a command button so the user can click
this and set ALL records to zero at the same time?

If it's a query that does it, i do not understand how it would work as I
tried to do an update query but couldn't figure out how to make it work.
 
Is there some code I can type for a command button so the user
can click
this and set ALL records to zero at the same time?
Click to expand...

private sub cmdNewMonth_Click()

dim strSQL as string ' you need a command
dim db as DAO.Database ' and something to send it to

' this is a DAO version: the ADO is not a lot different

' this is a basic update command: you may need to change
' the criterion
strSQL = "UPDATE MyTable " & _
"SET DollarAmount = 0 " & _
"WHERE TRUE "

' set up database access
set db = CurrentDB()

' and do the command
On Error Resume Next
db.Execute strSQL, dbFailOnError

' check out result
If err.Number <> 0 Then
' oo-errr, there's not much that could have gone wrong
Msgbox Err.Number & ": " & Err.Description

Else
' okay, do nothing else

End If

' All done
End Sub



.... or something like that. As you can tell, this is untested but should
be pretty close.


All the best


Tim F
 
Back
Top