Running a Multiple Items Form off a Standard Form

[Redbeard]

I am running Access 2010 and have an understanding of VBA Code, but not much coding experience. I would like to know if there is any way of running a Multiple Items Form off a Stander Form. To explain, I have a main form for my database that displays all my records 1 at a time. The form has around 50 fields and a large photo for each record. I also have a Multiple Items Form that shows six fields and a small thumbnail photo for each record that I use for quick compares. What I would like to do is use my main form to filter for groups of records (in any of the 50 fields), then open and view them on the Multiple Items Form. That way I can quickly compare records as they are all on the same page. So my question is, is this possible and if so does it require code or can it be done with queries? I have done many searches on this but can’t come up with anything. Any suggestions?

[ADezii]

Here is how this can be accomplished:

1. Make an exact Copy (CTL+C ==> CTRL+V) of your Form and Name it frmFind.
2. Make sure the Control Names of all Controls on frmFind are exactly the same as the Control Sources for these Controls.
3. DELETE the Control Sources of all Controls on frmFind, I'm assuming these are Text Boxes.
4. Add a Command Button with the Caption of Find on this Form.
5. This will now be your Search Form. It is an exact Copy of your Main Form, but all the Fields are Blank, and the Form itself has no Record Source.
6. Enter your Criteria in 1 or more Fields on frmFind.
7. Add the following Code to the Click() Event of your Find Button.
   

   Dim ctl As Control
   Dim strBuild As String
    
   For Each ctl In Me.Controls             'Loop thru all Controls on Form
     If ctl.ControlType = acTextBox Then   'Only interested in Text Boxes
       If Not IsNull(ctl) Then             'Text Box must have a Value in it
         'Start building your Criteria String
         If IsNumeric(ctl.Value) Then      'Number or String?
           strBuild = strBuild & "[" & ctl.Name & "] = " & ctl.Value & " AND "
         Else
           strBuild = strBuild & "[" & ctl.Name & "] = '" & ctl.Value & "' AND "
         End If
       End If
     End If
   Next
    
   'Strip " AND " from the end of strBuild (last 5 Characters)
   strBuild = Left$(strBuild, Len(strBuild) - 5)
    
   DoCmd.OpenForm "frmMultipleItems", acNormal, , , acFormReadOnly, acWindowNormal
    
   'Change the Record Source of the Form based on the pre-defined Criteria
   Forms!frmMultipleItems.RecordSource = "SELECT * FROM tblEmployees WHERE " & strBuild
    

8. The Code will:
   1. Loop thru all the Controls on your Find Form.
   2. Filter for only Text Boxes that are NOT NULL.
   3. Build a Criteria String based on whether the Value(s) in the Text Boxes are Numeric or Strings.
   4. Strip Non-essential Characters from the Criteria String.
   5. Open frmMultipleItems.
   6. Dynamically set the Record Source of frmMultipleItems to reflect the Criteria that was just built.
9. If you have any questions, please feel free to ask and one of us will help you. I have attempted to keep the solution as simple as possible. Perhaps someone else will come along with a more practical approach.

[Redbeard]

Hi ADezii
Thanks for the post. This is not exactly what I was looking for, but it does do exactly what I asked for it to do. So my fault for not explaining myself better. The problem is, due to the specific way I am doing the searches and the way my fields are set up. Here are the problems:
1. Half of my fields are list boxes or combo boxes, some of which do lookups on other tables.
2. Usually when I do a search in the database I start with one term and work down. So I would do a text filter on one field and then see how many results I get, if too many do another text filter on another field and so on. Sometimes they are filters for a words and other they are excluding words until I get down to what I want. So I don’t know what terms I am using until the search is done.
So I guess what I am looking to do is have it set up so that when I have done multiple filters and/or sort to get what I want on my main form, I click a button (on Main form) and my multiple list form come up with the exact same filters/sorts as my main form. I assume to accomplish that it would have to run off the same query that the main form is? I am just not sure how to link them up or if this is even possible? If you have any other idea’s let me know.

Thanks
P.S. I will use the code that you sent in another part of my database, it is really good. Thanks!

[Redbeard]

Found the answer. All I needed to do was add a button to my Main Form with the code below. It takes whatever I have filter for and sorted on my Main Form and opens up my Multiple List Form with the same filters and sorts.

DoCmd.OpenForm "frmMyForm"
Forms!frmMyForm.Filter = Replace(Me.Filter, "[MyQurey].", "")
Forms!frmMyForm.FilterOn = True
Forms!frmMyForm.OrderBy = Replace(Me.OrderBy, "[MyQurey].", "")
Forms!frmMyForm.OrderByOn = True