How to search for records in a subform within a form?

In the "On Click" event of your combo box you can have something like this:

Me.NameofYourSubForm.Form.Filter = "YthId=" & Me.ythIdCombo & ""
Me.NameofYourSubForm.Form.FilterOn = True

This is what I typed in:

Me.sbfrmLogTable.Form.Filter = "YthID=" & Me.ComboLogYth & ""
Me.sbfrmLogTable.Form.FilterOn = True

When I try it out, it tells me that it cannot find the Macro "Me".

don't put it in a macro.  This should be in your VBA code.

Is there a way to do it without VBA? I don't know how to use VBA.

I typed:

Me.sbfrmLogTable.Form.Filter = "YthID=" & Me.ComboLogYth & ""
Me.sbfrmLogTable.Form.FilterOn = True

on the right of the "On Click" field under the "Event" tab in the "Properties" window for the combo box.

Thanks, lucas911.

OK,

My first advise is that you stop using Macro and start writting VB codes for these action. I looks and sound difficult when you first start but believe me, it is so much more powerful than macro that you won't look back. You can ask every expert on this panel and I can bet that non of them use macro anymore.

Back to your question

To create a subform do the following:

1) create the subform as a normal form
2) open the main form in design view.
3) select the subform icon and use it to create the subform control on your main form. Then select the form that you created in step one as the source object of the subform control. During this process you will also be asked to select the child and master link. Select the YthID on the main form (the combo) and YthID on the subform as the child master link.

That's your form/subform done.

Now all you have to do is refresh the subform every time you change the value of the combobox. To do this follow the following steps:

1)Open the main form in design view.
2) right click on the combo box and select property.
3) In the property dialog box go to the event tab. Then go to On Change and type event procedure next to it. Then double click on the box on the right hand side of this textbox.
4) The code editor window will open and your cursor will be between the following

Private Sub ComboYth_Change()


End Sub

Type the following code before end sub

Me.SubformName.Form.Refresh

------------------

NB: the subform name is called child1,child2 .... by default. It is always good to rename them to something meaningful to help with your coding. To find the name of a child right click on it in design view. Go to the other tab and the name will be in the name textbox


 

<Is there a way to do it without VBA? I don't know how to use VBA.>

That was me 5 years a go. I soon found out that there was no way to achieve the stuffs I wanted to achieve without vba. You are about figure that out too. It  will be a steep learning curve but achievable. I am self thought and I believe everybody can do it if they put there mind to it.

sb9 don't be too hard on them ;-)

Here is a macro way on your ComboBox:


Action: ApplyFilter
Where Condition: YthID= ComboLogYth
Control Name: sbfrmLogTable

OK Lucas, I am sure that terrak will find this in his own time like we all did.

 Coool

Thanks, sb9.

I was able to insert the form as a subform, but it didn't ask me for the child/master link. Instead, I was able to right click on the subform and go to the Properties dialog box and click on the Data tab where I can set the Child and Master links. However, when I click on the "..." it tells me: "Can't build a link between unbound forms." So I went ahead and typed "YthID" on both child and master link fields.

Subform Properties/Data (frmLogTable)

Source Object: frmLogTable
Link Child Fields: YthID
Link Master Fields: YthID

I typed this on VBA for the combo box under On Change:

Private Sub ComboLogYth_Change()
Me.frmLogTable.Form.Refresh
End Sub

Then I go to "Form View" and it asks me to enter a parameter value for YthID. I click OK without inputting anything and nothing happens--when I select the different YthID's on the search combo box, it does not change the YthID on the subform (shown as a table).


SIDENOTE: When I go on the dashboard and click on tables, I see the master table, I can see the Contact Log table as a subtable. I think this is so because I linked the YthID's through relational tables. Not sure if this affects the child/master links on the subform.

Good start!

<Then I go to "Form View" and it asks me to enter a parameter value for YthID>

This is telling me that a field by this name does not exist in either the form or the subform. Check the subform and make your that it has a field with datasource YthID. Or maybe the related datasource has another name. If so change the Link Child Fields to that field name.

<when I select the different YthID's on the search combo box>

As I said before the default name of a subform control is child#. It is a common rockie error (got me confused and frustrated before I figured it out) to confuse between the subform and the subform source object. In this case your source object is frmLogTable. Your subform has another name. Open the property dialog box of the subform (the one which contains child/master links) and check the name in the other tab. I suggest that you change it to chdLogTable.

Then your code is:

Private Sub ComboLogYth_Change()
Me.chdLogTable.Form.Refresh
End Sub

If refresh don't work try

Private Sub ComboLogYth_Change()
Me.chdLogTable.Form.Requery
End Sub

<SIDENOTE: When I go on the dashboard and click on tables, I see the master table, I can see the Contact Log table as a subtable. I think this is so because I linked the YthID's through relational tables. Not sure if this affects the child/master links on the subform.>

That will cause a subform to be automatically generated if you create an auto form from the main table. But otherwise it should not affect anything.

When assigning the child master link use the dropbox in the dialog and it should give you a list of available fields from the form and subform.

The field YthID exists within the subform. And it is being referred to by the combo search box. I made sure to create a 8 records and I added some contact logs, just to give the database a try.

So, I'm following your lead sb9, but it's not quite working for me.

The subform's name was frmLogTable (as it says on the Database/Dashboard) now is chdLogTable. After going to the form containing the subform, I went to the properties of the subform and changed the source object to chdLogTable.

I went to VBA and this is what is says:

Private Sub ComboLogYth_Change()
Me.chdLogTable.Form.Requery
End Sub

When I go to Form View, it asks me to enter the parameter value for YthID. I click OK, then I can view the form. When I search with the combo search box, nothing changes on the table below it. Except that I get the error message on the image below.


error.JPG

lucas911,

I'm giving Macro a try as well. And this is what's going on. After I add:

Action: ApplyFilter
Where Condition: YthID= ComboLogYth
Control Name: sbfrmLogTable

to the combo search dialog box and I view it on Form View, I can't click on any of the names on the list.

sb9,

When I try to see the available fields to add to the child/master links, it tells me

Can't build a link between unbound forms.

This is why I typed in YthID on both. But it keeps asking me for a parameter value.. even though YthID does exist as a field on the table where the subform is coming from.

Ok Check out this page on form subform reference:

http://www.mvps.org/access/forms/frm0031.htm

<When I go to Form View, it asks me to enter the parameter value for YthID> IT should not be asking you for this unless the combo box is unbounded. Actually this must be what is happening. Which means that there is no control with YthID as its datasource in the main form.

Try inserting a text box in the main form header and use YthID as its data source.

Then change the onchange event of the combobox to:

Private Sub ComboLogYth_Change()

DoCmd.FindRecord Me.ComboLogYth

End Sub

This will cause the main form to go to the record that you have selected in the combobox. This should be seen in the new textbox (you can make it invisible once its all working) and change the subform.

<even though YthID does exist as a field on the table where the subform is coming from>

That doesn't matter. It must exist as the data source of a control on the main form

Thanks, sb.

I ended up doing something different.

I embedded the subform through the subform wizard. I added it to the main working form where the combo search box has been working well. The child/master link recognizes YthID now and when I go to Form View, it allows me to filter and see the contact logs of specific people (YthID).

Thanks for helping. Since I tried the different approaches but none of them worked for me, how should I go about closing this question? I'm new in E-E so I just want to make sure that I don't do anything unfair, but at the same time I don't want to select any one answer as the solution because it didn't work for me.. Should I just rate the comments?

Thanks again for your help.

You're right, and that's true. Your way worked though I interpreted as something else. Thank you!