Page 1 of 2 12 LastLast
Results 1 to 15 of 24

Searching subform from main form using keyword search

  1. #1
    Mike79 is offline Novice
    Windows 10 Access 2016
    Join Date
    Feb 2019
    Posts
    18

    Searching subform from main form using keyword search

    Hi,



    I'm pretty new to access and have been learning via youtube and forums. A quick background of the back end - there is a table with the company name, website, address, etc. Then a second table with the actual contact names (first, last, email) associated with each company (as companies can have multiple points of contacts) with a one to many relationship.

    I created a split form that includes a few search options (using a combo box to search by state, division or company specialty, and also a keyword search box to search by company name or description). The results are filtered using the DoCMd.ApplyFilter function. Below is my code to filter after update of my keyword search. What I would like to do, is also be able to search by first or last name, which is on my subform. However, I have gone in circles and in many different directions without success. Any help is greatly appreciated. (Side note, I have also tried to break up this line of code using &_ without success....which is why it is one long line). The below code is searching the company name and trade description, while looking to see if a state, division, and specialty is selected (or null).

    Private Sub KW_AfterUpdate()
    DoCmd.ApplyFilter "", "([Company Name] Like ""*"" & [Forms]![NVSF]![KW] & ""*"" OR [TrdDesc] Like ""*"" & [Forms]![NVSF]![KW] & ""*"") AND ([State] Like ""*"" & [Forms]![NVSF]![cboSt] & ""*"" or [State] Is Null) And ([Division] Like ""*"" &[Forms]![NVSF]![CboDiv]& ""*"" Or [Division] Is Null) And ([Specialty] Like ""*"" &[Forms]![NVSF]![cboSp] & ""*"" Or [Specialty] Is Null)", ""
    End Sub

  2. #2
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    5,883
    not a big user of split forms myself so not 100% sure of this but
    split forms are for seeing a single and datasheet view at the same time, as in, maybe put 3, 4, etc. fields on the "single" portion and the rest of the fields in the data sheet view. The end result is that all of it is from 1 domain (query or table). This is not what you have (if for sure it's a split form)

    What you need is a main form for the single record with a subform for the many.
    - "doesn't work" is no help. Post err msgs and where.
    - Use code tags for code/sql. Implement changes in copies of your database.
    Sometimes I talk to myself - like when I need expert advice.

  3. #3
    Mike79 is offline Novice
    Windows 10 Access 2016
    Join Date
    Feb 2019
    Posts
    18
    Thank you for the reply. Yes, what I have is a split form that has search queries on top. Then a 'single' view and a data sheet view. The datasheet view is populated but the fields in the 'single' view (I believe this in an inherent property of access split form). I have a subform in my single view with the 2nd table of contact names/emails. Everything works perfect, and I can search through company names. What I can't do is search through contact names, which are on the subform. Does that make sense? I attached an image. YOu can see my search options up top. I can search by company name or in the notes box, just fine. I can't figure out how to search the first name/last name in the sub form.

    Click image for larger version. 

Name:	example.jpg 
Views:	26 
Size:	133.8 KB 
ID:	38719

  4. #4
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    5,883
    well that's one I haven't seen yet - a split form with a subform on it. If you're saying that the searches you CAN do are against the subform and the one(s) you cannot are also on the subform then I have no idea. However, if the working searches are on the datasheet of the split form and the non-working ones are on the sub, then maybe your references to the subform are wrong. The syntax is

    [Forms]![Main form name]![subform control name].[Form]![control name on subform]

    where subform control name is the container/control that contains the subform. If none of that helps, I think you will have to post a copy of your db that has been compacted then zipped. If data is sensitive, you can run update queries against any that must remain in order to replicate the problem and delete the rest.
    - "doesn't work" is no help. Post err msgs and where.
    - Use code tags for code/sql. Implement changes in copies of your database.
    Sometimes I talk to myself - like when I need expert advice.

  5. #5
    Mike79 is offline Novice
    Windows 10 Access 2016
    Join Date
    Feb 2019
    Posts
    18
    Yes, my applyfilter command works for the main form [form name NVSF], but not for my subform [form name POCSF]. For example, with the following code:

    DoCmd.ApplyFilter "", "[Company Name] Like ""*"" & [Forms]![NVSF]![KW] & ""*"" OR [TrdDesc] Like ""*"" & [Forms]![NVSF]![KW] & ""*""", ""

    This code works. It will see what I type in my Keyword box, and check the company name and Trade Description for those words. However, I tried the following code and it does not work (is this the syntax you were getting at?) It wants me to enter a parameter value for First Name.

    DoCmd.ApplyFilter "", "[Forms]![NVSF]![POCSFCtrl].[Form]![FirstName] Like ""*"" & [Forms]![NVSF]![KW] & ""*"" OR [Forms]![NVSF]![POCSFCtrl].[Form]![LastName] Like ""*"" & [Forms]![NVSF]![KW] & ""*""", ""

  6. #6
    Mike79 is offline Novice
    Windows 10 Access 2016
    Join Date
    Feb 2019
    Posts
    18
    It's possible I have just gone about this from the beginning in the wrong way. My goal was to have one table of companies (with company name, address, website, etc). And another table with point of contact (first name, last name, email, etc). This way each company could be assigned many POC's.

    Then, I simply wanted a user to be able to search by a number of choices (state, specialty, point of contact name, company name) and return a list of results. Honestly, I don't even need the 'single' detail section in the middle. I considered covering that up and just showing the data sheet list of results. The goal is someone can say, I need a plumber in Texas. They search for texas, plumber, and can start calling everyone on the list.

    I can go back to the drawing board if necessary.

  7. #7
    Mike79 is offline Novice
    Windows 10 Access 2016
    Join Date
    Feb 2019
    Posts
    18
    I have a copy of the database I can post, with most of the vendor information gone, but I'm not sure how to do that? It's only a 664kb file

  8. #8
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    5,883
    if POCSFCtrl is the name of the subform control then it looks right to me. However, you're in unfamiliar territory with a subform on a split form. If I ever tried it, it would strictly be out of curiosity. I don't think is is anything I would ever use.
    You could post your db http://www.accessforums.net/showthread.php?t=70301

    but his sounds like it would be far more helpful to you - look for "search form" under "applications"
    http://allenbrowne.com/tips.html

    You may not need one as robust as that, but it will provide good guidance

  9. #9
    Mike79 is offline Novice
    Windows 10 Access 2016
    Join Date
    Feb 2019
    Posts
    18
    Thanks. Ill check out that link and maybe post my db.

    So, if a subform on a splitform is not common, what is the proper way to do what I am trying to do (searching a number of companies and returning the list of results, including any associated contacts related on a second table)? This must be a common goal?

  10. #10
    Mike79 is offline Novice
    Windows 10 Access 2016
    Join Date
    Feb 2019
    Posts
    18

    Attaching Database

    See attached. Notes in the VBA code in the after update code.
    Attached Files Attached Files

  11. #11
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    5,883
    Quote Originally Posted by Mike79 View Post
    So, if a subform on a splitform is not common, what is the proper way to do what I am trying to do (searching a number of companies and returning the list of results, including any associated contacts related on a second table)? This must be a common goal?
    Based on my interpretation of that and considering a customer - orders scenario
    - one method is where you put whatever controls are relevant to the "one" side on a main form. This means data fields (so you can see pertinent customer details) and any unbound controls needed to filter customers. On a subform of the main form, you put the orders form to show orders related to the displayed customer

    - or use a search form to do all the filtering and open a single form for orders. The disadvantage to the latter method is that to display another customer's details you have to go back to the search form.

  12. #12
    orange's Avatar
    orange is offline Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    13,910
    Mike,
    A few things for consideration:
    - use names that do NOT use embedded spaces nor special chars --anything not alpha or numeric eg "(3-+= etc..."
    -every table in a relational database should have a primary key
    -I recommend you set up referential integrity in your relationships window

    Create a draft data model of the things/entities/main subjects involved in your proposed application.
    If it's Customer-Order-Product scenario, then model that to show application scope --even if you have to focus on only a part of the application at this time.

    In vba a line break character is _ but must be preceded with at least 1 space.

    What exactly is a POC in your business?

    Do you have a clear description of what this database is intended to support? If so, please post it. I'm trying to determine the scope of the application.


    Do you have sample data " Plumber in Texas" and some sample scenarios that can be used to test/vet your design?

  13. #13
    Mike79 is offline Novice
    Windows 10 Access 2016
    Join Date
    Feb 2019
    Posts
    18
    Thank you for all your help, I'll do my best to answer your questions.

    I'm not quite sure what you mean my "referential integrity in your relationships window."

    POC = Point of Contact.

    Summary of the goal of the database: We are a contractor that has relationships with many subcontractors with different specialties around the country. We have many specialties, but you'll see in mine some are Concrete, Electrician, Fencing, Etc . There are hundreds if not thousands. Each one of these subcontractors can have multiple people we speak with. So Electrician A, may have 4 different people (Points of Contact, [POCs]), that we may want to email.

    I need to have a database so if we have a job in texas, someone can search texas, then search electricians, and see a list. Or perhaps they just want to search a specific company, they can. All this functionality works currently.

    I have since run into times when managers will only know the name of the person they speak with, but aren't even sure the exact company name. I know that sounds odd, but it happens. So I wanted to add functionality to search by first or last name of the contacts.

    For you to test, open the NVSF form. Search "BUrns" in the keyword search and you will see Burns & McDonnell will show. Same thing if you search by state and use GA.

    However, if you type the name "David" or "Craig" I want it to pull up any companies where the contacts have the name David or Craig.

  14. #14
    orange's Avatar
    orange is offline Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    13,910
    So Electrician A, may have 4 different people (Points of Contact, [POCs]), that we may want to email.
    My understanding of this:

    Subcontractor "Electrician A" has 4 identified people (qualified electricians) that may be available for tasks/jobs.

    Referential integrity refers to the accuracy and consistency of data within a relationship. In relationships, data is linked between two or more tables. ... So, referential integrity requires that, whenever a foreign key value is used it must reference a valid, existing primary key in the parent table.

    Can you lead us through a scenario indicating the process:
    - what triggers the need for subcontractor and workers,
    - who does what exactly to find resources,
    - what exactly happens when x resources are identified,
    - then what??

    How did/are you identifying Specialty? Are you working from some standard industry classification scheme?
    Who is expected to maintain the data?

    You have a table in your queries that is not defined NADVendorT
    Last edited by orange; 06-14-2019 at 07:39 PM.

  15. #15
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    5,883
    So, referential integrity requires that, whenever a foreign key value is used it must reference a valid, existing primary key in the parent table.
    I think it's more than just that. Integrity is maintained through cascade updates and/or cascade deletes so that if the parent data changes, the foreign related field values in the related tables are also changed or deleted, as the case may be. In the case of deletions, the entire child/related record is deleted IIRC, otherwise you'd have fields in a record that no longer relate to anything (AKA orphaned record, which would also describe a record where the foreign key value was not altered or removed but no longer matches the parent value). You can have a foreign key value reference a valid, existing primary key in the parent table without referential integrity options set.
    - "doesn't work" is no help. Post err msgs and where.
    - Use code tags for code/sql. Implement changes in copies of your database.
    Sometimes I talk to myself - like when I need expert advice.

Page 1 of 2 12 LastLast
Please reply to this thread with any new information or opinions.

Similar Threads

  1. Searching a subform field from the main form
    By mindbender in forum Queries
    Replies: 2
    Last Post: 11-01-2018, 12:16 PM
  2. Keyword Search from Subform
    By jdowning in forum Forms
    Replies: 5
    Last Post: 05-20-2016, 07:47 AM
  3. Replies: 4
    Last Post: 02-05-2016, 02:32 PM
  4. Searching by field on subform on main form
    By helen21112010 in forum Forms
    Replies: 1
    Last Post: 10-16-2012, 07:55 AM
  5. Replies: 3
    Last Post: 08-22-2012, 03:28 AM

Posting Permissions

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