Results 1 to 3 of 3

Thread: Search Design

  1. #1
    Join Date
    Feb 2004
    Posts
    59

    Unanswered: Search Design

    Hello All!

    I have almost finished one of my smaller apps. I'm very excited to have my first program almost done! I have one final step to go! I am creating a program, that will search a database, and return values. Sounds easy, because it is! But here's the deal:

    I generally have a "static" search event when pressing a button. For example, click this button for searching by last name (user typed in), press this button to search by location (selected from drop down), but this program needs to be a little more advanced.

    I'd like to have the button generate a query from my table based on the drop downs selected, and then place those entries into a ListView (Report). But I'd like to have it so that they can select anywhere between 0 (return all results) to 4 of the dropdowns, and have the query automatically run and return the results. My problem is that I've never used anything like this before, and I think I should have it create a SQL statement, based on the selections.

    My dropdowns are, cmbLocation, cmbSite, cmbName, and cmbBU. The first entry in all boxes is "select an item". It needs to search tblOnCall.

    How would the best way to be to code this? I'd like the easiest code where the results can be dumped into my ListView.

    So I guess this is a two part question:
    1) How should I check to see which boxes have been selected, and only query based on those records (with ALL being substituted for any non-selected boxes)?
    2) Should I use a SQL statement to search the table and then place those records into a ListView?

    I'm open to any/all suggestions! Thank you!
    Last edited by mateo107; 01-09-05 at 11:00.
    -Matt H-
    www.comperfection.com

  2. #2
    Join Date
    Jan 2005
    Posts
    18

    Smile

    Hello friend

    This is neezha. I have designed a search Screen which will have three combo boxes we shall take it as cmbSearch1, cmbSearch2 and cmbSearch3

    these combo boxes contains all the fields in ur database on which the search should be performed.

    and a text box corresponding to each where u will type in the criteria and then three combo box which has the relation such as " Like, = , > , < , <> "
    the the list view

    then use the following code which will help u design a better search

    the code below is a sample which i did for my own project. u try it out and if u need any help u can sure contact me.(neezha_s@rediffmail.com)


    Dim strArrayFieldList(50, 2) As String
    Dim strSql As String
    Dim itmxList As ListItem
    Public frmCurrentSearchingForm As Form
    Public boolCallFromAnotherForm As Boolean
    Public txtCallingControl As TextBox
    Dim adodbrsPatientPersonalInformation As New ADODB.Recordset
    Option Explicit

    Private Sub cmdClose_Click()
    Unload Me
    End Sub

    Private Sub cmdOk_Click()
    If lstSearchPatient.SelectedItem Is Nothing Then
    MsgBox "Select A Patient from the List", vbInformation, "Hospital Information : Error"
    Exit Sub
    End If
    frmCurrentSearchingForm.Enabled = True
    frmCurrentSearchingForm.WindowState = 2
    boolCallFromAnotherForm = True
    txtCallingControl.Text = lstSearchPatient.SelectedItem
    boolCallFromAnotherForm = False
    Unload Me
    End Sub

    Private Sub cmdRefresh_Click()
    fillSearchList
    End Sub

    Private Sub Form_Load()
    InitializeSearch
    End Sub

    Private Sub fillSearchList()
    lstSearchPatient.ListItems.Clear
    strSql = "Select * from PatientPersonalInformation where "
    If cmbSearchBy1.Text <> "" Then
    If strArrayFieldList(cmbSearchBy1.ListIndex + 1, 2) = "Date" Then
    If cmbRelation1.Text = "LIKE" Then
    MsgBox "The Relational Operator cannot be Like for : " & cmbSearchBy1.Text, vbInformation, "Hospital Management : Error"
    Exit Sub
    End If
    strSql = strSql & " " & strArrayFieldList(cmbSearchBy1.ListIndex + 1, 1) & " " & cmbRelation1.Text & " '" & Format(txtSearchByText1.Text, "mm/dd/yyyy") & "' and"
    Else
    If cmbRelation1.Text = "LIKE" Then
    strSql = strSql & " " & strArrayFieldList(cmbSearchBy1.ListIndex + 1, 1) & " " & cmbRelation1.Text & " '%" & Trim(txtSearchByText1.Text) & "%' and"
    Else
    strSql = strSql & " " & strArrayFieldList(cmbSearchBy1.ListIndex + 1, 1) & " " & cmbRelation1.Text & " '" & Trim(txtSearchByText1.Text) & "' and"
    End If
    End If
    If cmbSearchBy2.Text <> "" Then
    If strArrayFieldList(cmbSearchBy2.ListIndex + 1, 2) = "Date" Then
    If cmbRelation1.Text = "LIKE" Then
    MsgBox "The Relational Operator cannot be Like for : " & cmbSearchBy2.Text, vbInformation, "Hospital Management : Error"
    Exit Sub
    End If
    strSql = strSql & " " & strArrayFieldList(cmbSearchBy2.ListIndex + 1, 1) & " " & cmbRelation2.Text & " '" & Format(txtSearchByText2.Text, "mm/dd/yyyy") & "' and"
    Else
    If cmbRelation2.Text = "LIKE" Then
    strSql = strSql & " " & strArrayFieldList(cmbSearchBy2.ListIndex + 1, 1) & " " & cmbRelation2.Text & " '%" & Trim(txtSearchByText2.Text) & "%' and"
    Else
    strSql = strSql & " " & strArrayFieldList(cmbSearchBy2.ListIndex + 1, 1) & " " & cmbRelation2.Text & " '" & Trim(txtSearchByText2.Text) & "' and"
    End If
    End If
    If cmbSearchBy3.Text <> "" Then
    If strArrayFieldList(cmbSearchBy3.ListIndex + 1, 2) = "Date" Then
    If cmbRelation3.Text = "LIKE" Then
    MsgBox "The Relational Operator cannot be Like for : " & cmbSearchBy3.Text, vbInformation, "Hospital Management : Error"
    Exit Sub
    End If
    strSql = strSql & " " & strArrayFieldList(cmbSearchBy3.ListIndex + 1, 1) & " " & cmbRelation3.Text & " '" & Format(txtSearchByText3.Text, "mm/dd/yyyy") & "' and"
    Else
    If cmbRelation3.Text = "LIKE" Then
    strSql = strSql & " " & strArrayFieldList(cmbSearchBy3.ListIndex + 1, 1) & " " & cmbRelation3.Text & " '%" & Trim(txtSearchByText3.Text) & "%' and"
    Else
    strSql = strSql & " " & strArrayFieldList(cmbSearchBy3.ListIndex + 1, 1) & " " & cmbRelation3.Text & " '" & Trim(txtSearchByText3.Text) & "' and"
    End If
    End If
    End If
    End If
    strSql = Mid(strSql, 1, Len(strSql) - 3)
    Else
    strSql = "Select * from PatientPersonalInformation"
    End If
    Set adodbrsPatientPersonalInformation = Nothing
    adodbrsPatientPersonalInformation.Open strSql, adodbconHospitalManagement, adOpenForwardOnly, adLockReadOnly
    Do While Not adodbrsPatientPersonalInformation.EOF
    Set itmxList = lstSearchPatient.ListItems.Add(, , adodbrsPatientPersonalInformation!chrHospitalNumbe r)
    itmxList.SubItems(1) = adodbrsPatientPersonalInformation!chrFirstName
    itmxList.SubItems(2) = adodbrsPatientPersonalInformation!chrLastName
    itmxList.SubItems(3) = adodbrsPatientPersonalInformation!dateRegistration Date
    itmxList.SubItems(4) = adodbrsPatientPersonalInformation!chrGender
    itmxList.SubItems(5) = adodbrsPatientPersonalInformation!dateDateOfBirth
    itmxList.SubItems(6) = adodbrsPatientPersonalInformation!chrAge
    itmxList.SubItems(7) = adodbrsPatientPersonalInformation.MoveNext
    Loop
    End Sub

    Private Sub InitializeSearch()
    strArrayFieldList(1, 1) = "chrHospitalNumber"
    strArrayFieldList(1, 2) = "Varchar"
    strArrayFieldList(2, 1) = "chrFirstName"
    strArrayFieldList(2, 2) = "Varchar"
    strArrayFieldList(3, 1) = "chrLastName"
    strArrayFieldList(3, 2) = "Varchar"
    strArrayFieldList(4, 1) = "dateRegistrationDate"
    strArrayFieldList(4, 2) = "Date"
    strArrayFieldList(5, 1) = "chrGender"
    strArrayFieldList(5, 2) = "Varchar"
    strArrayFieldList(6, 1) = "dateDateOfBirth"
    strArrayFieldList(6, 2) = "Date"
    strArrayFieldList(7, 1) = "chrAge"
    strArrayFieldList(7, 2) = "Varchar"
    cmbSearchBy1.AddItem "HOSPITAL NUMBER", 0
    cmbSearchBy1.AddItem "FIRST NAME", 1
    cmbSearchBy1.AddItem "LAST NAME", 2
    cmbSearchBy1.AddItem "REGISTRATION DATE", 3
    cmbSearchBy1.AddItem "GENDER", 4
    cmbSearchBy1.AddItem "DATE OF BIRTH", 5
    cmbSearchBy1.AddItem "AGE", 6

    cmbSearchBy2.AddItem "HOSPITAL NUMBER", 0
    cmbSearchBy2.AddItem "FIRST NAME", 1
    cmbSearchBy2.AddItem "LAST NAME", 2
    cmbSearchBy2.AddItem "REGISTRATION DATE", 3
    cmbSearchBy2.AddItem "GENDER", 4
    cmbSearchBy2.AddItem "DATE OF BIRTH", 5
    cmbSearchBy2.AddItem "AGE", 6

    cmbSearchBy3.AddItem "HOSPITAL NUMBER", 0
    cmbSearchBy3.AddItem "FIRST NAME", 1
    cmbSearchBy3.AddItem "LAST NAME", 2
    cmbSearchBy3.AddItem "REGISTRATION DATE", 3
    cmbSearchBy3.AddItem "GENDER", 4
    cmbSearchBy3.AddItem "DATE OF BIRTH", 5
    cmbSearchBy3.AddItem "AGE", 6
    lstSearchPatient.ColumnHeaders.Add , , "HOSPITAL NUMBER", 2500
    lstSearchPatient.ColumnHeaders.Add , , "FIRST NAME", 3500
    lstSearchPatient.ColumnHeaders.Add , , "LAST NAME", 3500
    lstSearchPatient.ColumnHeaders.Add , , "REGISTRATION DATE", 2500
    lstSearchPatient.ColumnHeaders.Add , , "GENDER", 1000
    lstSearchPatient.ColumnHeaders.Add , , "DATE OF BIRTH", 2500
    lstSearchPatient.ColumnHeaders.Add , , "AGE", 1000
    cmbRelation1.ListIndex = 0
    cmbRelation2.ListIndex = 0
    cmbRelation3.ListIndex = 0
    txtSearchByText1.Enabled = False
    cmbSearchBy2.Enabled = False
    txtSearchByText2.Enabled = False
    cmbSearchBy3.Enabled = False
    txtSearchByText3.Enabled = False
    End Sub

    Private Sub cmbSearchBy1_Change()
    If Len(cmbSearchBy1.Text) = 0 Then
    txtSearchByText1.Enabled = False
    txtSearchByText1.Text = ""
    cmbSearchBy2.Enabled = False
    cmbSearchBy2.Text = ""
    End If
    End Sub

    Private Sub cmbSearchBy1_Click()
    If Len(cmbSearchBy1.Text) = 0 Then
    txtSearchByText1.Enabled = False
    cmbSearchBy2.Enabled = False
    Else
    txtSearchByText1.Enabled = True
    cmbSearchBy2.Enabled = True
    End If
    End Sub

    Private Sub cmbSearchBy1_KeyPress(KeyAscii As Integer)
    KeyAscii = 0
    End Sub

    Private Sub cmbSearchBy2_Change()
    If Len(cmbSearchBy2.Text) = 0 Then
    txtSearchByText2.Enabled = False
    txtSearchByText2.Text = ""
    cmbSearchBy3.Enabled = False
    cmbSearchBy3.Text = ""
    End If
    End Sub

    Private Sub cmbSearchBy2_Click()
    If Len(cmbSearchBy2.Text) = 0 Then
    txtSearchByText2.Enabled = False
    cmbSearchBy3.Enabled = False
    Else
    txtSearchByText2.Enabled = True
    cmbSearchBy3.Enabled = True
    End If
    End Sub

    Private Sub cmbSearchBy2_KeyPress(KeyAscii As Integer)
    KeyAscii = 0
    End Sub

    Private Sub cmbSearchBy3_Change()
    If Len(cmbSearchBy3.Text) = 0 Then
    txtSearchByText3.Enabled = False
    txtSearchByText3.Text = ""
    End If
    End Sub

    Private Sub cmbSearchBy3_Click()
    If Len(cmbSearchBy3.Text) = 0 Then
    txtSearchByText3.Enabled = False
    Else
    txtSearchByText3.Enabled = True
    End If
    End Sub

    Private Sub cmbSearchBy3_KeyPress(KeyAscii As Integer)
    KeyAscii = 0
    End Sub


  3. #3
    Join Date
    Nov 2004
    Posts
    108
    I have created search form using VB code as the previous example and SQL-SPs and the last one resulted to be the most efficient.

    All the parameters for the SP most be optional and the default value Null
    the Select statement will be something like
    Code:
    Select *
    From myTable or View
    Where (field1=Param1 or Param1 Is Null) and
         (field2=Param2 or Param1 Is Null) and
         (field3=Param3 or Param1 Is Null) and
         (field4=Param4 or Param1 Is Null)
    to err is human ; to really mess things up requires a computer

Posting Permissions

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