Results 1 to 4 of 4
  1. #1
    Join Date
    Jan 2004
    Location
    Oslo
    Posts
    45

    Arrow Unanswered: Need expert advice on search-possibilities. Please...

    Hi and thanx a LOT for reading my post..

    I'm quite lost here and VERY near deadline so yes.. i'm a bit stressed out

    I'm creating this criminal investigation information -"dump" database which should be searchable every- and any way possible..

    ----------------------GENERAL INFO---------------------------------
    In general it's 5 mainforms -
    * Person (PID)
    * Vehicle (VID)
    * Location (LID)
    * Scenary (SID)
    * Telephone (TID)

    Each and everyone of these forms got their "relation-form" like this example :

    PersonTelephone (PTID)
    Fields :
    *PID (auto-inserted if user came from Person form)
    *TID (same as above ex. Telephone form) (user picks which telephone should be "related" to the person via lookup)
    *Role (which "role" the user has on the selected telephone)
    *Date
    -----------------------------------------------------------

    I have tried to make this search-form with 1 searchfield and one button. Then i got x numbers of labels which displays how many "matches" i find in each relation-form (ex VehichleLocation) and a "display results" button which opens up a "table" with the results, but since i got about 15 relation-forms it's not a good way to handle it..

    -------------What i wonder about : -----------------------------------
    1) How can i, in a better way, search multiple relation/connection forms based on information input by the user in ex. 2 searchfields ?

    2) and.. how on EARTH can i display the results in a good way ?

    3) Is there any good article anyone knows about which "tutorials" me through this in any way ?

    I hope someone is able to help me with this issue.. it seems my knowledge has meet the edge when it comes to this

    Best regards.. and have a HAPPY easter-holiday..

    Terje..
    Best regards
    Mirador

  2. #2
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    "expert advice" might be an exaggeration, but if you can imagine a search form with appropriate textboxes and radiobutton groups, maybe this sort of code in the "go" button is what you are looking for:
    Code:
    Private Sub butSearch_Click()
    
        Dim strSQL As String
        
    
        'construct the SELECT part depending on what you want to see in the return
    
        strSQL = "SELECT tblTable1.IDxxxx, tblTable1.strYyyyy, tblTable1.NumZzzzz "
        strSQL = strSQL & "FROM tblTable1 "
        'as complex as you like, joins etc
    
    
        'construct the WHERE part depending on what is happening on the form
     
        strSQL = strSQL & "WHERE (((1=1)) " 'thanks Rudy
    
        If Not IsNull(numBox1) Then strSQL = strSQL & "AND ((tblTable99.myNum1)=" & numBox1 & ") "   'example for numeric
        If Len(stringBox1) > 0 Then strSQL = strSQL & "AND ((tblTable77.mystr1) Like ""*" & stringBox1 & "*"") "   'example for string
        'etc - as complicated as you like
    
    
        'construct the ORDER BY part depending on what is happening on the form
    
        Select Case howSort
            Case 1
                strSQL = strSQL & ") ORDER BY tblTable66.field33 ;"
            Case 2
                strSQL = strSQL & ") ORDER BY tblTable55.field44 ;"
            Case Else
                strSQL = strSQL & ") ORDER BY tblTable1.strYyyyy ;"
        End Select
    
    
        'setup the form to display the returns (easiest is in continuous forms)
    
        Me.RecordSource = strSQL
        Me.Requery
           
    End Sub
    izy
    currently using SS 2008R2

  3. #3
    Join Date
    Sep 2003
    Location
    MI
    Posts
    3,713
    And to echo Izy ...

    Code:
    Function ConstructFilter() As String
        Dim HaveStatusSEL As Boolean, HaveTrackingSEL As Boolean, HaveRequestorSEL As Boolean
        Dim HaveUsingSEL As Boolean, HaveDatesSEL As Boolean
        Dim FilterString As String
        
        HaveStatusSEL = False
        HaveTrackingSEL = False
        HaveRequestorSEL = False
        HaveUsingSEL = False
        HaveDatesSEL = False
        
        ' Validations
        If BeginTrackTxt.Value & "" <> "" And EndTrackTxt.Value & "" <> "" Then
            If CLng(BeginTrackTxt.Value) > CLng(EndTrackTxt.Value) Then
                MsgBox "The starting tracking # cannot exceed the ending tracking #.", vbExclamation, "System Monitor"
                ConstructFilter = ""
                BeginTrackTxt.SetFocus
                Exit Function
            End If
        End If
        If BeginDtTxt.Value & "" <> "" And EndDtTxt.Value & "" <> "" Then
            If CDate(BeginDtTxt.Value) > CDate(EndDtTxt.Value) Then
                MsgBox "The starting date cannot exceed the ending date.", vbExclamation, "System Monitor"
                ConstructFilter = ""
                BeginDtTxt.SetFocus
                Exit Function
            End If
        End If
        
        FilterString = " WHERE ("
        If SelectionGroup.Value <> 0 Then
            FilterString = FilterString & "( [Purchase Orders].Status="
            Select Case SelectionGroup.Value
                Case 1  ' Purchase Requests
                    FilterString = FilterString & PO_Created_STAT
                Case 2  ' In Process Orders
                    FilterString = FilterString & PO_InProcess_STAT
                Case 3  ' Validated Orders
                    FilterString = FilterString & PO_Validated_STAT
                Case 4  ' Outstanding Orders
                    FilterString = FilterString & PO_Outstanding_STAT
                Case 5  ' Fulfilled Orders
                    FilterString = FilterString & PO_Fulfilled_STAT
                Case 6  ' Voided Orders
                    FilterString = FilterString & PO_VOIDED_STAT
            End Select
            FilterString = FilterString & " )"
            HaveStatusSEL = True
        End If
        If BeginTrackTxt.Value & "" <> "" Then
            If HaveStatusSEL Then FilterString = FilterString & " AND "
            FilterString = FilterString & "( [Purchase Orders].[Tracking #]"
            If EndTrackTxt.Value & "" <> "" Then
                FilterString = FilterString & " BETWEEN " & CLng(BeginTrackTxt.Value) & " AND "
            Else
                FilterString = FilterString & "=" & CLng(BeginTrackTxt.Value) & " )"
            End If
            HaveTrackingSEL = True
        End If
        If EndTrackTxt.Value & "" <> "" Then
            If HaveStatusSEL And Not HaveTrackingSEL Then
                FilterString = FilterString & " AND "
            End If
            If Not HaveTrackingSEL Then
                FilterString = FilterString & "( [Purchase Orders].[Tracking #]="
            End If
            FilterString = FilterString & CLng(EndTrackTxt.Value) & " )"
            HaveTrackingSEL = True
        End If
        If RequestedByComboBox.Value & "" <> "" Then
            If HaveStatusSEL Or HaveTrackingSEL Then FilterString = FilterString & " AND "
            FilterString = FilterString & "( [Purchase Orders].RequestedByID='" & RequestedByComboBox.Value & "' )"
            HaveRequestorSEL = True
        End If
        If DateGroup.Value <> 0 Then
            If HaveStatusSEL Or HaveTrackingSEL Or HaveRequestorSEL Then FilterString = FilterString & " AND "
            Select Case DateGroup.Value
                Case 1  ' Required Date
                    FilterString = FilterString & "( [Purchase Orders].RequiredDate"
                Case 2  ' Order Date
                    FilterString = FilterString & "( [Purchase Orders].OrderDate"
                Case 3  ' Last Modified Date
                    FilterString = FilterString & "( [Purchase Orders].LastModified"
            End Select
            If BeginDtTxt.Value & "" <> "" And EndDtTxt.Value & "" <> "" Then
                FilterString = FilterString & " BETWEEN #" & BeginDtTxt.Value & "# AND #" & EndDtTxt.Value & "# )"
            ElseIf BeginDtTxt.Value & "" <> "" Or EndDtTxt.Value & "" <> "" Then
                If BeginDtTxt.Value & "" <> "" Then
                    FilterString = FilterString & "=#" & BeginDtTxt.Value & "# )"
                ElseIf 0 Then
                    FilterString = FilterString & "=#" & EndDtTxt.Value & "# )"
                End If
            End If
            HaveDatesSEL = True
        End If
        If HaveStatusSEL Or HaveTrackingSEL Or HaveRequestorSEL Or HaveDatesSEL Then FilterString = FilterString & ")"
        
        If FilterString = " WHERE ()" Then FilterString = ""
        
        ConstructFilter = FilterString
    End Function

  4. #4
    Join Date
    Sep 2003
    Location
    MI
    Posts
    3,713
    And for those of you who want to see the form this filter function belongs to ... (I did have to reduce the resolution tho ...)
    Attached Thumbnails Attached Thumbnails filter.bmp  

Posting Permissions

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