Too few parameters error in update query (1 Viewer)

[MilaK]

Too few parameters error in function

Hello,

I've wrote a function that should accept two parametes and the third one is optional.

However, if I don't supply the third parameter "compare_res" when I call the function, I get "too few paremeters" error. Is there a way to avoid this error? I don't always need the third parameter.

There is also and issue with date formating inside the function that I can't figure out.

Public Function CompleteReview(run_name As String, user_role As Variant, Optional compare_res As Variant)

Dim project_name As String
Dim role As Variant
Dim comp_res As Variant

project_name = run_name
role = user_role
comp_res = compare_res

Dim todaysDate As Date
todaysDate = Date

'If MsgBox("Are you sure that you want to complete review?", vbQuestion + vbYesNo + vbDefaultButton2) = vbNo Then
       ' Exit Function
         'End If
         
Dim db As Database
Dim SQL1 As String
Dim SQL2 As String
Dim SQL3 As String
Dim SQL4 As String
Dim SQL5 As String
Dim SQL6 As String

Set db = CurrentDb

If role = "First_Rev" Then
 
        SQL1 = "UPDATE tbl_Projects SET tbl_Project.first_review = True"
        SQL1 = SQL1 & " WHERE tbl_Projects.run_name = '" & project_name & "'"
                       '#" & DateMin & "# #" & dataentry_fromdate & "#"
        'SQL2 = "UPDATE tbl_Projects SET tbl_Projects.first_review_completed_date = #" & todaysDate & "#"
        'SQL2 = SQL2 & " WHERE tbl_Projects.run_name = '" & project_name & "'"
        'Debug.Print SQL2
                          
        On Error GoTo Proc_Err
        db.Execute SQL1, dbFailOnError
        'db.Execute SQL2, dbFailOnError
              
End If

If role = "Second_Rev" And comp_res = 0 Then

        SQL3 = "UPDATE tbl_Projects SET tbl_Project.second_review = True"
        SQL3 = SQL3 & " WHERE tbl_Projects.run_name = '" & project_name & "'"
                       
        SQL4 = "UPDATE tbl_Projects SET tbl_Projects.second_review_completed_date = #" & todaysDate & "#"
        SQL4 = SQL4 & " WHERE tbl_Projects.run_name = '" & project_name & "'"
        Debug.Print SQL4
        
                   On Error GoTo Proc_Err
                   db.Execute SQL3, dbFailOnError
                   db.Execute SQL4, dbFailOnError
                
 End If
 
 If role = "Second_Rev" And comp_res = -1 Then

        SQL5 = "UPDATE tbl_Projects SET tbl_Project.approval = True"
        SQL5 = SQL5 & " WHERE tbl_Projects.run_name = '" & project_name & "'"
                       
        SQL6 = "UPDATE tbl_Projects SET tbl_Projects.approval_date = #" & todaysDate & "#"
        SQL6 = SQL6 & " WHERE tbl_Projects.run_name = '" & project_name & "'"
        Debug.Print SQL6
        
                   On Error GoTo Proc_Err
                   db.Execute SQL5, dbFailOnError
                   db.Execute SQL6, dbFailOnError
                
 End If
Proc_Exit:
  
Set db = Nothing
  Exit Function

Proc_Err:
  
MsgBox "Error updating: " & Err.Description
Resume Proc_Exit
        
        
End Function

Here is the vba sql statement I'm using:

Dim todaysDate As Date
todaysDate = Date

SQL2 = "UPDATE tbl_Projects SET tbl_Projects.first_review_completed_date = #" & todaysDate & "#"
        SQL2 = SQL2 & " WHERE tbl_Projects.run_name = '" & project_name & "'"

Here is what is prints in the window:

UPDATE tbl_Projects SET tbl_Projects.first_review_completed_date = #12/10/2016# WHERE tbl_Projects.run_name = 'SN2-66-OFA_starting_input_study_102016_BD_102133'

field [first_review_completed_date] is Short date.

perhaps something is wrong with the dates?

Thanks

 

[pbaldy]

Looks okay offhand. I'd guess something is spelled wrong.

 

[MilaK]

Here is how I call the function:

Dim varR As Variant
Dim run_name As String
run_name = Me.txtCurrentRun.Caption
 
varR = DLookup("[user_role]", "tbl_User_Control", _
    "[ID] = 1")
Debug.Print varR

Call Modcontains.CompleteReview(run_name, varR)

I'm still not sure what "too few parameters" error is caused. Thanks

 

[pbaldy]

Have you double checked all the spelling? Can you attach the db here?

 

[MilaK]

Sorry, i found the problem. I was missing "s" in tbl_Projects. Also, i changed how I was calling the function and it now works.

 

[pbaldy]

So, something was misspelled...