Snag in module 1

[Kurt6905]

Any input is welcome. The situation is users were allowed to put the amount of pesticide applied (Active) along with the 1-3 crops it was applied to (Crop_Code1,2,3) in a single record. I wanted to divide the Active value by 2 if used on 2 crops, and divide the Active by 3 if 3 crops, and eventually create separate records using a Union command.

Running the following code, I first a box asking for a Parameter Value for CropActive1, then an error message saying "Field not updatable." Can anyone help me get beyond the error messages? Much thanks in advance.

Function DoCropConversion()
Const table As String = "[MainTable]"
'make sure CropActive columns are created before running

DoCmd.RunSQL "update " & table & " set [CropActive1] = [Active] where [Crop_Code2]=0"

DoCmd.RunSQL "update " & table & " set [CropActive1] = [Active]* 0.5 where [Crop_Code2]>0 and [Crop_Code3]=0"

DoCmd.RunSQL "update " & table & " set [CropActive2] = [Active]* 0.5 where [Crop_Code2]>0 and [Crop_Code3]=0"

DoCmd.RunSQL "update " & table & " set [CropActive1] = [Active]* 0.333 where [Crop_Code3]>0"

DoCmd.RunSQL "update " & table & " set [CropActive2] = [Active]* 0.333 where [Crop_Code3]>0"

DoCmd.RunSQL "update " & table & " set [CropActive3] = [Active]* 0.333 where [Crop_Code3]>0"

End Function

 

[jrbarnett]

Is CropActive1 a valid fieldname in MainTable?

Does

SELECT *
FROM MainTable
Where Crop_Code = 0

show any valid data?

John

 

[Kurt6905]

John,
Thanks for taking a look. Yes, CropActive1 is a valid fieldname.

And there is no Crop_Code. Only Crop_Code1, Crop_Code2 and Crop_Code3 are valid fields.

 

[jrbarnett]

What about Where Crop_Code1 = 0 instead?

Basically I'm looking to find out if there are records that match your criteria.

My first observation is that your data itself is not in a normalised structure. This basically means that the data are stored in a means that is sub optimal, which can lead to update anomalies if you aren't careful with your data handling.

A better table structure in my opinion would be:

CropActiveNumber, CropActive, CropCodeNumber

plus any other fields in that table.

Basically you want to structure the data in such a way that you don't need to add new fields as you increase the amount of data over time.

John

 

[Kurt6905]

John,

Thanks for taking a look. I found the big problem, namely the database I'm working with isn't called MainTable. Once I fixed that, it seems SQL doesn't like numbers or hyphens in the field names, so I fixed that, too.

You're right about the table structure.

Thanks.