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-10-29T16:06:14+00:00

Here is my blank DB

https://onedrive.live.com/redir?resid=ECC1EF85FF6BD974!247&authkey=!ANh9ixX_1pfQu3A&ithint=file%2caccdb
Was this answer helpful?

0 comments No comments
Anonymous

2015-10-29T19:00:17+00:00

Greg, I have looked at your database briefly and I am sorry to say, but you have bigger problems than just trying to get a search to work on your form/sub form. Your table structure is all wrong and creates redundant data which is a big no, no in relational database design.

Your table design is the most crucial phase of building a database application. Your tables are the heart of everything. If they are not correct, then nothing else will be either and you will spend countless hours trying to work around the problems caused.

The entire reason I provided you with the database sample in my earlier post was to give you a foundation for correctly building your own system. At the moment you are a good distance off track.

Because you intend to use this for a business, it would be important to build your system correctly the first time around instead of fumbling about trying to learn as you go. It would be in your best interest to consider a professional developer for your project. I am available to do this for you as are many other professionals here.

Please get back and let me know your thoughts. If you would like to chat off line, you can email me at shooter0167 at Hotmail.
Was this answer helpful?

0 comments No comments
Anonymous

2015-10-29T19:25:28+00:00

As RunningManHD points out there are fundamental problems with your logical model. You can correct the immediate problem as below, but this is really only moving the deckchairs on the Titanic:

1. Change the RowSource property of the Combo98 control to:

SELECT DogsT.ID, DogsT.[Dogs Name], CustomersT.[Last Name]

FROM CustomersT INNER JOIN DogsT

ON CustomersT.[Customer ID]=DogsT.[Customer ID]

ORDER BY DogsT.[Dogs Name];

2. Change the After Update event procedure of the Combo98 control to:

Private Sub Combo98_AfterUpdate()

    Dim strFilter As String

    If Not IsNull(Me.Combo98) Then

        strFilter = "[Customer ID] IN (SELECT [Customer ID] FROM DogsT WHERE ID = " & Me.Combo98 & ")"

        Me.Filter = strFilter

        Me.FilterOn = True

    Else

        Me.FilterOn = False

    End If

End Sub

3. Add the following as the AfterUpdate event procedure of the DogsT Subform form:

Private Sub Form_AfterUpdate()

    Me.Parent.Combo98.Requery

End Sub

This will filter the form to a single customer. Clearing the combo box will show all customers. To filter to multiple customers with dogs of the selected name:

1. Change the RowSource property of the Combo98 control to:

SELECT DogsT.[Dogs Name]

FROM DogsT

ORDER BY DogsT.[Dogs Name];

2. Change the combo box's ColumnCount property to 1 and laeve its ColumWidths property empty.

3. Change the After Update event procedure of the Combo98 control to:

Private Sub Combo98_AfterUpdate()

    Dim strFilter As String

    If Not IsNull(Me.Combo98) Then

        strFilter = "[Customer ID] IN (SELECT [Customer ID] FROM DogsT WHERE [Dogs Name] = """ & Me.Combo98 & """)"

        Me.Filter = strFilter

        Me.FilterOn = True

    Else

        Me.FilterOn = False

    End If

End Sub

4. Add the following as the AfterUpdate event procedure of the DogsT Subform form:

Private Sub Form_AfterUpdate()

    Me.Parent.Combo98.Requery

End Sub
Was this answer helpful?

0 comments No comments