If Then Do Nothing?

[thr33xx]

Still learning VBA, so please bear with me. I have an If Then Statement which if the criteria is true, it executes, otherwise it should do nothing. When I execute the code (click the add record button), the following error results when the value is 2 or Null or blank,

Run-time error '-2147217908(80040e0c)':
Command text has not been set for the command object.​

Now when I debug, the following code is highlighted,

'Modifys last month to accept remainder value of total target enrollment if not divisible by whole numbers
If add_cbo_calculate = 1 Then
        sql5 = "Update dbo_Monthly_Expectations "
        sql5 = sql5 & "Set Exp_Recruitment= " & (-Int(-(Numtoenroll) Mod (Monthstoadd)) + (-Int(-(Numtoenroll) \ (Monthstoadd))))
        sql5 = sql5 & " Where Reporting_Month= " & DMax("Reporting_Month", "dbo_monthly_expectations", "study_id='" & Me.Add_Study_ID & "'")
    End If
    
    [COLOR="Red"][B]cn.Execute sql5[/B][/COLOR]

I have tried using an ELSE statement where add_cbo_calculate = 2 Or Isnull Or "". But something keeps telling me that is not necessary, as if the criteria add_cbo_calculate does not equal one, it should not execute.

Any help for a VBA beginner is greatly appreciated!

Thanks

 

[boblarson]

'Modifys last month to accept remainder value of total target enrollment if not divisible by whole numbers
If add_cbo_calculate = 1 Then
        sql5 = "Update dbo_Monthly_Expectations "
        sql5 = sql5 & "Set Exp_Recruitment= " & (-Int(-(Numtoenroll) Mod (Monthstoadd)) + (-Int(-(Numtoenroll) \ (Monthstoadd))))
        sql5 = sql5 & " Where Reporting_Month= " & DMax("Reporting_Month", "dbo_monthly_expectations", "study_id='" & Me.Add_Study_ID & "'")
[B][COLOR=red]cn.Execute sql5
[/COLOR][COLOR=red]End If[/COLOR][/B]

 

[thr33xx]

Thank you, AGAIN! Bob!

 

[PNGBill]

You are correct that If add_cbo_calculate is not 1 then the code will skip this part.

Most versions of access no longer require the SQL String to be built up like in the old days.
You just have a long string and to shorten the line length add " & _ at the end of the line and " at the beginning of the new line with a space either before the " in the top line or after the " in the next line.

I always check my sql's to ensure they work before converting them to be vba string. Have you tried this ?

 

[boblarson]

Most versions of access no longer require the SQL String to be built up like in the old days.

A lot of times it is just personal preference.