Results 1 to 5 of 5
  1. #1
    Join Date
    Jul 2011
    Location
    Austria, Europe
    Posts
    15

    Question Unanswered: problems with adapting search form code to newly added form

    Hello everybody,

    I've got a simple database (with two tables, "tblSitzung" and "tblThemen") with a search form. The search form (the code for which was supplied by Sinndho, a very helpful user in this forum to whom I am greatly indebted) works just fine. When the search word was found, the search form opens the main results form (frmSitzung) which has one subform ("ufoThemen")

    I would now like to replace the old form "frmSitzung" with a new one ("frmSitzung2") which now has two subforms ("ufoAufgaben2" and "ufoThemen2") as I added another table ("tblAufgaben"). In other words, the search form should now open up the new form "frmSitzung2" instead of the old "frmSitzung".

    I've desperately tried to make things work, but simply renaming the objects in the code somehow didn't do the trick. Also, I have no Idea how to adapt the code for the command buttons "print" and "print preview".

    I will upload a sample database to illustrate my problem.

    Any assistance would be greatly appreciated.
    Attached Files Attached Files

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

    code

    I should have thought of that earlier, here are the code sections that need to be adapted, so you don't have to look for them in the sample database.

    search form code:

    Code:
    Private Sub Command_Go_Click()
    
        Dim strSearch As String
        Dim strSQL As String
        Dim strMatches As String
        Dim rst As DAO.Recordset
        Dim fld As DAO.Field
        Dim i As Integer
        
        If IsNull(Me.Text_Search.Value) Then
       MsgBox "Bitte geben Sie einen Suchbegriff ein!"
       Text_Search.SetFocus
       End If
        
        If Len(Nz(Me.Text_Search.Value, "")) Then
            strSearch = "*" & Trim(Me.Text_Search.Value) & "*"
            strSQL = "SELECT tblSitzung.sitzung_id " & _
                          ", tblSitzung.sitzung_datum " & _
                          ", tblSitzung.sitzung_art " & _
                          ", tblSitzung.sitzung_teilnehmer " & _
                          ", tblThemen.themen_id " & _
                          ", tblThemen.themen_fragestellung " & _
                          ", tblThemen.themen_input " & _
                          ", tblThemen.themen_ergebnis " & _
                       "FROM tblSitzung INNER JOIN tblThemen ON tblSitzung.sitzung_id=tblThemen.sitzung_id_f;"
            Set rst = CurrentDb.OpenRecordset(strSQL, dbOpenSnapshot)
            With rst
                Do Until .EOF
                    For Each fld In .Fields
                        If Nz(fld, "") Like strSearch Then
                            If Not InStr(strMatches, rst!sitzung_id) Then
                                If Len(strMatches) > 0 Then strMatches = strMatches & ", "
                                strMatches = strMatches & !sitzung_id
                            End If
                        End If
                    Next fld
                    .MoveNext
                Loop
                .Close
            End With
            Set rst = Nothing
            If Len(strMatches) Then
                DoCmd.OpenForm "frmSitzung", , , "sitzung_id IN ( " & strMatches & " )"
                Forms!frmSitzung.[Unterformular].Form.Filter = "[themen_fragestellung] LIKE '*" & Me.Text_Search.Value & "*' OR [themen_input] LIKE '*" & Me.Text_Search.Value & "*' OR [themen_ergebnis] LIKE '*" & Me.Text_Search.Value & "*'"
                Forms("frmSitzung").Sought = Me.Text_Search.Value
                Forms!frmSitzung.AllowEdits = False
                Forms!frmSitzung!Unterformular.Form.AllowEdits = False
            Else
                MsgBox "Keine Ergebnisse für den Suchbegriff:  " & Me.Text_Search.Value, vbInformation, "Suche abgeschlossen"
                Text_Search.SetFocus
            End If
        End If
        
        
    End Sub

    Print preview button code:

    Code:
    Private Sub Command_Preview_Click()
    
        DoCmd.OpenReport "Rpt_Search_Results", acViewPreview, , Me.Filter
        
    End Sub
    Print button code:

    Code:
    Private Sub Command_Print_Click()
    
        DoCmd.OpenReport "Rpt_Search_Results", acViewNormal, , Me.Filter
        
    End Sub

    I hope these are the only relevant code sections.

  3. #3
    Join Date
    Mar 2009
    Posts
    5,441
    Provided Answers: 14
    I've added Sample_DB_English-2.mdb to the zip with 2 new forms: frmEinstieg2 and frmSuche2.

    I don't have a lot of time right now so this was done in a hurry. Please ask again if it's not what you're looking for : I'll have more time during the weekend (or maybe sooner) to analyze the needed modifications in detail.
    Attached Files Attached Files
    Have a nice day!

  4. #4
    Join Date
    Jul 2011
    Location
    Austria, Europe
    Posts
    15
    Thank you very, very much for your help! This looks very good at a first glance, I'll try to implement it in the database with the real data. Should any issues arise, I'll try to provide a very detailed description of the problem.

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

Posting Permissions

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