Search button will not search on the subform

Anonymous

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.

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 Volunteer Moderator

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.

0 comments

Answer accepted by question author

ScottGem 68,840 Volunteer Moderator

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.

0 comments

61 additional answers

Anonymous

2015-09-28T16:06:26+00:00

Okay, it is going to take me a while to digest the info you just gave me, it is kind of more advanced than what I have done so far . I just created a search button(Find Records) was how I did my searches and I would really like for that to search both the main and sub form. As for the ID and Dogs ID fields, the ID is the auto number, I think that when I exported my old table to excel then imported it back to Access after dividing up my table I ended up with the 2 of those, I think when I was trying to set up the foreign key the first time was how I ended up with Dogs ID, I used the wrong field. I really just want my search button to work . I apologize for using the wrong terminology in my original post, I am still learning. **Isn't there a simple solution to make my search button search both the main and sub form without going through all the combo box stuff?**I am trying to keep this as simple as possible because I intend to share it online in the near future with a couple of our volunteers and they are not very computer savvy. You can see my search(Find Records) button in the upper right corner of my screen shot. And by the way, Access seems family simple to learn as far as just the basics go. I have truly enjoyed this experience so far!
Was this answer helpful?

0 comments No comments
ScottGem 68,840 Reputation points Volunteer Moderator

2015-09-28T15:46:54+00:00

OK, Sorry, but it wasn't clear from your original post whether you had done all that.

What you don't show us is how you are doing the search. The way I would do this is to have 2 Combo boxes in the Header of your form. The first combo would be to search for a customer. I would use the Combobox wizard and create a Search combo (3rd Option) for that. It will allow you to select a customer and position the record pointer for that customer. For the dog search its a little bit trickier.

For that I would not use the wizard to create the combo. I would set the Relevant properties like so:

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"

This combo will display the dog names along with their customer.

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

Me.Filter = "[CustomerID] = " & Me.cboSelectDog

DoCmd.runCommand acCMDApplyfilterSort

That should then filter the form for the customer and the subform would display the dogs.

By the way, Why do you have an ID and DogID field.

Also, the Breeds should be stored in a child table. Whenever you have fields numbered like Breed1, Breed2, etc. That is called a repeating group and should be in a child table.
Was this answer helpful?

0 comments No comments
Anonymous

2015-09-28T14:52:32+00:00

I have the tables linked and they work fine, I have the foreign key linked to the customer ID and it functions as it should. I want my search button to search each table because s ometimes I don't know the customers name and I need to search by the dogs name instead in order to find the customers name. As I said it all works perfectly except my search button only searches the customers table and not the dogs table. I don't think that I need to create any other tables, my Dogs table contains ID, Dogs ID Dogs Name, New dogs name for when the customer changes their dogs name, Age which is a combo box with a drop down that has Puppy, Adult, and Senior as the options, I don't put in the actual age, I have a Y/N check box for if the dog is still living, a Microchip field, Adoption Date, Adoption Fee,Customer IDFK linked to customer ID,Breed 1, Breed 2 Breed 3 for dogs that are mixed breeds. and that is it.

And yes I did use a form wizard and created a sub form when I did that. Here is a screen shot of my form:
Was this answer helpful?

0 comments No comments