Search button will not search on the subform

Anonymous
2015-09-28T14:06:18+00:00

I am just learning Access but have a fair knowledge of the basics so far. I had a table with all of my info I wanted to track and I had a form with a search button that would search everything. I watched the MS videos on database design and discovered that the best design was to take my one table and create 2 linked tables according to the video. I did this so that I did not have repeated customer data since some customers have adopted more than one dog from us. My database is very small, about 2MB. I  put 9 fields in my customer Table and 10 fields into my Dogs Table. I created a split form and added a search button to the Customers part (main form) but now when I search it only searches the main part of the form and not my Dogs sub form. Can someone please enlighten me as to how to make the search button search both forms? Everything is linked and works correctly otherwise, I just can't get it to search the sub form.

Microsoft 365 and Office | Access | For home | Windows

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments
Answer accepted by question author
ScottGem 68,840 Reputation points Volunteer Moderator
2015-09-29T16:29:34+00:00

Lets leave off the New dog name for the moment. It adds a level of complexity you don't need right now.

The Rowsource you show doesn't include the customer name. Lets go back to here:

RowSource: SELECT CustomersT.[Customer ID], DogsT.Dogs Name], Lastname & ", " & Firstname AS CustName 

FROM CustomersT INNER JOIN DogsT ON CustomersT.[Customer ID] = DogsT.[Customer IDFK];

ORDER BY [Dogs Name];

Bound Column: 1

Column Count: 3

Column Widths: 0";1.5";2"

Nothing is going to happen when you select a row in the combobox because you didn't do the last part. 

From my previous note:

In the After Update event of this combo I would use code like:

Me.Filter = "[Customer ID] = " & Me.cboSelectDog

DoCmd.runCommand acCMDApplyfilterSort

By the way its not a good idea to use spaces in object names. This will come back to haunt you.

Was this answer helpful?

0 comments
Answer accepted by question author
ScottGem 68,840 Reputation points Volunteer Moderator
2015-09-28T19:45:53+00:00

This part here is in my customers table   "For the third column enter: CustName: Lastname & ", " & Firstname."  So do I have to link that table using my customer ID and Customer IDFK fields from these 2 tables?

I put the Customer IDFK in this query because it is a part of the dogs table. I also used dogs name and dogs new name fields from Dogs Table.

I tried what I said above but the drop down box just lists the ID numbers, not sure if it is customers or dogs, running short on time for now

I truly appreciate you taking the time to help me on this issue!!

You are correct, I forgot about that. Here are the instructions I posted previously:

RowSource: SELECT CustomerID, Dogname, Lastname & ", " & Firstname AS CustName 

                    INNER JOIN ON Dogs.CustomerID = Customer.CustomerID

                    FROM Dogs, Customer

                    ORDER BY Dogname;

Bound Column: 1

Column Count: 3

Column Widths: 0";1.5";2"

As I said before. When you click the ellipses next to the RowSource it should open Query Design mode. You need to add both tables and join the CustomerID PK to the CustomerID FK. Then add the CustomerID and Dogname fields. then create the third column as noted. If you then switch to SQL view it should look like the SQL statement above.

The reason why its only listing the IDs is because you didn't adjust the Column count and Columns Width properties as shown. The first 2 properties are on the data tab, the other 2 are on the Format tab.

Was this answer helpful?

0 comments

61 additional answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Anonymous
    2015-11-05T16:49:32+00:00

    Okay, a few questions Scott. In the PersonsT (my CustomersT) the last field you have as PersonsTypeID (FK) but I do not see a PersonTypeID anywhere to link it to. Also, you have left my Notes field out, is that just an oversight on your part or intentional?

    So do I need another table named PersonTypeID and if so could you suggest how I lay it out too please? Will I be able after all of this is done to import the data or am I going to be doing a lot of data entry or should I just create a duplicate DB and rearrange it to meet your suggested format? A duplicate, since I do not wish to chance destroying my original work by accident.

    A comment: my AdoptionsT was my original table before I normalized it. And I believe you had mentioned the nature of relational databases. My intent of keeping it was purely for the ease with which I could search by any of the fields in my DogsT with much greater ease. I am willing to abandon that though since it isn't the correct way of doing things.

    Note: Not all dogs are microchipped was why I did not create a table for that, is that okay or do I need to address that in some way. I have been kind of busy here at work this past week and today is the first time I have had to sit down and try to digest this info and start the process of getting this right

    Was this answer helpful?

    0 comments
  2. ScottGem 68,840 Reputation points Volunteer Moderator
    2015-11-02T14:23:12+00:00

    I would love to be able to link all 3 and when I put

    information into 1 of them, have it automatically propagate to the other 2 tables (assuming that is possible)

    All of the other tables can stand independently I believe, just sort of an archive is all I need from them.

    First, this is incorrect thinking for a relational database. One of the main principles is that data does not repeat. Data exists in one table and one table only. As long as your tables are properly linked, you can pull data from the other tables as needed.

    And no, you should not have a separate DB. The data is all linked so should remain in the same db. And data rarely stands independently.

    I understand what you want to do. So you need one more table:

    ApplicationT

    ApplicationID (PK Autonumber)

    PersonID (FK)

    ApplicationDate

    ApplicationResult

    Notes

    This table keeps track of people who apply for an adoption and the result of that application. For your statistical analysis.

    I urge you to examine the structure I suggested. Yes the Adoptions table is the hub of your app. The Pets and People tables revolve around it, linking to it using the PetID and PersonID fields. The structure I gave you is normalized and efficient.

    Was this answer helpful?

    0 comments
  3. Anonymous
    2015-11-02T13:37:43+00:00

    @RunningManHD 

         The business we are running is very very small, a 2 person operation. We are a 501c3 non-profit organization  and we do not even take a salary of any type ourselves. We do not profit in any way and really it is a money pit for us. We have more money going out than we do coming in. Year to date we have spent well over $40,000 just on veterinarian bills alone. It is paid for in small part by the fees we charge for the dogs that get adopted out and the biggest portion of it comes from donor contributions. Whatever is left needing to be paid comes out of our pocket (My GF and myself). Now, considering that she got injured on the job almost a year ago exactly, and her hearing is set for this week, we lost over 60% of our income when she got hurt, she cannot collect unemployment since she filed a workman comp claim, so you can now see why there is no money in the budget to hire anyone to do anything., I am an IT professional myself, but not a DBA, I work in Networking. So I figure this stuff out as I go and am quite proud of what I have accomplished , even if it is very flawed. I am learning. But I do not plan on changing careers LOL. The only way we could "pay" for any help would be if the work was done for free and we could provide a form so that the fee could be claimed as a charitable contribution on the persons taxes  who performed the work. 

    @Scottgem

    I am glad to hear that my work so far is not completely in vain, and not as flawed some as others see it. I do not

    mind it being called flawed at all, the constructive criticism is much welcomed (and needed) by all who have offered

    it. I will keep plugging away until I can get it to where I want it. I am going to have to sit down today, and the rest of

    the week and try to focus on your suggestions , I do all of this from work since I have a lot of free time while I am at work.

    Some history of my work. As you have probably figured out. I started out with one table, the AdoptionsT

    I then normalized it by splitting it into the CustomerT and DogsT

    I created a table for donors that I called Bash Donors(we had a Barnyard Bash event, hence the name)

    We had a microchip clinic where we microchipped dogs so I created the Chip Clinic Table to track those

    And finally I tried importing the complete applications database from our website.But am now thinking I don't need the whole application since all I plan on using it for is statistical purposes, tracking the number of total applications

    versus the numbers of applications we deny( I am guessing we deny roughly 60% of all applications because our

    adoption standards are very strict. And I want to track that.

    My thoughts are this, at this moment and are subject to change, either with your suggestions, or as my ideas evolve:

    My main thing is that I would like to do is to have 3 "main" tables, the Adoptions (the original table) and the 2 that I split off from it when I normalized it (CustomersT and DogsT) I would love to be able to link all 3 and when I put

    information into 1 of them, have it automatically propagate to the other 2 tables (assuming that is possible)

    All of the other tables can stand independently I believe, just sort of an archive is all I need from them.

    Maybe I should create a separate DB for those? Is that a good thought?  

    Oh, and you asked why I had a field named Living, that is for customer communication purposes, either mail or email, I want to be able to send out "appropriate" communications with former adopters, and it is getting to be too much to keep in my head. That is why I went to the whole DB process in the first place.

    Was this answer helpful?

    0 comments