Results 1 to 4 of 4
  1. #1
    Join Date
    Jan 2010
    Posts
    5

    Unanswered: Complex search form SQL code with numerous inner joins

    Hey everyone. I currently have a search form utilizing unbound combo boxes. The record source of the combo boxes are a query to one of two different tables (tblICD and tblCPT), each of which has a one-many relationship with a common intermediate table. The form is set up such that there are 2 columns of combo boxes, each relating to one of the aforementioned tables. Each combo box is separated by a logical operator (and/or) for further search flexibility. My goal is to have the query eventually appear in an attached subform. My query looks legit, I think. So when I search for just one ICD code (to keep things simple at first to see if it works), why do I get the error message "no value given for one or more required parameters?

    Dim strSQL As String

    'create the query based on the information on the form
    strSQL = "SELECT tblPatientdata.MRN, tblPatientdata.Dateofbirth, tblPatientdata.Misc, tblProcedure.Dateofsurgery, tblProcedure.Age, tblICDint.ICD, tblICD.Diagnosis, tblCPTint.CPT, tblCPT.Procedure"

    'create the joins
    strSQL = strSQL & " FROM ((tblPatientdata INNER JOIN tblProcedure ON tblPatientdata.MRN = tblProcedure.MRN) INNER JOIN (tblCPT INNER JOIN tblCPTint ON tblCPT.CPT = tblCPTint.CPT) ON tblProcedure.ProcedureID = tblCPTint.ProcedureID) INNER JOIN (tblICD INNER JOIN tblICDint ON tblICD.ICD = tblICDint.ICD) ON tblProcedure.ProcedureID = tblICDint.ProcedureID"

    strSQL = strSQL & " WHERE "

    'add the logical operator and subsequent combobox
    If Me!cboICD1.Value = "" Then
    strSQL = strSQL
    Else: strSQL = strSQL & "me!tblICDint!ICD = '" & Me!cboICD1.Value & "'"
    End If
    'add the 2nd combo box of first column and the 1st operator
    If Me!cboICD2.Value = "" Then
    strSQL = strSQL
    Else: strSQL = strSQL & " '" & Me!cboLogical1.Value & "' me!tblICDint!ICD = '" & Me!cboICD2.Value & "'"
    End If
    'add the 3rd combo box of first column and the 2nd operator
    If Me!cboICD3.Value = "" Then
    strSQL = strSQL
    Else: strSQL = strSQL & " '" & Me!cboLogical2.Value & "' me!tblICDint!ICD = '" & Me!cboICD3.Value & "'"
    End If
    'add the 4th combo box of first column and the 3rd operator
    If Me!cboICD4.Value = "" Then
    strSQL = strSQL
    Else: strSQL = strSQL & " '" & Me!cboLogical3.Value & "' me!tblICDint!ICD = '" & Me!cboICD4.Value & "'"
    End If
    'add the 5th combo box of first column and the 4th operator
    If Me!cboICD5.Value = "" Then
    strSQL = strSQL
    Else: strSQL = strSQL & " '" & Me!cboLogical4.Value & "' me!tblICDint!ICD = '" & Me!cboICD5.Value & "')"
    End If

    'Add the column logical operator
    If Me!FrameLogical.Value > 0 Then
    If Me!FrameLogical.Value = 1 Then
    strSQL = strSQL & " AND ("
    End If
    If Me!FrameLogical.Value = 2 Then
    strSQL = strSQL & " OR ("
    End If
    Else
    strSQL = strSQL
    End If

    'Repeat for all five 2nd column combo boxes...
    'add the logical operator and subsequent combobox
    If Me!cboCPT1.Value = "" Then
    strSQL = strSQL
    Else: strSQL = strSQL & "me!tblCPTint!CPT = '" & Me!cboCPT1.Value & "'"
    End If
    'add the 2nd combo box of first column and the 1st operator
    If Me!cboCPT2.Value = "" Then
    strSQL = strSQL
    Else: strSQL = strSQL & " '" & Me!cboLogical5.Value & "' me!tblCPTint!CPT = '" & Me!cboCPT2.Value & "'"
    End If
    'add the 3rd combo box of first column and the 2nd operator
    If Me!cboCPT3.Value = "" Then
    strSQL = strSQL
    Else: strSQL = strSQL & " '" & Me!cboLogical6.Value & "' me!tblCPTint!CPT = '" & Me!cboCPT3.Value & "'"
    End If
    'add the 4th combo box of first column and the 3rd operator
    If Me!cboCPT4.Value = "" Then
    strSQL = strSQL
    Else: strSQL = strSQL & " '" & Me!cboLogical7.Value & "' me!tblCPTint!CPT = '" & Me!cboCPT4.Value & "'"
    End If
    'add the 5th combo box of first column and the 4th operator
    If Me!cboCPT5.Value = "" Then
    strSQL = strSQL
    Else: strSQL = strSQL & " '" & Me!cboLogical8.Value & "' me!tblCPTint!CPT = '" & Me!cboCPT5.Value & "')"
    End If

    myRecordSet.Open strSQL

    End Sub


    Thanks in advance for any input!
    Attached Files Attached Files
    Last edited by jstro003; 01-11-10 at 23:46.

  2. #2
    Join Date
    Jan 2010
    Posts
    5
    I updated my code after some reading, but can't figure out why I am getting this error message

  3. #3
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    pop all the way down to myRecordSet.Open strSQL, set a breakpoint, open the intermediate window (ctrl+g) and find out what the value of strSQL is just before it executes "?strSQL", then paste the results here.
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  4. #4
    Join Date
    Jan 2010
    Posts
    5
    Thanks for the reply Teddy. Now that you showed me how to work the debugger, with some modifications the string, strSQL, shows the correct SQL in the immediate window (?strSQL) that I want when searching for one or more items. But when I don't have a breakpoint set, I'm still getting the same error message that "no value given for one or more required parameters" I think my problem lies with my definitions (setting the connection, available forms/tables, etc) or with my final command to run the string. Thoughts?

    Here is the updated code as of now:


    Public Sub cmdSearch_Click()
    Dim cnnx As ADODB.Connection
    Set cnnx = CurrentProject.Connection
    Set db = CurrentDb()
    Dim myRecordSet As New ADODB.Recordset
    myRecordSet.ActiveConnection = cnnx

    Dim strSQL As String

    'create the query based on the information on the form
    strSQL = "SELECT tblPatientdata.MRN, tblPatientdata.Dateofbirth, tblPatientdata.Misc, tblProcedure.Dateofsurgery, tblProcedure.Age, tblICDint.ICD, tblICD.Diagnosis, tblCPTint.CPT, tblCPT.Procedure"

    'create the joins
    strSQL = strSQL & " FROM ((tblPatientdata INNER JOIN tblProcedure ON tblPatientdata.MRN = tblProcedure.MRN) INNER JOIN (tblCPT INNER JOIN tblCPTint ON tblCPT.CPT = tblCPTint.CPT) ON tblProcedure.ProcedureID = tblCPTint.ProcedureID) INNER JOIN (tblICD INNER JOIN tblICDint ON tblICD.ICD = tblICDint.ICD) ON tblProcedure.ProcedureID = tblICDint.ProcedureID"

    strSQL = strSQL & " WHERE "

    'add the logical operator and subsequent combobox
    If Not IsNothing(Me!cboICD1) Then
    strSQL = strSQL & "me!tblICDint!ICD = " & Me!cboICD1.Value
    End If
    'add the 2nd combo box of first column and the 1st operator
    If Not IsNothing(Me!cboICD2) Then
    strSQL = strSQL & " " & Me!cboLogical1.Value & " me!tblICDint!ICD = " & Me!cboICD2.Value
    End If
    'add the 3rd combo box of first column and the 2nd operator
    If Not IsNothing(Me!cboICD3) Then
    strSQL = strSQL & " " & Me!cboLogical2.Value & " me!tblICDint!ICD = " & Me!cboICD3.Value
    End If
    'add the 4th combo box of first column and the 3rd operator
    If Not IsNothing(Me!cboICD4) Then
    strSQL = strSQL & " " & Me!cboLogical3.Value & " me!tblICDint!ICD = " & Me!cboICD4.Value
    End If
    'add the 5th combo box of first column and the 4th operator
    If Not IsNothing(Me!cboICD5) Then
    strSQL = strSQL & " " & Me!cboLogical4.Value & " me!tblICDint!ICD = " & Me!cboICD5.Value
    End If

    'Add the column logical operator
    If Me!FrameLogical.Value > 0 Then
    If Me!FrameLogical.Value = 1 Then
    strSQL = strSQL & " AND ("
    End If
    If Me!FrameLogical.Value = 2 Then
    strSQL = strSQL & " OR ("
    End If
    Else
    strSQL = strSQL
    End If

    'Repeat for all five 2nd column combo boxes...
    'add the logical operator and subsequent combobox
    If Not IsNothing(Me!cboCPT1) Then
    strSQL = strSQL & "me!tblCPTint!CPT = " & Me!cboCPT1.Value
    End If
    'add the 2nd combo box of first column and the 1st operator
    If Not IsNothing(Me!cboCPT2) Then
    strSQL = strSQL & " " & Me!cboLogical5.Value & " me!tblCPTint!CPT = " & Me!cboCPT2.Value
    End If
    'add the 3rd combo box of first column and the 2nd operator
    If Not IsNothing(Me!cboCPT3) Then
    strSQL = strSQL & " " & Me!cboLogical6.Value & " me!tblCPTint!CPT = " & Me!cboCPT3.Value
    End If
    'add the 4th combo box of first column and the 3rd operator
    If Not IsNothing(Me!cboCPT4) Then
    strSQL = strSQL & " " & Me!cboLogical7.Value & " me!tblCPTint!CPT = " & Me!cboCPT4.Value
    End If
    'add the 5th combo box of first column and the 4th operator
    If Not IsNothing(Me!cboCPT5) Then
    strSQL = strSQL & " " & Me!cboLogical8.Value & " me!tblCPTint!CPT = " & Me!cboCPT5.Value
    End If

    myRecordSet.Open strSQL

    End Sub

    Here is what the ?strSQL shows before the sting is executed when searching for two ICD codes and one 'AND' operator (to keep it simple):

    SELECT tblPatientdata.MRN... FROM ((tblPatientdata INNER JOIN... WHERE me!tblICDint!ICD = 123 AND me!tblICDint!ICD = 222

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •