Code to change data from certain column in table

[branston]

Hi!

I am trying to work out a bit of code which I can put on a button which will set every record in a column to 0 if certain criteria are met.

Example: The table has 24 columns - Named Month01 to Month24. I have made a continuous form with a button showing each of the month names. When I click on the button "Month01" I want every record to have the value in Month01 to change to 0... ALMOST!
There is an added twist in that I have 3 different "Parts" to my database. The form with the buttons is already filtered for "Part1", say, so I want every Part1 record have the value in Month01 change to 0.

Is this possible?
I tried being clever with sql, but it didn't work:

CurrentDb.Execute "Update TblMonth Set TblMonth." & Me.MonthNumber & " = 0 Where TblMonth.Part = " & DLookup("[PartNumber]", "[TblPartCurrent]")

Am I on the right track or miles off?!
Thanks!

 

[vbaInet]

Is there no way to get the PartNumber without ysing a DLookup()? If this is a subform then maybe the parent form has the current PartNumber. Your DLookup() will return the first Partnumber it finds.

That said, you're on the right part. Finally you need to requery the form.

 

[branston]

The table the dlookup is looking at only ever has 1 record, so that is not a problem.

I now have:
CurrentDb.Execute "Update TblMpp Set TblMpp.'" & Me.MonthNumber & "' = 0 Where TblMpp.Phase = '" & DLookup("[PhaseNumber]", "[TblPhaseCurrent]") & "'"

but its saying I have "too few parameters. Expected 1."
Any ideas how to fix that?!

 

[vbaInet]

Is Phase a Number or Text datatype?

 

[branston]

It's Text datatype

 

[vbaInet]

What does Me.MonthNumber return? A number like 1 or the actual field name like Month1?

 

[branston]

The second options - it returns text such as Month01

 

[vbaInet]

Alrighty, here you go:

CurrentDb.Execute "Update TblMpp Set TblMpp." & Me.MonthNumber & " = 0 Where TblMpp.Phase = '" & Nz(DLookup("[PhaseNumber]", "[TblPhaseCurrent]"), vbNullString) & "';"

 

[branston]

Oh, ace! Thats brilliant, thank you!

 

[vbaInet]

The main problem was that you were enclosing Me.MonthNumber in single quotes.

Glad that worked for you.

 

[branston]

Ah right, I thought they were needed because of MonthNumber being text... does it not need them because its calling part of a table rather than being a criteria?

 

[vbaInet]

It's the criteria that needs it. If the field name doesn't contain single quotes (which of course it shouldn't), then you don't need it when building the sql in code.

 

[branston]

Ah right. I shall remember that!
Thanks again!

 

[vbaInet]

No problemo!