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

    Unanswered: Numerous Multiselect lists encompasing many-many relationships Query

    Hey guys,

    Thanks for taking the time to help me out. I've been stumped on the design of a search form for a few weeks now. I've scoured google groups, misc. MVP sites, this forum, and even attempted to read numerous Access/SQL/VBA For Dummies books to no avail. I am a beginner with this stuff, and it doesn't help that half of the coding seems like a foreign language to me despite my best efforts to learn the syntax. You're my last hope!

    I've posted my database in its current state since I'll probably butcher its description. I have 6 tables in all due to a few one-many and many-to-many relationships (so 2 of the tables are merely intermediate tables) and I think the database is normalized. The premise is that the database will store patient information regarding demographics (stored in one table), dates of surgeries (another table since patients may have more than one). Each date of surgery is associated with multiple CPT or ICD-9 codes (unique numbers indicating a diagnosis or procedure which are stored in their own tables with the codes as the PK). I am setting up the database such that people without any Access knowledge can use the database. The data entry form is essentially finished, but it works in principle.

    My problem comes with the search form (frmsearch). There are four total multiselect boxes - one for the ICD-9 codes (lstICD), CPT codes (lstCPT), procedures (lstProcedures), and diagnoses (lstDiagnoses). You can only search using 1 - 2 lists since I used an enabled function to simplify the query results. There is also a logical function between there. So the goal is by searching from anywhere from 1 to however many codes/descriptors, the attached subform will display the results of the query including the patient demographics, surgical dates, etc (i.e., everything in the other tables).

    Private Sub Command45_Click()
    Dim MyDB As Database
    Dim mydbd As ADODB.Connection
    Set mydbd = CurrentProject.Connection
    Dim myRecordset As New ADODB.Recordset
    myRecordset.ActiveConnection = mydbd
    Dim strlog As String
    Dim strWhere As String

    strWhere = "Select "

    'Entering ICD list selection
    If Me!lstICD.ItemsSelected.Count > 0 Then
    Set ctl = Me!lstICD
    For Each varItem In ctl.ItemsSelected
    strWhere = strWhere & ctl.ItemData(varItem) & ","
    Next varItem
    End If


    'Entering Diagnoses list selection
    If Me!lstDiagnoses.ItemsSelected.Count > 0 Then
    Set ctl = Me!lstDiagnoses
    For Each varItem In ctl.ItemsSelected
    strWhere = strWhere & "'" & ctl.ItemData(varItem) & "',"
    Next varItem
    End If

    'Adding logical operator
    If Me!Frame88.Value = 1 Then
    strlog = "AND"
    Else
    strlog = "OR"
    End If
    If Me!Frame88.Value = 2 Then
    strlog = "OR"
    Else
    strlog = "AND"
    End If
    strWhere = strWhere & strlog & " "

    'Entering CPT list selection
    If Me!lstCPT.ItemsSelected.Count > 0 Then
    Set ctl = Me!lstCPT
    For Each varItem In ctl.ItemsSelected
    strWhere = strWhere & ctl.ItemData(varItem) & ","
    Next varItem
    End If

    'Entering Procedures list selection
    If Me!lstProcedures.ItemsSelected.Count > 0 Then
    Set ctl = Me!lstProcedures
    For Each varItem In ctl.ItemsSelected
    strWhere = strWhere & "'" & ctl.ItemData(varItem) & "',"
    Next varItem
    End If

    'trim trailing comma
    strWhere = Left(strWhere, Len(strWhere) - 1)


    myRecordset.Open strWhere


    End Sub

    Any help with the VBA code upon the search command button is much appreciated. If you would like more information, please let me know. Thanks!
    Attached Files Attached Files
    Last edited by jstro003; 01-03-10 at 22:15. Reason: Wanted to add updated code

  2. #2
    Join Date
    Jan 2010
    Posts
    5
    Nevermind. I abandoned this approach. I still need help, but I will repost with the appropriate topic.

Posting Permissions

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