Insert records based upon combo box selection

[scottb]

Hi everyone,
I have a database with three tables: tblCases (4 fields: CaseID (autonumber), CaseName (text), CaseSubmitted (date) and CaseType (text), tblCaseType (1 field: CaseType (text), and tblCaseFollowUp (3 fields: CaseFollowUpID (autonumber), CaseID (number), and CaseFollowUpDate (date). There is one form: frmCases.
On frmCases (record source is tblCases) there are 3 fields (CaseID, CaseName, CaseSubmitted) and one combo box (name = cmbCaseType, control source = CaseType ).

I am trying to create an after update event for cmbCaseType that will create/insert records into tblCaseFollowUp, inserting CaseID for the current CaseID, and dates based on the following conditions:
If cmbCaseType=CaseTypeOne
Create 1 record with CaseFollowUpDate=CaseSubmitted+180

If cmbCaseType=CaseTypeTwo
Create 4 records with CaseFollowUpDate=CaseSubmitted+45, +180, +365, and +730

If cmbCaseType=CaseTypeThree
Create 2 records with CaseFollowUpDate=CaseSubmitted+30, and +365

Else
End If

I am struggling with how to create the combined SQL insert statements.
Any help would be greatly appreciated.
Thank you!
-Scott

[HansV]

This is a bit risky: what if the user selects a case type, then selects a different case type for the same case. The code would add records to tblCaseFollowUp twice...

Code: Select all

Private Sub cmbCaseType_AfterUpdate()
    If IsNull(Me.CaseSubmitted) Then
        Me.CaseSubmitted.SetFocus
        MsgBox "Please enter the submitted date!", vbExclamation
        Exit Sub
    End If
    Select Case cmbCaseType
        Case "CaseTypeOne"
            CurrentDb.Execute "INSERT INTO tblCaseFollowUp (CaseID, CaseFollowUpDate) VALUES (" & _
                Me.CaseID & ", #" & Format(Me.CaseSubmitted + 180, "mm/dd/yyyy") & "#)", dbFailOnError
        Case "CaseTypeTwo"
            CurrentDb.Execute "INSERT INTO tblCaseFollowUp (CaseID, CaseFollowUpDate) VALUES (" & _
                Me.CaseID & ", #" & Format(Me.CaseSubmitted + 45, "mm/dd/yyyy") & "#)", dbFailOnError
            CurrentDb.Execute "INSERT INTO tblCaseFollowUp (CaseID, CaseFollowUpDate) VALUES (" & _
                Me.CaseID & ", #" & Format(Me.CaseSubmitted + 180, "mm/dd/yyyy") & "#)", dbFailOnError
            CurrentDb.Execute "INSERT INTO tblCaseFollowUp (CaseID, CaseFollowUpDate) VALUES (" & _
                Me.CaseID & ", #" & Format(Me.CaseSubmitted + 365, "mm/dd/yyyy") & "#)", dbFailOnError
            CurrentDb.Execute "INSERT INTO tblCaseFollowUp (CaseID, CaseFollowUpDate) VALUES (" & _
                Me.CaseID & ", #" & Format(Me.CaseSubmitted + 730, "mm/dd/yyyy") & "#)", dbFailOnError
        Case "CaseTypeThree"
            CurrentDb.Execute "INSERT INTO tblCaseFollowUp (CaseID, CaseFollowUpDate) VALUES (" & _
                Me.CaseID & ", #" & Format(Me.CaseSubmitted + 30, "mm/dd/yyyy") & "#)", dbFailOnError
            CurrentDb.Execute "INSERT INTO tblCaseFollowUp (CaseID, CaseFollowUpDate) VALUES (" & _
                Me.CaseID & ", #" & Format(Me.CaseSubmitted + 365, "mm/dd/yyyy") & "#)", dbFailOnError
    End Select
End Sub

[scottb]

Thank you Hans. I see your point. If CaseName (which will be unique) is also inserted, could the after update check to see if it exists and if so stop to avoid adding multiple records? Appreciate your help with this.

[HansV]

For example:

Code: Select all

Private Sub cmbCaseType_AfterUpdate()
    If DCount("*", "tblCaseFollowUp", "CaseID=" & Me.CaseID) > 0 Then
        MsgBox "You have already added follow up dates for this case!", vbExclamation
        Exit Sub
    End If
    If IsNull(Me.CaseSubmitted) Then
        Me.CaseSubmitted.SetFocus
        MsgBox "Please enter the submitted date!", vbExclamation
        Exit Sub
    End If
    Select Case cmbCaseType
        Case "CaseTypeOne"
            CurrentDb.Execute "INSERT INTO tblCaseFollowUp (CaseID, CaseFollowUpDate) VALUES (" & _
                Me.CaseID & ", #" & Format(Me.CaseSubmitted + 180, "mm/dd/yyyy") & "#)", dbFailOnError
        Case "CaseTypeTwo"
            CurrentDb.Execute "INSERT INTO tblCaseFollowUp (CaseID, CaseFollowUpDate) VALUES (" & _
                Me.CaseID & ", #" & Format(Me.CaseSubmitted + 45, "mm/dd/yyyy") & "#)", dbFailOnError
            CurrentDb.Execute "INSERT INTO tblCaseFollowUp (CaseID, CaseFollowUpDate) VALUES (" & _
                Me.CaseID & ", #" & Format(Me.CaseSubmitted + 180, "mm/dd/yyyy") & "#)", dbFailOnError
            CurrentDb.Execute "INSERT INTO tblCaseFollowUp (CaseID, CaseFollowUpDate) VALUES (" & _
                Me.CaseID & ", #" & Format(Me.CaseSubmitted + 365, "mm/dd/yyyy") & "#)", dbFailOnError
            CurrentDb.Execute "INSERT INTO tblCaseFollowUp (CaseID, CaseFollowUpDate) VALUES (" & _
                Me.CaseID & ", #" & Format(Me.CaseSubmitted + 730, "mm/dd/yyyy") & "#)", dbFailOnError
        Case "CaseTypeThree"
            CurrentDb.Execute "INSERT INTO tblCaseFollowUp (CaseID, CaseFollowUpDate) VALUES (" & _
                Me.CaseID & ", #" & Format(Me.CaseSubmitted + 30, "mm/dd/yyyy") & "#)", dbFailOnError
            CurrentDb.Execute "INSERT INTO tblCaseFollowUp (CaseID, CaseFollowUpDate) VALUES (" & _
                Me.CaseID & ", #" & Format(Me.CaseSubmitted + 365, "mm/dd/yyyy") & "#)", dbFailOnError
    End Select
End Sub

[scottb]

Yes. Thank you for your help Hans. I will test it out and loop back.

[HansV]

You might also ask the user whether they want to replace the existing follow up dates with new ones:

Code: Select all

Private Sub cmbCaseType_AfterUpdate()
    If IsNull(Me.CaseSubmitted) Then
        Me.CaseSubmitted.SetFocus
        MsgBox "Please enter the submitted date!", vbExclamation
        Exit Sub
    End If
    If DCount("*", "tblCaseFollowUp", "CaseID=" & Me.CaseID) > 0 Then
        If MsgBox("You have already added follow up dates for this case!" & vbCrLf & _
            "Do you want to delete them, then add new ones?", vbQuestion + vbYesNo) = vbYes Then
            CurrentDb.Execute "DELETE * FROM tblCaseFollowUp WHERE CaseID=" & Me.CaseID, dbFailOnError
        Else
            Exit Sub
        End If
    End If
    Select Case cmbCaseType
        Case "CaseTypeOne"
            CurrentDb.Execute "INSERT INTO tblCaseFollowUp (CaseID, CaseFollowUpDate) VALUES (" & _
                Me.CaseID & ", #" & Format(Me.CaseSubmitted + 180, "mm/dd/yyyy") & "#)", dbFailOnError
        Case "CaseTypeTwo"
            CurrentDb.Execute "INSERT INTO tblCaseFollowUp (CaseID, CaseFollowUpDate) VALUES (" & _
                Me.CaseID & ", #" & Format(Me.CaseSubmitted + 45, "mm/dd/yyyy") & "#)", dbFailOnError
            CurrentDb.Execute "INSERT INTO tblCaseFollowUp (CaseID, CaseFollowUpDate) VALUES (" & _
                Me.CaseID & ", #" & Format(Me.CaseSubmitted + 180, "mm/dd/yyyy") & "#)", dbFailOnError
            CurrentDb.Execute "INSERT INTO tblCaseFollowUp (CaseID, CaseFollowUpDate) VALUES (" & _
                Me.CaseID & ", #" & Format(Me.CaseSubmitted + 365, "mm/dd/yyyy") & "#)", dbFailOnError
            CurrentDb.Execute "INSERT INTO tblCaseFollowUp (CaseID, CaseFollowUpDate) VALUES (" & _
                Me.CaseID & ", #" & Format(Me.CaseSubmitted + 730, "mm/dd/yyyy") & "#)", dbFailOnError
        Case "CaseTypeThree"
            CurrentDb.Execute "INSERT INTO tblCaseFollowUp (CaseID, CaseFollowUpDate) VALUES (" & _
                Me.CaseID & ", #" & Format(Me.CaseSubmitted + 30, "mm/dd/yyyy") & "#)", dbFailOnError
            CurrentDb.Execute "INSERT INTO tblCaseFollowUp (CaseID, CaseFollowUpDate) VALUES (" & _
                Me.CaseID & ", #" & Format(Me.CaseSubmitted + 365, "mm/dd/yyyy") & "#)", dbFailOnError
    End Select
End Sub

[scottb]

That is terrific. It is working perfectly. Thanks for your help and ideas Hans!

[scottb]

One more quick question: If I wanted to add a title/caption "Case Follow Up" the the following msgbox statement where would it belong? I tried and it is erroring out.

If MsgBox("You have already added follow up dates for this case!" & vbCrLf & _
"Do you want to delete them, then add new ones?", vbQuestion + vbYesNo) = vbYes

Thank you.

[HansV]

Like this:

Code: Select all

        If MsgBox("You have already added follow up dates for this case!" & vbCrLf & _
            "Do you want to delete them, then add new ones?", vbQuestion + vbYesNo, _
            "Case Follow Up") = vbYes Then

[scottb]

Thank you. I couldn't find the syntax.

[HansV]

The slightly simplified syntax for MsgBox is

MsgBox(prompt, buttons, title)

where prompt is required and the other two arguments are optional. See MsgBox function for details.

[scottb]

Much appreciated Hans.

[scottb]

One more quick request.
I added a sub form on frmCases:

At the end of the after update event I added a Requery to refresh the subform for the current record. I am using Me.x.Requery. Should this be DoCmd instead or other? Thank you.

End Select
Me.subfrmCaseFollowUps.Requery

[scottb]

I tried DoCmd.Requery.subfrmCaseFollowUps and that did not work either.

[scottb]

This did it:

End Select
Me.Dirty = False
Me.subfrmCaseFollowUps.Requery

Thanks again for all the help!

[HansV]

Congratulations!