By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
426,060 Members | 1,893 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 426,060 IT Pros & Developers. It's quick & easy.

Running a Multiple Items Form off a Standard Form

100+
P: 112
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?
Jun 6 '13 #1

✓ answered by 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.
Expand|Select|Wrap|Line Numbers
  1. DoCmd.OpenForm "frmMyForm"
  2. Forms!frmMyForm.Filter = Replace(Me.Filter, "[MyQurey].", "")
  3. Forms!frmMyForm.FilterOn = True
  4. Forms!frmMyForm.OrderBy = Replace(Me.OrderBy, "[MyQurey].", "")
  5. Forms!frmMyForm.OrderByOn = True

Share this Question
Share on Google+
3 Replies


ADezii
Expert 5K+
P: 8,623
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.
    Expand|Select|Wrap|Line Numbers
    1. Dim ctl As Control
    2. Dim strBuild As String
    3.  
    4. For Each ctl In Me.Controls             'Loop thru all Controls on Form
    5.   If ctl.ControlType = acTextBox Then   'Only interested in Text Boxes
    6.     If Not IsNull(ctl) Then             'Text Box must have a Value in it
    7.       'Start building your Criteria String
    8.       If IsNumeric(ctl.Value) Then      'Number or String?
    9.         strBuild = strBuild & "[" & ctl.Name & "] = " & ctl.Value & " AND "
    10.       Else
    11.         strBuild = strBuild & "[" & ctl.Name & "] = '" & ctl.Value & "' AND "
    12.       End If
    13.     End If
    14.   End If
    15. Next
    16.  
    17. 'Strip " AND " from the end of strBuild (last 5 Characters)
    18. strBuild = Left$(strBuild, Len(strBuild) - 5)
    19.  
    20. DoCmd.OpenForm "frmMultipleItems", acNormal, , , acFormReadOnly, acWindowNormal
    21.  
    22. 'Change the Record Source of the Form based on the pre-defined Criteria
    23. Forms!frmMultipleItems.RecordSource = "SELECT * FROM tblEmployees WHERE " & strBuild
    24.  
  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.
Jun 6 '13 #2

100+
P: 112
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!
Jun 10 '13 #3

100+
P: 112
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.
Expand|Select|Wrap|Line Numbers
  1. DoCmd.OpenForm "frmMyForm"
  2. Forms!frmMyForm.Filter = Replace(Me.Filter, "[MyQurey].", "")
  3. Forms!frmMyForm.FilterOn = True
  4. Forms!frmMyForm.OrderBy = Replace(Me.OrderBy, "[MyQurey].", "")
  5. Forms!frmMyForm.OrderByOn = True
Jun 12 '13 #4

Post your reply

Sign in to post your reply or Sign up for a free account.