[AccessD] Dynamic SQL statement

[Bob Walsh]

How about...

        Set rs1 = CurrentDb.OpenRecordset("Exam", dbOpenDynaset, dbAppendOnly)
        rs1.AddNew
        rs1!IndividualIDFK = ctl.Column(2, varItem)
        rs1!ExamDescriptionIDFK = strExamType
        rs1!ExamDate = strExamDate
        rs1!Results = strObservation
        rs1!NextExamDue = strFollowupDate
        rs1.Update
        rs1.Bookmark = rs1.LastModified

        rs1.Close
        Set rs1 = Nothing

-----Original Message-----
From: AccessD [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of FW Salato Center
Sent: Monday, December 07, 2015 6:54 AM
To: Access Developers discussion and problem solving
Subject: [AccessD] Dynamic SQL statement

I ran into an interesting SQL problem a few months ago and I'm about to repeat the process with another set of data. Basically, I'm updating animals by group - herd, flock...

Most of our animals are included as individuals, but forcing the vet and animal care guys to update all of them separately for the same thing was tedious and unnecessarily inefficient. Many procedures are "herd-applied" - very few records will apply to a single individual.

I created a form that populates with all of the individuals of a specific species selected as a group and displayed in a list box. They can then unselect individuals if they want. The input values allow them to choose medical procedures, dates, and include a comment. When saved, the SQL behind the code creates a new medical record with the input values for each individual in the selected group.

What complicated things was that not all of the fields in the input form are required so I had to come up with five different INSERT INTO statements and a list of conditions to meet to know which one to execute. Writing it all was easy enough - just basic stuff, but it occurs to me on the other side of all this that with the right dynamic statement, I could cover all possibilities with one statement. Maybe not.

What I've got works fine so I'm not in any hurry to "fix" it - it aint broke. This week, I'll be putting in the same solution for updating vaccines for the herd/flock so I thought now might be a good time to consider something more... dynamic.

The code for the first set - the medical procedures -- is below - as you can see, it's simple routine stuff. The first snippet checks for the required fields. The second creates the variables I drop into the SQL clauses because empty delimiters generate an error when executing a generic "all-inclusive" statement. The third sets up the SQL statement and executes it.

I'm really interested in how you guys might omit that SELECT CASE and create a dynamic "string" that considers it all and just evaluates one SQL to execute - I think it's possible but I know that on my end, coming up with it isn't worth the time it would take me. Might not be for you guys, but some of you do this stuff in your sleep. :) I don't think you really need to closely examine the code, I just included in case.

I'm interested in the discussion, even if I don't apply it - might be something to tuck into my toolkit for another project. Or, I might come back later next year and redo it. :) Nobody but me is ever going to see this code, so I don't need any "oooo... this is cool... she's so smart..." snippets. But I'm interested in learning something new just the same. - Thanks! Susan H.

    'Check for required fields.
    boo = lstIndividuals.ItemsSelected.Count = 0 Or IsNull(cboExam.Value) Or IsNull(txtObservation.Value)
    If boo Then
        MsgBox "Please choose an animal, exam type, and enter an explanation in the observation and procedure control.", _
            vbOKOnly, "Required value is missing"
        Exit Sub
    End If


'------------Nov 4
'Catch null values for non-required fields to eliminate delimiters that cause an error.
'------------


    'Accommodate null values for non-required fields.
    'Above boo snipped above catches the three (table-level) required fields.
    'The Ifs below catch the other two, otherwise, the empty delimiters make it to the
    'SQL statement and cause an error.

    strExamType = ", " & cboExam.Value

    If IsNull(txtDate.Value) Then
        strExamDate = ""
    Else
        strExamDate = ", #" & txtDate.Value & "#"
    End If

    strObservation = ", '" & txtObservation.Value & "'"

    If IsNull(txtFollowup.Value) Then
        strFollowupDate = ""
    Else
        strFollowupDate = ", #" & txtFollowup.Value & "#"
    End If

'--------------Nov 6
'This could be more efficient.
'Write new string that sets everything but the two possible missing values and run the if before the For.
'--------------

    'Appends each record to exam table.
    For Each varItem In ctl.ItemsSelected
    '        strIndividual = ctl.Column(2, varItem)
     'Determine SQL string to run.
    If IsNull(txtDate.Value) And IsNull(txtFollowup.Value) Then
    'Basic SQL statement with only the three required fields.
        strSQL = "INSERT INTO Exam " _
            & "(IndividualIDFK, ExamDescriptionIDFK, Results) VALUES " _
            & "(" _
            & ctl.Column(2, varItem) & strExamType & strObservation _
            & ")"
    ElseIf IsNull(txtDate.Value) And Not (IsNull(txtFollowup.Value)) Then
    'If only txtDate is null.
        strSQL = "INSERT INTO Exam " _
            & "(IndividualIDFK, ExamDescriptionIDFK, Results, NextExamDue) VALUES " _
            & "(" _
            & ctl.Column(2, varItem) & strExamType & strObservation & strFollowupDate _
            & ")"
    ElseIf Not (IsNull(txtDate.Value)) And IsNull(txtFollowup.Value) Then
    'If only follow up date is null.
        strSQL = "INSERT INTO Exam " _
            & "(IndividualIDFK, ExamDescriptionIDFK, ExamDate, Results) VALUES " _
            & "(" _
            & ctl.Column(2, varItem) & strExamType & strExamDate & strObservation _
            & ")"
    ElseIf Not (IsNull(txtDate.Value)) And Not (IsNull(txtFollowup.Value)) Then
    'If nothing's null.
        strSQL = "INSERT INTO Exam " _
            & "(IndividualIDFK, ExamDescriptionIDFK, ExamDate, Results, NextExamDue) VALUES " _
            & "(" _
            & ctl.Column(2, varItem) & strExamType & strExamDate & strObservation & strFollowupDate _
            & ")"
    Else
        MsgBox "There is an error with a selected item. Please try again.", vbOKOnly, "Error"
        Exit Sub
    End If
    Debug.Print strSQL
    conn.Execute strSQL
    strSQL = ""
    Next varItem
    conn.Close
    Exit Sub

--
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com
IMPORTANT NOTE: The information contained in this message may be privileged, confidential, and protected from disclosure. If the reader of this message is not the intended recipient, or an employee or agent responsible for delivering this message to the intended recipient, you are hereby notified that any dissemination, distribution, or copying of this communication is strictly prohibited. If you have received this communication in error, please notify us immediately and delete this message from your computer. Acumentra Health.