Zero-out a field

[pbrown2]

The form currently being used allows the user to update the records with the lasts' months totals. Then a report is ran based on if the field [Current Month] is >0. The problem is that currently the user must close the form and open the table and change all values in the [Current Month] to zero before they update with the new data.

Is there away to have a command on the form or even a different form that allows the user to click a button and all values in [Current Month] will automatically change to 0? This way the user simply clicks the button, all the values change to zero and they can go on to enter the updated data.





Thank you for any and all help,

PBrown

 

[genomon]

Dim strSQL as String
SET strSQL = "UPDATE tablename SET current_month = 0"
DoCmd.RunSQL

Create a command button on the form and put the above code in its click() event!

 

[pbrown2]

Tried:
Dim strSQL As String
Set strSQL = "UPDATE Service Parts and Volumes SET current_month = 0"
DoCmd.RunSQL

But the error
"Object required"
and goes directly to
set strsql


The table name is
Service Parts and Volumes


Thank you for any and all help,

PBrown

 

[RoyVidar]

Hi!

I'm not an expert on query syntax, but field names and tablenames containing spaces must be bracketed. Done some other alterations on the code, so perhaps something like this:

[tt]Dim strSQL As String
strSQL = "UPDATE [Service Parts and Volumes] SET [current month] = 0"
'docmd.setwarnings false
DoCmd.RunSQL strSQL
docmd.setwarnings true[/tt]

would work?

Check the spelling before you try;-)

The setwarning lines are nice when the thingie is working, because they suppress the "do you wanna change N records..." messages. I've commented out the "false line", so that you can test it without first. When it's working, remove the single quote.

HTH Roy-Vidar

 

[genomon]

OOPS!! Should be DoCmd.RunSQL strSQL - sorry!

If that wasn't it try this:
You will need to go to the VB code window (IDE) and select Tools|References from the drop-down menu and make sure that you put a check next to any that start with "missing".
Also make sure the following references are checked:
Visual Basic for Applications
Microsoft Access 9.0 Object Library
Microsoft DAO 3.6 Object Library
Microsoft Visual Basic for Applications Extesibility 5.3
Hope this helps!!

 

[pbrown2]

I found one item incorrect on my part. The table Name is MainTable

Services parts... is the form name.

However, below is what I currently have in the code and it is reading the same errors.

Dim strSQL As String
Set strSQL = "Update [Maintable] set [current month] = 0"
DoCmd.RunSQL strSQL


I have also checked the references and nothing is missing and all required is checked.

Thank you for any and all help,

PBrown

 

[danvlas]

Don't SET a string variable...Simply:

strSQL = "Update [Maintable] set [current month] = 0"

Good luck



Daniel Vlas
Systems Consultant

http://www.geocities.com/danvlas/AutoMail.html