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

ScottGem 68,840 Reputation points Volunteer Moderator

2015-09-28T16:56:17+00:00

Okay, I just created a new blank form that doesn't have a sub form and now it works, so thank you for your help, it was much appreciated. I think I have what I want now

That sounds like you created a query joining the two tables and are using that as the Recordsource of your form. If so, that is NOT the answer to your problem. Since you have a 1 to many relationship between Customer and Dogs, then the proper way to present this in a form is with a mainform/subform. Using a multi-table query can cause further problems down the road.
Was this answer helpful?

0 comments No comments
Anonymous

2015-09-28T17:16:48+00:00

Speed bump for sure. The reason I liked the sub form was because it showed in a single record ALL of the dogs a customer owned. I may still have to go back and try the instructions you gave yet. Unless of course you can tell me how to make a regular form show all of the animals listed on one record as I was able to do with a sub form.

I created the find record button with the wizard and it searches both tables now,(in my new form) but as I said, I no longer get a customer record with all of their dogs, rather I get one customer record for each dog instead. So as I said I may have to go back and try the way you told me.

Right now my focus is on getting the database and forms to function in the manner in which I want it to, a single record for each customer, not each dog. I want to make the record show multiple dogs if that customer has more than one. It seems simpler to do that with the sub form, unless there is an easy way to make a form created from the blank form option show if a customer has multiple dogs. It keeps basing my records on the dogs table instead of the customers table it seems. I think that was how I ended up with the Dogs ID field, I was trying to compensate for that!

One thing is for sure, if I keep doing and redoing this, then it will help me learn it better for the long run. Right now it is looking like to get it to work the way I want it then I will have to do as you suggested. Unless as I said, I can find another way without the sub form. I keep experimenting but can't get it to do as I wish. I do NOT want repeated customer records and that is the problem I am running into now that I created the new form.

I am going to try it your way now. LOL
Was this answer helpful?

0 comments No comments
Anonymous

2015-09-28T17:24:53+00:00

New question now. I am trying to follow your instructions. I tried this in both design view and Layout view. I have the form header showing in my property sheet but I looked through all the listed properties and cannot find a row source option, is it possible it is named something else?

I have looked through each of the tabs
Was this answer helpful?

0 comments No comments