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: Newest
Most helpful Newest Oldest
  1. ScottGem 68,840 Reputation points Volunteer Moderator
    2015-09-30T12:02:58+00:00

    Ok, first I would not use the generic name assigned by Access for controls. This makes it harder to go back and understand the code. 

    But the problem is that cboSelectDog doesn't exist. That is what the error message is telling you. Also a Combobox doesn't have a filter property. The code you are using is not what I gave you.

    So your code should be:

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

    To explain this further. Access is an object oriented development platform. Everything is an object and you make things happen by manipulating the properties of objects. This manipulation can be done manually in design mode, through the Access interface or through code. 

    So Filter is a property of the Form object. The Me. refers to the currently open form So the line is saying to set the value of the Filter for the current form to some expression. A Filter is a string expression that takes the syntax; fieldname operator value. In this case fieldname is Customer ID, the operator is = and the value is the value returned by the combobox Combo99. A filter says to only show records that match the expression. So, when you select a dog's name in the combobox, the Customer ID is returned because the Bound Column is 1, the first column. So lets say you select Fido who belongs to Customer ID 5, the Filter will be:

    [Customer ID] = 5

    When you apply the filter using the second line, the form will only show the record for Customer ID 5. The subform will then display the dogs associated with that customer.

    Was this answer helpful?

    0 comments
  2. Anonymous
    2015-09-30T11:38:46+00:00

    Here is a screen shot of my code plus the error I get. The first line is highlighted so I am guessing that is where the error lies maybe? But that line of code was automatically generated when I opened the code builder and I put the code you gave me between that and the end sub line. My box is named Combo98 by the system, I substituted that for the Me. that you used. I feel like I am so close to getting this to work. I am feeling hopeful that today is the day. The first line of code was highlighted in yellow with an arrow on the left, I have now figured that is the debugger at work but it just sits there and never does anything else so I am now assuming that the text highlighted in blue is the issue.

    Was this answer helpful?

    0 comments
  3. Anonymous
    2015-09-30T11:22:07+00:00

    I have a form with a sub form, ctrl F will only search the main form and not the sub form. That is the problem I am trying to solve. I am very close right now with the help of Scottgem I think I am on the last step of the solution with the code builder.

    Was this answer helpful?

    0 comments