Calcualtion on a table field

[DC CS]

I have a Access table with fields named MM1, MM2, MM3...MM17.
I need to add 1 to the current value stored in the field, depending on the result of some calculations done on several variables.
The code I currently use, was proposed by ChatGPT, but it does not work. Code as follows:

        Case 9
            Me!Frm_TyreForecastSubForm.Form!("MM0_" & Me("MM" & t) - Val(MinMM)) = Me!Frm_TyreForecastSubForm.Form!("MM0_" & Me("MM" & t) - Val(MinMM)) + 1
            If t = 1 Then FieldName = "MM" & t
            CurrentDb.Execute "UPDATE TempMMtable SET " & FieldName & " = " & FieldName & " + 1 WHERE CLRegistration = '" & rs!CLRegistration & "';"

In the above scenario, the result is not important, but the field "MM1" must be changed by 1. The SQL statement should read as follows (just for clarity sake):


"UPDATE TempMMtable SET MM1 = MM1 +1;" - Because t was equal to 1, field MM1 needs to be updated. If t = 2, then field MM2 must be updated with 1.

Any suggestions?
Thanks

[Welshgasman]

Put your criteria/SQL into a string variable and debug.print that until you get it correct.
You can also post the output of the debug back here if you cannot see the error.

Basic debugging 101.

Also check your control values are what you think they are.
I would even do the calculation of the control name separately so I can check I have the correct name.

Not impressed with ChatGPT coding so far.

debug.print "MM0_" & Me("MM" & t) - Val(MinMM)) + 1

Also it appears your DB is not normalised. Having digits after fieldnames gives that away, especially that high a number.

[DC CS]

Thanks for the suggestions. I narrowed it down and am receiving the following error now:



The field is not auto filled and no restrictions on the field.

[davegri]

Here's a bare bones solution that updates the table based on value of t without knowing full context of problem. Maybe you can adapt it to fit.

DCDS-davegri-v01.zip

[Gicu]

What kind of table is it? Regular Access local table, linked Access, linked Excel, linked ODBC?
Can you post a screen shot of the table in design view showing the properties window for field MM1?
Cheers,

[Micron]

Look for one of these causes for the error message
http://allenbrowne.com/ser-61.html

[DC CS]

The table is an Access Linked table. Property sheet below:

[madpiet]

The table is an Access Linked table. Property sheet below:

What's MM[n] mean? Seems like this should be a child table, because this looks an awful lot like a repeating field.

[Welshgasman]

You do not have the field names correct?
You have MyMMn in the table and are using MMn in the code?

[CJ_London]

According to your code, if t<>1 then fieldname is not determined

If t = 1 Then FieldName = "MM" & t