Argument Not Optional Error

[cb9002]

Hi

I'm using Access 2000 and DAO.

I have a form with fields that include "Date" and six categories listed as the array "DefectType". Each category has an associated text box with the word "Box" suffixed to the category name.

On submission, I wish to check the contents of the text box for each field, and if it doesn't contain the word satisfactory, write the contents of the text box to a new record in the table "Defects".

Running the query below creates an "Argument not optional" compile error at DoCmd.RunSQL. Any help appreciated.

Thanks


For Check2 = 0 To 5
If Me(DefectType(Check2)) <> "Satisfactory" Then
DoCmd.RunSQL 'INSERT INTO Defects (Reported, Type, Description) VALUES ("'& Me[Date] &'", "'& Me(DefectType(Check2)) &'", "'& Me(DefectType(Check2))' & 'Box'") '
End If
Next

 

[MajP]

always do this with a sql command in code so you can see what you are doing wrong.

dim strSql as string
some code
strSql = 'INSERT INTO Defects (Reported, Type, Description) VALUES ("'& Me[Date] &'", "'& Me(DefectType(Check2)) &'", "'& Me(DefectType(Check2))' & 'Box'") '

debug.print strSql
'see your errors when debugging
docmd.runSql strSql

Looks to me like you have several errors, but know you will be able to see them.

 

[MajP]

Also "Date" is a reserved word in VBA, may be part of your error.

 

[PHV]

Perhaps this ?

DoCmd.RunSQL "INSERT INTO Defects (Reported,Type,Description) VALUES ('" & Date & "','"& Me(DefectType(Check2)) & "','" & Me(DefectType(Check2)) & "Box')"

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[cb9002]

tbh all these quotes are confusing the hell outta me! Thanks for your advice so far.

I will change the Date field to something else

MajP, why is it better (apart from clarity) to phrase SQL statements as variables? I don't understand what you mean, thanks.

 

[cb9002]

Thanks for your help so far, here's my revised code.

DefectType(0) = Surface
...

    For Check2 = 0 To 5
        If Me(DefectType(Check2)) <> "-1" Then
            NewDefect = "INSERT INTO Defects (Reported, Type, Description)"
            NewDefect = NewDefect & "VALUES ('" & ReportDate & "' , '" & DefectType(Check2) & "' , '" & DefectType(Check2) & "Box')"
            DoCmd.RunSQL NewDefect
        End If
    Next

Its mostly working now.

In the middle field, Type, it correctly inserts the string, i.e. "Surface".

However in the last field, it inserts the string "SurfaceBox", not the contents of the memo box "SurfaceBox". How can I get it to inset the contents of the box?

I'm very grateful for your help so far.

 

[cb9002]

I've tried various combinations including:

Me!SurfaceBox
Me!SurfaceBox.Value
Me(SurfaceBox)

 

[PHV]

NewDefect = NewDefect & "VALUES ('" & ReportDate & "','" & DefectType(Check2) & "','" & Me(DefectType(Check2) & "Box") & "')"

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[MajP]

MajP, why is it better (apart from clarity) to phrase SQL statements as variables?

You answer your own question

all these quotes are confusing the hell outta me

You are able to see your sql string as it comes together before you try to run it. You can even drop that string into a query to see if you got it correct. Then you can see what you are doing wrong. If you what have done what I suggested you would have clearly seen your mistake that PHV is pointing out to you. Right now you have no idea what your string look like. Got it?

 

[cb9002]

Thank you both.

MajP, I did do what you said - in my second post below the code above is called as a string.

I see what you mean about a query, so if the SQL is not working in the code I can put it directly into a query and debug that way - that makes perfect sense now!