Submit Form and If Then VBA

[dmhackett8]

Hello,

The database I use at work is needing help, but I'm not sure how to accomplish this task using VBA or other methods. We enter information onto an "Evaluation" form and then submit it, sending information to two tables, "tblCoaching" and "tblSurveyInfo".

What I'm trying to do is exclude certain employee types from having information send to tblSurveyInfo and only tblCoaching.

Here is what the code and form look like for submitting the form:

Private Sub SubmitForm()


    Dim rst As New ADODB.Recordset
    Dim rstSrvyInf As New ADODB.Recordset
    Dim bookmark As Long 'not using this anymore it will not keep accurate with data submittion
    Dim bookmark2 As Long
    Dim Continue As Integer

    On Error GoTo Err_DataError

    Continue = MsgBox("Are you sure you want to continue?", vbYesNo, "Submit Form")
    Select Case Continue
        Case vbNo
            Exit Sub
    End Select

    With rst 'this rst will go to tblCoaching
        .Open "tblCoaching", CurrentProject.Connection, adOpenKeyset, adLockOptimistic
        .AddNew
        !EvaluatorID = Me("textEvaluatorID").Value
        !EmployeeID = Me("textEmployeeID").Value
        !CallTypeID = Me("comboCallType").Value
        !DateofCall = Me("DateofCall").Value
        !DateGraded = Me("DateGraded").Value
        !NumberofCall = Me("NumberofCall").Value
        !CallID = Me("CallID").Value
        !TotalScore = Me("TotalScore").Value
        !CustomerID = Me("CustomerID").Value
        !CallLength = Me("textCallLength").Value
        !OutCallID = Me("OutCallID").Value
        !OutCustomerID = Me("OutCustomerID").Value
        ' Any additional fields to be updated go here.
        .Update
        bookmark2 = !CoachingID
    End With
    rst.Close
    'MsgBox "The magic ID is " & rst!CoachingID

    If Me("comboCallType").Value = "2" Or Me("comboCallType").Value = "10" Or Me("comboCallType").Value = "6" Or Me("comboCallType").Value = "7"_ 
      Or Me("comboCallType").Value = "4" Or Me("comboCallType").Value = "69" Or Me("comboCallType").Value = "8" Or Me.textAssociateType.Value =_ 
      "Customer Service" Then
        With rstSrvyInf
            .Open "tblSurveyInfo", CurrentProject.Connection, adOpenKeyset, adLockOptimistic
            .AddNew
            !EvalID = Me("textEvalID").Value
            !EmpID = Me("textEmpID").Value
            !EmpNumber = Me("textEmpNumber").Value
            !Supervisor = Me("textSupervisor").Value
            !DateofCall = Me("textDateofCallS").Value
            !SurveyID = Me("textSurveyID").Value
            !CallType = Me("textCallType").Value
            !CustomerID = Me("CustomerIDS").Value
            !CustName = Me("textCustName").Value
            !DateGraded = Me("DateGraded").Value
            .Update
        End With
        rstSrvyInf.Close
    Else
    End If

When an employee name is entered into the textEmployeeName combobox on the form, it is coded to bring up their information including position type in text boxes. I'm trying to use that position type that is generated by their name to determine whether or not the rstSrvyInf is processed. The text box that includes the employee position is named textAssociateType.



I attempted to use an If Then statement like I did for certain "comboCallTypes" (which works), but it's not working for some reason.

Is there something I'm missing or an easy way to accomplish this? Please let me know if this requires more information to share on my part!

Thanks!

[June7]

Note that I edited your post to use CODE tags so indentation is retained and easier to read.

Why are you using code to write data? You are not using bound forms?

What does 'not working' mean - error message, wrong results, nothing happens? Step debug. Follow code as it executes. Where does it deviate from expectations? Review link at bottom of my post for debugging guidelines.

[dmhackett8]

Thank you for adding that

I was not the one who created this database, but I am the one who can improve it. I am, although, a beginner, so bound forms is foreign to me. I simply learned how to use MSAccess by reviewing the structure, VBA, and function of this database. Maybe I should learn a bit more about bound forms

By not working I mean that information is still being sent to the tblSurveyInfo table. No errors appear in VBA when I submit the form, so I guess this falls into the wrong result category. Does this answer your questions?

[June7]

Yes, now debug. Debugging is a critical skill for programmer. If you have coded for the condition to not write the data, why does it not comply?

The way you have the condition written, data will write if any of the specified call types are met OR associate type is Customer Service. This means if the associate type is not Customer Service the data could still be written if any of the call types are met. Perhaps you need an AND operator.

If (the call type parameters here) AND Me.textAssociateType = "Customer Service" Then

Note the () - they are important to conditional logic when mixing OR and AND operators, use them.


I know that some developers prefer UNBOUND forms but I have never built a database that way.

Still don't understand why data is duplicated.

Save EmpID and don't need EmployeeNumber.

Why are DateOfCall and CallType in both tables? Why not just CallID?

You probably should gain a grounding in basics of relational database principles and Access functionality. Here is a good tutorial site http://www.rogersaccesslibrary.com/

[dmhackett8]

That's true! I didn't even think about using AND instead. I will give that a try when I get to work tomorrow.

All of the text boxes are unbound on the form, why they created the DB that way I don't know! I plan to go through a MS ACCESS class here next month so I'm sure I'll learn the basics in that class.

Thank you for your help so far and I'll update once I try this tomorrow