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-28T19:17:50+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!!
Was this answer helpful?

0 comments No comments
Anonymous

2015-09-28T19:08:40+00:00

I am trying to figure out what I am doing wrong, I am leaving here in a few minutes to go home and probably will be busy the rest of the day so I may not be back on until 7am eastern tomorrow. I can get the combo box created(using wizard) and I can select from a drop down list, but nothing happens when I do. Let me see if I am doing this right, I create the combo box with the wizard then close the wizard without going through the setup (next, next) stuff, and try to enter the values for row source you gave me, but the only options in row source are tables and queries. I will be here for about 20 moire minutes then I will have to work on it tomorrow. any Idea what I may be doing wrong?
Was this answer helpful?

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

2015-09-28T18:57:18+00:00
I am getting closer. Is there a way to initiate a search by typing in a dogs name instead of a drop down list? And is having the drop down for customers necessary, my "Find Records" searches everything in the customers table so I can use that instead for this part of my search, and since I can search by any field and even partial words in the field, I kind of like using that for my customers search portion.? What I am asking is can I just put a combo box in the header for just the dogs table?Or do I have to have both of them because of some relational issue?

No you don't have to use the combo. If you are satisfied with using the Find feature for the main form, feel free to use it.

You can just use a text box and type the dog's name in, but a combobox is easier for the following reasons:
1. You can still just start to type the dog's name, the Auto Expand feature will find the first instance of a matching value so you don't have to type the whole name. Its not necessary to scroll thru the list to make a selection.
2. The Limit To List property will not allow the user to enter a name that is not in the database. If you just use a textbox, then you have to provide code that will inform the user that the name doesn't exist.
3. It will require more coding to use a text box then a combo.
4. As I said before, comboboxes are used extensively in user interfaces. Anyone who uses a computer at all is familiar with them and how they work.
"When I select row source the only options I get are tables and queries, do I select the dogs table, I am not getting this to work"

If you select the ellipses [...] to the right of the Rowsource property, it opens Query Design mode. Select the Dogs table then select the CustomerID field and the Dogname field. For the third column enter: CustName: Lastname & ", " & Firstname.

Note: use the actual field names.

Set the Dogname column as your Sort column. Then set the other properties as I indicated previously.
Was this answer helpful?

0 comments No comments