Page 1 of 2 12 LastLast
Results 1 to 15 of 18
  1. #1
    Join Date
    Jul 2011
    Location
    Austria, Europe
    Posts
    15

    Question Unanswered: help needed for search function (sample DB attached)

    Hello everybody,

    Thanks to this great forum, I have been able to set up a database (samples of which I have already uploaded in various stages of development in other threads, whenever I hit a problem I couldn’t solve) for storing the minutes of management meetings, which is my main task during my summer internship. I am particularly grateful for Sinndho’s help in creating the search function.

    Before I continue, I’d like to point out that I uploaded a sample database (user interfaces are translated into English for your convenience) to help illustrate my problem. I hope it will also help to clarify the information below.

    So, this is what I had come up with so far:

    A database with

    Two tables:

    - tblSitzung (stores general meeting details like date, particpants etc.)
    - tblThemen (stores the actual meeting log)

    A query

    Three forms:

    - “frmEinstieg”: the form that pops up upon opening the database, and allows the user to open the search form and the main database form
    - “frmSuche”: the search form
    - “frmSitzung2”: main database form, can be opened via “frmEinstieg”; consists of one subform (“ufoThemen2”, gathers data from “tblThemen”); the text search is conducted in the fields of this subform .


    Now my boss wanted me to add a new feature to the database. Each point of the agenda should be linked to a new subform displaying a task associated with it, plus an employee assigned to it, the date it is due, and a status field indicating whether the task has already been done.

    So I’ve created a new table (“tbAufgaben”) and a new subform (“ufoAufgaben2”) to be displayed in the main database form “frmSitzung2”, alongside the already existing subform “ufoThemen2”. The table and subform have the columns “task”, “Assigned to”, “date” and “done” (a true/false field with a checkbox).

    The tricky part is, that my boss also wants to have a search function specifically for this new subform (“ufoAufgaben2”)

    I have already designed a search form (“frmAufgaben”). The major problem of course now is making this form work with appropriate code.

    The search form consists of the following four textfields:

    - task
    - assigned to
    - two for “date” : one for “from” and one for “until”

    there is also one option box, where the user can choose whether he wants to search for unsolved tasks, tasks that were accomplished, or both.

    So, the search form should work like this:

    if a word is entered into the “task” field, all datasets containing the word should be found (it should search the column “task” of the subform/table)

    if a name is entered into the “assigned to” field, all datasets containing the person in the “assigned to” column should be found

    if two dates are entered into the boxes of “date”, all datasets for that period of time should be found.

    also, the search should observe the option box, and only yield accomplished, open, or all tasks.

    the searches should also be able to interact with each other in a sense that if you for example enter a task, a name and a date, plus tick the box “to do”, the search should yield the results that match all these criteria.

    Upon running the search with the button go, the results should be shown using the form “frmSitzung2” just like it is the case with the existing search form.

    Your assistance would be very much appreciated.

    Thank you very much in advance!

    Best wishes,

    Ian
    Attached Files Attached Files

  2. #2
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Here's a routine to assemble the criteria that can be used to filter a form or subform. As the names of the controls in frmAufgaben are not significant, I used their Tag property to relate them to the columns in the table tblAufgabe, like this:
    Code:
    Control Name Control Tag
    ---------------------------
    Text1         Aufgabe
    Text4         Beauftragter
    Text10        Datum_1
    Text14        Datum_2
    Rahmen24      Erledigt
    I hope I'm right in the correspondences (my knowledge of the German language is very poor), change if necessary.
    Code:
    Private Sub Befehl33_Click()
    
        Dim ctl As Control
        Dim strCriteria As String
        
        strCriteria = "1 = 1"
        For Each ctl In Me.Controls
            If ctl.Tag <> "" Then               ' Control might be used to assemble the criteria.
                If Nz(ctl.Value, "") <> "" Then ' User typed something in this control --> use it in the criteria.
                    Select Case ctl.Tag
                        Case "Aufgabe", "Beauftragter"
                            strCriteria = strCriteria & " AND [" & ctl.Tag & "] Like '*'" & ctl.Value & "*'"
                        Case "Datum_1"
                            If IsDate(ctl.Value) Then strCriteria = strCriteria & " AND [Datum] >= #" & Format(ctl.Value, "mm/dd/yyyy") & "#"
                        Case "Datum_2"
                            If IsDate(ctl.Value) Then strCriteria = strCriteria & " AND [Datum] <= #" & Format(ctl.Value, "mm/dd/yyyy") & "#"
                        Case "Erledigt"
                            If ctl.Value = 1 Then
                                strCriteria = strCriteria & " AND [" & ctl.Tag & "] = True"
                            ElseIf ctl.Value = 2 Then
                                strCriteria = strCriteria & " AND [" & ctl.Tag & "] = False"
                            End If
                        Case Else
                            '
                            ' Not handled here.
                            '
                    End Select
                End If
            End If
        Next ctl
        
    End Sub
    Have a nice day!

  3. #3
    Join Date
    Jul 2011
    Location
    Austria, Europe
    Posts
    15

    thank you very much

    Thank you very much! The code you supplied looks pretty complete to me. Could you perhaps briefly describe what I still need to to in de section you labelled "not handled here" ?

  4. #4
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    The Case Else is there for the case where the Tag property of other controls in the form would be used for another purpose, which the procedure does not handle. It could be used to raise an event, change the value of a status variable or initiate another process (assemble another string criteria, for instance).
    Have a nice day!

  5. #5
    Join Date
    Jul 2011
    Location
    Austria, Europe
    Posts
    15
    I see, thank you very much!

  6. #6
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    You're welcome!
    Have a nice day!

  7. #7
    Join Date
    Jul 2011
    Location
    Austria, Europe
    Posts
    15
    I'm sorry to bring this up again. After some trying that led nowhere I was relcutant to admit that I don't have enough programming knowledge (in fact, none at all, looking at VBA code I might as well try to read Chinese) to work out a solution based on the input you provided, also because I didn't want to bother you again.

    So I took this to another forum, where another helpful user was so kind as to do the following extensions to your code:

    Code:
    Private Sub Befehl33_Click()
     Dim ctl As Control
        Dim strCriteria As String
        
        strCriteria = "1 = 1"
        For Each ctl In Me.Controls
            If ctl.Tag <> "" Then               ' Control might be used to assemble the criteria.
                If Nz(ctl.Value, "") <> "" Then ' User typed something in this control --> use it in the criteria.
                    Select Case ctl.Tag
                        Case "Aufgabe", "Beauftragter"
                            strCriteria = strCriteria & " AND Forms!frmSitzung2!UfoAufgaben2.Form![" & ctl.Tag & "] Like '*" & ctl.Value & "*'"
                        Case "Datum_1"
                            If IsDate(ctl.Value) Then strCriteria = strCriteria & " AND [Datum] >= #" & Format(ctl.Value, "mm/dd/yyyy") & "#"
                        Case "Datum_2"
                            If IsDate(ctl.Value) Then strCriteria = strCriteria & " AND [Datum] <= #" & Format(ctl.Value, "mm/dd/yyyy") & "#"
                        Case "Erledigt"
                            If ctl.Value = 1 Then
                                strCriteria = strCriteria & " AND Forms!frmSitzung2!UfoAufgaben2.Form![" & ctl.Tag & "] = True"
                            ElseIf ctl.Value = 2 Then
                                strCriteria = strCriteria & " AND Forms!frmSitzung2!UfoAufgaben2.Form![" & ctl.Tag & "] = False"
                            End If
                        Case Else
                            '
                            ' Not handled here.
                            '
                    End Select
                End If
            End If
        Next ctl
        
    
        strCriteria = Mid(strCriteria, 11)
        If strCriteria > "" Then DoCmd.OpenForm "frmSitzung2", , , strCriteria
     
    End Sub

    This code does open the desired form, but it's totally empty, even when you enter search words that are definitely in the database. Plus, if you try to do a search filling in all variables ("assigned to, date, task etc.) there's some kind of error.

    I really hate to do this, it's got nothig to do with being lazy, but I'm really lacking the necessary knowledge to go the whole nine yards on my own. Could you, if you find the time to do so, please complete the code so it's good to go? If you don't have time that's perfectly fine, but I guess it would still be easier for you than for someone else to work with code they haven't written themselves, that's why I came back here. Thank you so very much!

  8. #8
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Oops, sorry!

    I lost from view that the search must be performed for matches in the data set of the subform ufoAufgabe2, not in the main form frmSitzung2. Applying the criteria to the main form is useless in that case. I'll post a corrected version very soon.
    Have a nice day!

  9. #9
    Join Date
    Jul 2011
    Location
    Austria, Europe
    Posts
    15
    Thank you very much for your efforts! This is very much appreciated!

  10. #10
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    You're welcome!

    Here's a corrected version of the database. Let me know if it's ok.
    Attached Files Attached Files
    Have a nice day!

  11. #11
    Join Date
    Jul 2011
    Location
    Austria, Europe
    Posts
    15
    Thank you very much! I really appreciate your efforts, and I am very sorry that I need to come back once more.

    Here is what works fine:

    - if a keyword is entered, only the meeting in question is displayed (e.g. if you enter “Cox” in the “assigned to” field, only the meeting that took place on August 20th is displayed, not the other meeting that does not contain this search word)

    However, the search is not refined. It does bring up the correct meeting, but it still displays too much unnecessary data, and the “Status” checkboxes don’t seem to work. For instance, if you type in “Cox” and tick “done”, the result form should only display the dataset in the subform “ufoAufgabe2” that matches these criteria plus the point on the agenda in the other subform “ufoThemen2” it is attached to. Instead, it displays all the other points on the agenda, and all the other tasks as well. So, in practice when a meeting has many points on the agenda, the user would be faced with a whole agenda (which can be very long) and would have to scroll through the data of a whole meeting to find the proper search results.

    To illustrate this with the sample database:

    When you search for “cox” in the “assigned to” field and tick the “done” box in the status field, then the search yields the correct meeting (20.8.2011) and the correct point on the agenda (another series etc.) this particular task it is related to. However, in the subform “ufoAufgabe2”, where only the task of Dr Cox should be displayed in accordance with the search criteria, another task assigned to another person is also shown. Moreover, the other point on the agenda of this meeting (“introduction”) is also displayed, alongside the data in the subform “ufoAufgabe2”) although it is completely unrelated to the search (there is a task assigned to Dr Cox, but it doesn’t have the status “done” and thus should not be displayed.

    Moreover, in practice it would be very common that the user simply wants to tick the box “to do” in the search form to look for all open tasks without specifically entering a person assigned to a particular task or a task itself. When you do that, the results form does open, but again, it displays every task, no matter if it has actually been marked as “done” or not.

    Last but not least, if you try to refine the search entering a date in the respective fields, it yields the following error message (translated from German, I hope you’ll know what it means)

    runtime error 3075

    Syntax error in “Datum” in query expression ‘1=1 AND [Datum] > = # (the date entered)# AND [Datum] < = #(the date entered)# AND [Erledigt] = false’.


    The debugger then highlights the following line:

    Code:
    Set rst = CurrentDb.OpenRecordset(strSQL, dbOpenSnapshot)

    I hope this clarifies the problem.

    I’m sorry that the issue still isn’t resolved. I appreciate your patience!

  12. #12
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    No need to feel sorry: you're welcome!

    1. I would not say that the search is not refined. The search finds every record in the main form (frmSitzung2) for which an entry exists in the subform (ufoAufgaben2). But you're right when you say that it does not restrict the contents of this subform to the matching records (I never thought of it). This can easily be done by adding 4 lines of code in the Sub Befehl33_Click() procedure (in red):
    1)
    Code:
        Dim strCriteria As String
        Dim strCriteria2 As String
        Dim strSQL As String
    2)
    Code:
        Next ctl
        
        ' 0. Save the assembled criteria (it will be used as a filter for the subform [ufoAufgaben2]).
        '
        strCriteria2 = strCriteria
        
        ' 1. First complete the criteria.
        '
        strCriteria = "WHERE " & strCriteria
    3 & 4)
    Code:
        If Len(strINList) > 0 Then
        
            ' Complete the filter for opening  the form frmSitzung2
            ' with only the rows matching the sitzung_id's in our list.
            '
            strCriteria = "sitzung_id IN ( " & strINList & " )"
            
            ' We can now open the form frmSitzung2 with the filter we just composed.
            '
            DoCmd.OpenForm "frmSitzung2", , , strCriteria
            
            ' Allow time and processor resources for the form to be fully open.
            '
            DoEvents
            
            ' Apply the filter to the subform.
            '
            Forms("frmSitzung2").ufoAufgaben2.Form.Filter = strCriteria2
            DoCmd.Close acForm, Me.Name
        Else
    2. I suspect that the problem with the open/closed tasks has the same origin. I'll investigate and let you know about it.

    3. The problem with the dates renders me more perplex. Could you please send the error message as it is, at least the full 'query expression' part of it?
    Have a nice day!

  13. #13
    Join Date
    Jul 2011
    Location
    Austria, Europe
    Posts
    15
    Thank you very, very much!

    As far as the issue with the dates is concerned, I was able to resolve it with some research on my own. The problem was the date format.

    The old code line read:

    Code:
    
    Case "Datum_1"
              If IsDate(ctl.Value) Then strCriteria = strCriteria & " AND [Datum] >= #" & Format(ctl.Value, "mm/dd/yyyy") & "#"
    Case "Datum_2"
                If IsDate(ctl.Value) Then strCriteria = strCriteria & " AND [Datum] <= #" & Format(ctl.Value, "mm/dd/yyyy") & "#"

    The new code with the new date format reads as follows:

    Code:
    Case "Datum_1"
             If IsDate(ctl.Value) Then strCriteria = strCriteria & " AND [Datum] >= " & Format(ctl.Value, "\#yyyy\-mm\-dd\#")                    
    
    Case "Datum_2"
             If IsDate(ctl.Value) Then strCriteria = strCriteria & " AND [Datum] <= " & Format(ctl.Value, "\#yyyy\-mm\-dd\#")

    It now works fine.

    Moreover, the search using the status "all" now works fine as well.

    In other words, the search function is now bug-free as far as I can tell I will continue testing it throughout the day, but I don't expect to run into any further trouble

    The filter you implemented to the subform "ufoAufgaben2" works like a charm. Restricing the contents of this subform to the matching records is exactly what I had in mind.

    Would it be possible to do the same to the subform "ufoThemen2" ? So that the content of this subform (the respective point on the agenda) is only displayed when there actually is a corresponding record(the task that goes with this point on the agenda) in the "ufoAufgaben2" that matches the search criteria?

    Right now, if you for instance search for the term "cox" in the "assigned to" field and tick the "done" box, the search will correctly produce the form "frmSitzung2" containing the meeting that took place on August 20th.

    The record in the subform "ufoAufgaben2" which matches the search criteria is correctly displayed (other non-matching records are correctly not displayed), so is the point on the agenda in the subform "ufoThemen2" it is linked with.

    The only issue now is that other points on the agenda in the subform "ufoThemen2" are displayed as well, although the fields in the subform "ufoAufgaben2" they are linked to do not contain data that matches the search criteria, and the subform is thus empty, which somehow renders the data in the subform "ufoThemen2" not-matching and unnecessary as well.

    So, another filter applied to the subform "ufoThemen2" to accomplish the desired restrictions outlined above would be the only improvement to functionality I could still think of This would really be great!

    Thank you for coming this long way with me! All that is left to say is: almost there

  14. #14
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    For the problem with the dates I was rather sure that it was a question of format, that's why I asked for the full error message. I'm glad you could solve it, though.

    Filtering the subform ufoThemen2
    Again we'll have to assemble a string containing a filter that we shall apply to the subform ufoThemen2. Fortunately it's easy because we can retrieve the matching themen_id values in the same loop as the one we use to retrieve the sitzung_id values. And again a few supplemental lines will perform the required filtering (changed lines in red, new lines (or parts of line) in blue).

    1.
    Code:
        Dim strCriteria As String
        Dim strCriteria2 As String
        Dim strCriteria3 As String
        Dim strSQL As String
        Dim strINList_sitzung As String
        Dim strINList_themen As String
    Declaring strCriteria3 is not stricly necessary: we could re-use one of the existing strings (strCriteria or strCriteria2).

    2.
    Code:
        ' 2. Create the Select query for the temporary RecordSet.
        '
        '    2011-08-30 themen_id was added to the data set: we now create 2 lists
        '    ---------- (one for sitzung_id and one for themen_id).
        '
        strSQL = "SELECT DISTINCT tblThemen.themen_id, tblThemen.sitzung_id_f AS sitzung_id " & _
                 "FROM tblThemen INNER JOIN tblAufgabe ON tblThemen.themen_id = tblAufgabe.themen_ID_f " & _
                 strCriteria & ";"
    We now have both themen_id and sitzung_id_f in the data set.

    3.
    Code:
        ' 3. Open the temporary RecordSet, loop through it and assemble the list of matching sitzung_id.
        '
        '    2011-08-30 Assemble both lists: strINList_sitzung and strINList_themen.
        '    ----------
        Set rst = CurrentDb.OpenRecordset(strSQL, dbOpenSnapshot)
        With rst
            Do Until .EOF
                If Len(strINList_sitzung) > 0 Then strINList_sitzung = strINList_sitzung & ","
                strINList_sitzung = strINList_sitzung & !sitzung_id
                If Len(strINList_themen) > 0 Then strINList_themen = strINList_themen & ","
                strINList_themen = strINList_themen & !themen_id
                .MoveNext 
           Loop
            .Close
        End With
    The loop now assembles 2 lists, one for themen_id and one for sitzung_id.

    4.
    Code:
            ' Apply the filters to both subforms (ufoAufgaben2 and ufoThemen2).        '
            Forms("frmSitzung2").ufoAufgaben2.Form.Filter = strCriteria2
            
            ' 2011-08-30  Also apply a filter created from strINList_themen
            ' ----------   to the subform ufoThemen2.
            '
            strCriteria3 = "themen_id IN ( " & strINList_themen & " )"
            Forms("frmSitzung2").ufoThemen2.Form.Filter = strCriteria3
            DoCmd.Close acForm, Me.Name
    Have a nice day!

  15. #15
    Join Date
    Jul 2011
    Location
    Austria, Europe
    Posts
    15
    Thank you very much! The complete code now reads as follows:

    Code:
    Private Sub cmdSuche_Click()
    'Suche starten
    
    Dim rst As DAO.Recordset
        Dim ctl As Control
        Dim strCriteria As String
        Dim strCriteria2 As String
        Dim strCriteria3 As String
        Dim strSQL As String
        Dim strINList_sitzung As String
        Dim strINList_themen As String
        
        strCriteria = "1 = 1"
        For Each ctl In Me.Controls
            If ctl.Tag <> "" Then               ' Control might be used to assemble the criteria.
                If Nz(ctl.Value, "") <> "" Then ' User typed something in this control --> use it in the criteria.
                    Select Case ctl.Tag
                        Case "Aufgabe", "Beauftragter"
                            strCriteria = strCriteria & " AND [" & ctl.Tag & "] Like '*" & ctl.Value & "*'"
                        Case "Datum_1"
                            If IsDate(ctl.Value) Then strCriteria = strCriteria & " AND [Datum] >= " & Format(ctl.Value, "\#yyyy\-mm\-dd\#")
                        Case "Datum_2"
                            If IsDate(ctl.Value) Then strCriteria = strCriteria & " AND [Datum] <= " & Format(ctl.Value, "\#yyyy\-mm\-dd\#")
                        Case "Erledigt"
                            If ctl.Value = 1 Then
                                strCriteria = strCriteria & " AND [" & ctl.Tag & "] = True"
                            ElseIf ctl.Value = 2 Then
                                strCriteria = strCriteria & " AND [" & ctl.Tag & "] = False"
                            End If
                        Case Else
                            '
                            ' Not handled here.
                            '
                    End Select
                End If
            End If
        Next ctl
        
           ' 0. Save the assembled criteria (it will be used as a filter for the subform [ufoAufgaben2]).
        
        strCriteria2 = strCriteria
    
        ' 1. First complete the criteria.
        '
        strCriteria = "WHERE " & strCriteria
        
        ' Find all rows in Tblsitzung which is the the Master table (i.e. the table
        ' that's on the top of the relationships) that match the criteria.
        '
        ' 2. Create the Select query for the temporary RecordSet.
        '
        ' 2011-08-30 themen_id was added to the data set: we now create 2 lists
        '    ---------- (one for sitzung_id and one for themen_id).
        '
    
        strSQL = "SELECT DISTINCT tblThemen.themen_id, tblThemen.sitzung_id_f AS sitzung_id " & _
                 "FROM tblThemen INNER JOIN tblAufgabe ON tblThemen.themen_id = tblAufgabe.themen_ID_f " & _
                 strCriteria & ";"
    
        ' 3. Open the temporary RecordSet, loop through it and assemble the list of matching sitzung_id.
        
        '2011-08-30 Assemble both lists: strINList_sitzung and strINList_themen.
        '    ----------
    
        Set rst = CurrentDb.OpenRecordset(strSQL, dbOpenSnapshot)
        With rst
            Do Until .EOF
                If Len(strINList_sitzung) > 0 Then strINList_sitzung = strINList_sitzung & ","
                strINList_sitzung = strINList_sitzung & !sitzung_id
                If Len(strINList_themen) > 0 Then strINList_themen = strINList_themen & ","
                strINList_themen = strINList_themen & !themen_id
                .MoveNext
            Loop
            .Close
        End With
        Set rst = Nothing
        
        ' 4. We now have the list of sitzung_id matching our search.
        '    If the list is empty --> No match.
        '    Open the form frmSitzung2 with a filter on the matching rows
        '    or display an information message if no matches were found.
        '
        If Len(strINList) > 0 Then
        
            ' Complete the filter for opening  the form frmSitzung2
            ' with only the rows matching the sitzung_id's in our list.
            '
            strCriteria = "sitzung_id IN ( " & strINList & " )"
            
            ' We can now open the form frmSitzung2 with the filter we just composed.
            '
            DoCmd.OpenForm "frmSitzung2", , , strCriteria
            
            ' Allow time and processor resources for the form to be fully open.
            '
            DoEvents
            
            ' Apply the filters to both subforms (ufoAufgaben2 and ufoThemen2).
            '
            Forms("frmSitzung2").ufoAufgaben2.Form.Filter = strCriteria2
            ' 2011-08-30  Also apply a filter created from strINList_themen
            ' ----------   to the subform ufoThemen2.
            '
            strCriteria3 = "themen_id IN ( " & strINList_themen & " )"
            Forms("frmSitzung2").ufoThemen2.Form.Filter = strCriteria3
    
            DoCmd.Close acForm, Me.Name
        Else
        
            ' Nothing in the list --> No match.
            '
            MsgBox "Es wurden keine Daten gefunden, die den angegebenen Kriterien entsprechen.", vbInformation, "Suche abgeschlossen"
        End If
        
    
    End Sub

    Unfortunately, there's still one - I hope minor - issue. When I run the debugger, the message "variable not defined" pops up, and the following (marked blue) segment is highlighted:


    Code:
     ' 4. We now have the list of sitzung_id matching our search.
        '    If the list is empty --> No match.
        '    Open the form frmSitzung2 with a filter on the matching rows
        '    or display an information message if no matches were found.
        '
        If Len(strINList) > 0 Then
        
            ' Complete the filter for opening  the form frmSitzung2
            ' with only the rows matching the sitzung_id's in our list.

Posting Permissions

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