multi criteria Form Search?

[jackie77]

Hi all

Has anyone ever come across an example of a form where you can carry out a multi criteria search which not only displays the results on a subform but when you select an item from that subform the details can be displayed in text boxes etc on the main form.
I have tediously searched this forum and the web but all search examples only display on a subform only, is it even possible if so has anyone found any examples or how would I go about achieving this

Thanks Jackie

 

[neileg]

Sounds you need a two stage process. Set up your search form. Then use the selcted record in the results to use as criteria for a query that populates the main form.

 

[jackie77]

Hi thanks for the reply!

that sound like exactly what I want but not sure how I would go about that, I have successfully managed the first part (see code belwo) but sorry new to this how do I go about using the record I select in the subform to run a query to populate the main form?

Jackie

 

[jackie77]

Sorry forgot to add the code

Option Compare Database
Option Explicit

Private Sub btnClear_Click()
    Dim intIndex As Integer
    
    ' Clear all search items
    Me.txtBooked_Date = ""
    Me.cmbEngineer = 0
    
    
End Sub

Private Sub btnSearch_Click()

    ' Update the record source
Me.frmsubClients.Form.RecordSource = "SELECT * FROM jobsheetquery " & BuildFilter
    ' Requery the subform
    
    Me.frmsubClients.Requery
    
End Sub


Private Sub Form_Load()
    
    ' Clear the search form
    btnClear_Click
    
End Sub

Private Function BuildFilter() As Variant
    
 

    Dim varWhere As Variant
    Dim varItem As Variant
    Dim intIndex As Integer

    varWhere = Null  ' Main filter
    
    
   
    
     ' Check for LIKE Last Name
    If Me.txtBooked_Date > "" Then
        varWhere = varWhere & "[BookedDate] LIKE """ & Me.txtBooked_Date & "*"" AND "
    End If
    
    ' Check for Engineer
    If Me.cmbEngineer > 0 Then
        varWhere = varWhere & "[EngineerID] = " & Me.cmbEngineer & " And "
    End If
    
    
    ' Check if there is a filter to return...
    If IsNull(varWhere) Then
        varWhere = ""
    Else
        varWhere = "WHERE " & varWhere
        
        ' strip off last "AND" in the filter
        If Right(varWhere, 5) = " AND " Then
            varWhere = Left(varWhere, Len(varWhere) - 5)
        End If
        
         End If
    
    
    Debug.Print varWhere
BuildFilter = varWhere
   
    
End Function