Results 1 to 13 of 13
  1. #1
    Csoraparu is offline Novice
    Windows 10 Access 2016
    Join Date
    Aug 2020
    Posts
    7

    Search box on Sub Form to be filtered by Main form


    I'm very new to Access and I'm trying to set up a very simple database to track my customer's and calls. I used the Contacts template in Access and modified it to meet my needs. The last bit that I'm trying to figure out is how to have a Search Box in a sub form but have it controlled by a field in the parent form. The parent form is my customer contact information, name, city, state ...., so they primary key for that is just ID. The sub form tracks my calls for that particular customer and has a date field, description of the call, and a large text field for my call notes. The sub form and parent form are linked by the ID field. So, right now I can change the customer and the notes only for that particular customer show up in the sub form. I want to add a search box to the sub form so I can search by the date or by the short description. I did add a Combo Box to the sub form and I can do a search but it brings up all the calls for every customer not just the customer that is currently in the main form. How do I have the search box only show results for calls that are for the customer ID in the parent form?

  2. #2
    Gicu's Avatar
    Gicu is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    Add criteria to the combo's row source to limit the results to the current ID: "Select tblCalls.* from tblCalls WHERE ID = Forms!frmYourMainForm!ID;". Now in the Enter event of the combo box just add one line:
    Me.cboYourComboBoxName.Requery 'this ensures the combo gets refreshed when moving from record to record in the main form

    Cheers,
    Vlad Cucinschi
    MS Access Developer
    http://forestbyte.com/

  3. #3
    Csoraparu is offline Novice
    Windows 10 Access 2016
    Join Date
    Aug 2020
    Posts
    7
    Could you assist with what you described if I attach my database for you to see?

  4. #4
    Gicu's Avatar
    Gicu is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    Of course!

  5. #5
    Csoraparu is offline Novice
    Windows 10 Access 2016
    Join Date
    Aug 2020
    Posts
    7
    This is what I have entered but its not working. I'm sure its something that I did on my end. Can you please help and point out my error?

    SELECT CommReportT.*
    FROM CommReportT
    WHERE ((("ID=Forms!Contact Details!ID;")<>False));

  6. #6
    Csoraparu is offline Novice
    Windows 10 Access 2016
    Join Date
    Aug 2020
    Posts
    7

    CRM Database

    here is a copy of my db. I'm trying to add the search box to the CommReport Form and have it be linked to the ID that is on the Contact Details Form. I would like to be able to search by the date or by the description. I would also like to add a similar search box to the CustomerProductList so I can search by the size, shape, grade, or finish

    I do appreciate the help. I'm going nuts trying to figure this out.
    Attached Files Attached Files

  7. #7
    Gicu's Avatar
    Gicu is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    Please have a look at the CommReport form and see if that is what you're after. You only have one record per ID so it only shows one date or description. Notice I changed the record source of the subform to remove the Like 'Select...' from the criteria as that was wrong. The master/child linking fields (ID) are already doing what you were attempting. For the products form I used a filter approach.

    Cheers,
    Attached Files Attached Files
    Vlad Cucinschi
    MS Access Developer
    http://forestbyte.com/

  8. #8
    Csoraparu is offline Novice
    Windows 10 Access 2016
    Join Date
    Aug 2020
    Posts
    7
    Thank you for the help and for teaching me how to do this. It's great to know that there are still nice people like you out there!!!!

  9. #9
    Gicu's Avatar
    Gicu is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    You're very welcome, good luck with your project!

    Cheers,
    Vlad
    Vlad Cucinschi
    MS Access Developer
    http://forestbyte.com/

  10. #10
    Csoraparu is offline Novice
    Windows 10 Access 2016
    Join Date
    Aug 2020
    Posts
    7
    Can I ask one more favor of you? In my Contact List form I would like to have the Contact Date for each contact but only the last contact date. How would I set that up?

  11. #11
    Gicu's Avatar
    Gicu is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    You can add a new field to the form's record source (the Contacts Extended query) and use dMax to get the latest date:
    LatestContactDateMax("[ContactDate]","[commReportT]","[ID]=" & [ID]). Then simply add this calculated field to your form.

    Here is your sample with this added.

    Cheers,
    Attached Files Attached Files
    Vlad Cucinschi
    MS Access Developer
    http://forestbyte.com/

  12. #12
    Csoraparu is offline Novice
    Windows 10 Access 2016
    Join Date
    Aug 2020
    Posts
    7
    Thank you and I will give this a shot. I do have a general question for you about access databases. Someone told me that they are not that stable and once you have amassed too much data it may crash. Is any this true?

  13. #13
    Gicu's Avatar
    Gicu is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    Please have a look at the attachment in my previous post, I have added the text box for you at the end of the customer list. As for Access being unstable with large amount of data I believe that it is an "urban myth", especially among older IT people. I have been doing Access development since 1996 (Access 2) and, while there were some problems during this time as with any piece of software, I can tell you confidently that you should not have any problems with Access and too much data unless you are talking million records plus and bad database design. A properly designed Access db running on a stable network will be able to handle your data for many years to come.

    Cheers,
    Vlad
    Vlad Cucinschi
    MS Access Developer
    http://forestbyte.com/

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 28
    Last Post: 01-29-2018, 06:40 PM
  2. Search on a form that is filtered with a combobox
    By Tambe257 in forum Programming
    Replies: 1
    Last Post: 04-21-2017, 12:51 PM
  3. Replies: 8
    Last Post: 09-14-2015, 09:38 PM
  4. Search Form with Combo Box - Filtered
    By Varda in forum Forms
    Replies: 6
    Last Post: 06-14-2013, 07:24 PM
  5. Replies: 3
    Last Post: 08-10-2012, 10:02 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums