ADP SPROC Help

[TR21Mark]

Using Access 2007 ADP front end / SQL 2005 BE / Win7 x64

I have a form with unbound controls used for data entry. I have a stored procedure that is to append all of the fields into tblProjects.

What my code below does runs validation that each textbox is not null and if it passes then it runs a private sub that passes the parameters to the stored procedure. Right now I am having 2 issues with the code. The first is I am getting no error messages, the second is the data is not getting input into the table.

Could someone review my code and steer me in the right direction please? Thank in advance and appreciate any help that is given.

Private Sub cmdSaveProj_Click()
    ' Comments:
    ' Params  :
    ' Modified: MWJ 07192011
    
    'This checks all controls on the form with the word "ACE" in the Tag property of the control
    'If any of the controls are null or empty then the user can not proceed
    
    On Error GoTo PROC_ERR
    
    Dim objCtl As Object
    
    For Each objCtl In Me.Controls
        If (objCtl.ControlType = acTextBox Or objCtl.ControlType = acComboBox) And objCtl.Tag = "ACE" Then
            
            If Nz(objCtl, "") = "" Then
                MsgBox ("You can't exit this form until ALL data is provided!" & vbCrLf & "Enter the missing data and try again . . . "), , "MISSING DATA"
                objCtl.SetFocus
                
                Exit For
            End If
            
        End If
        'Insert below this line the commands you want if validation is met
        'Me.cmdCreateFC.Visible = True
        Call AddProject
        
    Next objCtl
    

    
PROC_EXIT:
    Exit Sub
    
PROC_ERR:
    MsgBox Err.Description, vbCritical, Me.Name & ".cmdSaveProj_Click"
    Resume PROC_EXIT




End Sub

Private Sub Form_AfterUpdate()

End Sub


Private Sub AddProject()
    Dim cn As ADODB.Connection
    Dim cmd As ADODB.Command
    
    Set cmd = New ADODB.Command
    Set cn = New ADODB.Connection
    cn.Open "my conn left out on purpose"
    
    On Error GoTo PROC_ERR
    
    With cmd
            Set .ActiveConnection = cn
            .CommandText = "mjspAddNewProject"
            .CommandType = adCmdStoredProc
            
           
            .Parameters.Append .CreateParameter("@ProjName", adVarChar, adParamInput, 100, Me.txtJobName)
            .Parameters.Append .CreateParameter("@ProjNo", adVarChar, adParamInput, 20, Me.txtJobNo)
            .Parameters.Append .CreateParameter("@ProjS1", adVarChar, adParamInput, 60, Me.txtStreet1)
            .Parameters.Append .CreateParameter("@ProjS2", adVarChar, adParamInput, 60, Me.txtStreet2)
            .Parameters.Append .CreateParameter("@ProjCity", adVarChar, adParamInput, 50, Me.txtCity)
            .Parameters.Append .CreateParameter("@ProjState", adVarChar, adParamInput, 5, Me.txtState)
            .Parameters.Append .CreateParameter("@ProjZip", adVarChar, adParamInput, 15, Me.txtZip)
            .Parameters.Append .CreateParameter("@ProjGC", adVarChar, adParamInput, 85, Me.cboGCName)
            .Parameters.Append .CreateParameter("@ProjGCID", adVarChar, adParamInput, 20, Me.txtGCID)
            
            'keep adding lines above for each parameter/textbox or control
            
            .Execute
            Set .ActiveConnection = Nothing
            End With
            
    
    
PROC_EXIT:
    cn.Close
    Set cmd = Nothing
    Set cn = Nothing
    Exit Sub
    
PROC_ERR:
    MsgBox Err.Number, Err.Description, vbCritical, Me.Name & ".AddProject"
    Resume PROC_EXIT


End Sub

 

[xenou]

sSQL is your sql string - so either declare a variable sSQL and use it to hold the sql string or remove it and use whatever you were using before. It was there only by way of example.

ξ

 

[TR21Mark]

Thanks,

I removed the sSQL and comma and it is working now. Thanks so much for this great tidbit of knowledge. Now I have one place to change the connection string.