Update the value in a field based on a condition

[Access_Help]

I have a calculated field in the form footer which adds up the number of boxes that have been ticked for the received field

=Sum(IIf([Recieved]=Yes,1,0))

If the ticks equal to 3 then I want to update the status field in another table to "Active".

I am trying this VBA code but it won't work.

If Text9 = "3" And custNumber = tblCustomers.custNumber Then
   tblCustomers.Status = "Active"

I'm not good with VBA so hope someone can help!

Thanks

 

[CJ_London]

if you are summing then the result is a number, not text so

If Text9 = "3" And ...

should be

If Text9 = 3 And ...

with regards the rest of your code, that depends on what your fields are actually called - you would not normally put a table name in front of a field name unless that fieldname is repeated in another table.

try removing tblCustomers.custNumber and replacing with me. which will bring up all the objects/properties in the form, so keep typing tthen b then l, my guess is nothing appropriate will appear. So backspace to the . and type in c followed by u followed by s - you should then see a list of those objects that start cus. I would expect one of these to be custnumber

 

[Access_Help]

if you are summing then the result is a number, not text so

If Text9 = "3" And ...

should be

If Text9 = 3 And ...

with regards the rest of your code, that depends on what your fields are actually called - you would not normally put a table name in front of a field name unless that fieldname is repeated in another table.

try removing tblCustomers.custNumber and replacing with me. which will bring up all the objects/properties in the form, so keep typing tthen b then l, my guess is nothing appropriate will appear. So backspace to the . and type in c followed by u followed by s - you should then see a list of those objects that start cus. I would expect one of these to be custnumber

Thanks, I replaced it with me but it returns an error. Attached is a screenshot of the error.

 

[smig]

you still have tblStudents.EnrolmentNumber there