compile error argument not optional access 2007

[cthai]

Hi -

I have a button on a form that will bring up a new for the user to fill out but each time i click on the button it's giving me a compile error: Argument not optional... I dont know why it's costing this error... "AddNewWorksheet_red" is highlighted when i click "OK"

Private Sub txtCARWorksheet_red_Click()
On Error GoTo EH:
g_strSource = "txtCarWorksheet_red_Click"
Dim xrsp As Long
Dim rs As ADODB.Recordset
Dim StrSQL As String
Dim idWorksheet As Long
Set rs = New ADODB.Recordset
' check to see how many wkst exist if none go directly to worksheetform
'   if many ask if want to create new or review olds
'   p_CountCAREntries
Set rs = GetData("p_CountCAREntries_red '" & Me.SSN & "'")
If rs.EOF = False Then
   If rs!CountEntries > 0 Then
        'goto listing of worksheets
        'ask if they want to go to listing or start new Worksheet
        xrsp = MsgBox("A worksheet already exists for this person." & vbCrLf & " Do you want to create new record?" & vbCrLf & vbCrLf & "Select Yes to create new worksheet." & vbCrLf & "Select No to view existing worksheets.", vbYesNoCancel, "Worksheet already exists")
        Select Case xrsp
            Case 6 'yes create new worksheet
                idWorksheet = AddNewWorksheet_red(Me.SSN, Me.[Position Title].Value & vbNullString, Me.[SENSITIVITY LEVEL].Value & vbNullString)
                'DoCmd.OpenForm "frmCaseAdjudicationRecommendation", , , "idCARWorksheet = " & idWorksheet
                doOpenForm "frmCARWorksheet_red", "idCARWorksheet = " & idWorksheet  'call to function that checks link criteria is in place
                'DoCmd.Close acForm, "frmFederalEmployeeSuitability", acSaveNo
                DoCmd.SelectObject acForm, "frmCARWorksheet_red"
                DoCmd.Maximize
            Case 7 'no view existing worksheets
                'open list of worksheets
                doOpenForm "frmCARWorksheetList_red", "SSN = " & Me.SSN   'call to function that checks link criteria is in place
                DoCmd.SelectObject acForm, "frmCARWorksheetList_red"
                DoCmd.Maximize
        End Select
   Else
        'go to worksheet form directly
        'Create a new Record for person....
        idWorksheet = AddNewWorksheet_red(Me.SSN, Me.[Position Title].Value & vbNullString, Me.[SENSITIVITY LEVEL].Value & vbNullString)
        doOpenForm "frmCARWorksheet_red", "idCARWorksheet = " & idWorksheet  'call to function that checks link criteria is in place
        DoCmd.SelectObject acForm, "frmCARWorksheet_red"
        DoCmd.Maximize
   End If
End If

Exit Sub
EH:
    If g_objErr Is Nothing Then
        InitEH g_objErr
    End If
   g_objErr.HandleError Err, m_FormName, g_strSource
End Sub

[pbaldy]

How many arguments does that function (AddNewWorksheet_red) require?

[cthai]

hi pbaldy

there are 3 argument requires ... when i look at the fucntion that it's pulling from

Public Function AddNewWorksheet_red(ByVal SSN As String, ByVal POSITION As String, ByVal SensitivityLevel As String, ByVal ClearanceLevel As String) As Long
On Error GoTo EH:
g_strSource = "AddNewWorksheet_red"
Dim cmd As ADODB.Command
Dim strMsg As String
Dim lngIdWorksheet As Long
 
    Set cmd = New ADODB.Command
    cmd.CommandType = adCmdStoredProc
    cmd.CommandText = "p_CARWorksheetAddNew_red"
    cmd.ActiveConnection = CurrentProject.Connection
 
    With cmd
        .Parameters.Append .CreateParameter("@SSN", adVarChar, adParamInput, 9, SSN)
        .Parameters.Append .CreateParameter("@nvcPosition", adVarChar, adParamInput, 50, POSITION)
        .Parameters.Append .CreateParameter("@nvcPositionSensitivity", adVarChar, adParamInput, 50, SensitivityLevel)
 
    End With
    cmd.Execute , , adExecuteNoRecords
'    strMsg = "WORKSHEET ID " & cmd.Parameters("@idWorksheet")
'    MsgBox (strMsg)
AddNewWorksheet_red = cmd.Parameters("@idWorksheet")
Exit Function
EH:
    If g_objErr Is Nothing Then
        InitEH g_objErr
    End If
   g_objErr.HandleError Err, "modMISC", g_strSource
End Function

[John_G]

Why do you not compile your code each time you make changes to it? That will make life a lot easier, as you will always see where the compilation errors are.

As was pointed out above - you are calling AddNewWorksheet_red with three parameters - but you have defined it (Function AddNewWorksheet_red...) with at least 4

John

[pbaldy]

I count 4:

ByVal SSN As String, ByVal POSITION As String, ByVal SensitivityLevel As String, ByVal ClearanceLevel As String

[John_G]

Ummm -

By my count, there are 4 arguments -

SSN
Position
Sensitivitylevel
Clearancelevel

John

[cthai]

OMG! you guys are great! I was reading the code like a mad man thinking there gotta to be something i am not looking at!... thank you so much...

[pbaldy]

Happy to help!