Filter Report with SubForm after filtering from Form - Confused already?

[Ryman]

Hi everyone,

I wonder if you are able to assist me at all?

I am using Access 2010 and am attempting to build an internal skills database for my organisation.

I am almost there, but am struggling with the final hurdle...

I have designed a form, which opens automatically when the database is opened. This is very similar to a well known search engine in relation to look and feel it has a single text box [SearchBox] and a command button to click on in order to search the database when this button is clicked.

Attached to the button is a macro, which opens a pre-designed report and actions the filter based on what was entered in the [SearchBox] field.

Below is the code that is run:
[Category] Like "*" & [Forms]![frmSkillsSearch]![SearchBox] & "*" Or [KeyWords] Like "*" & [Forms]![frmSkillsSearch]![SearchBox] & "*"

Within the report is a SubForm, which has the same functionality as the original form (the text box is called [SearchBox1]), but I cannot get the macro attached to the command button on the subform to re-filter the results?

I think it may have something to do with the original filter from the initial form...

Any suggestions?

Cheers
Ryan

 

[strive4peace]

Hi Ryan,

the subform will not take on the filter of the main form ... is it linked using LinkMasterFields and LinkChildFields? How is your subform synchronized with the main form and why does it use the same source?

 

[Ryman]

Thanks for getting back and apologies maybe, I should have been clearer.

I basically have a search form [frmSkillsSearch] in the same style as Google, which opens up as soon as anyone opens the database using the AutoExec macro. The only controls on this form is an unbound text box [SearchBox] and a command button [Command10]. The user will type in the text box the information they want to search the database for e.g."Housing" then click on the command button to an embedded macro.

The embedded macro opens a report [rptSkillsSearchResults], which has a query as its record source [qrySearchResults] and the macro continues to filters results based on what the user entered in the unbound text box [SearchBox] on the [frmSkillsSearch] form with what is in the [Category] and [KeyWords] fields.

=[Category] Like "*" & [Forms]![frmSkillsSearch]![SearchBox] & "*" Or [KeyWords] Like "*" & [Forms]![frmSkillsSearch]![SearchBox] & "*".

This produces exactly the results that I require.

I then decided to add a subform [frmSkillsSearchResults] within the report [rptSkillsSearchResults], which has the similar fields as the original form, but is just compressed in terms of space. The unbound textbox is called [SearchBox1] and am using an image that will be clicked instead of a command button and this time is called [image57]. The macro attached to the on click event procedure is to open [rptSkillsSearchResults] and filter based on the information within the unbound text box of the subform [SearchBox1] condition below:

=[Category] Like "*" & [Forms]![frmSkillsSearchResults]![SearchBox1] & "*" Or [KeyWords] Like "*" & [Forms]![frmSkillsSearchResults]![SearchBox1] & "*"

When I action this macro, the filtered report [rptSkillsSearchResults] stays the same as what was originally searched for using the orginal form [frmSkillsSearch].

I have tried numerous macro options such as remove filter and refilter and cannot seem to get the results to update based on the information requested in the subform [SearchBox1]?

Not sure what you mean in relation to the LinkMaster fields, do you mean what are the relationsips within the database?

Cheers

Ryan

 

[strive4peace]

Hi Ryan,

I am a little confused why you have a subform with the same recordsource ...

anyhow, you need to apply the same filter to the subform. As it is already open, here is logic you can modify:

have the subform show all records, then filter it for whatever criteria is specified.

Put unbound comboboxes/textboxes, in the form header, for instance. Then, to trigger the code, put this in the [Event Procedure] code of the AfterUpdate event of each filter control:

    SetFormFilter

Perhaps your filter comes from data in the main form. In that case, trigger the code to filter on the mainform Current event and AfterUpdate event of each control that affects it.

this is a generic version of the code that would also go behind your form:

Private Function SetFormFilter()
  'Crystal strive4peace

   dim varFilter as variant
   varFilter = Null
 
   If not IsNull(me.controlname_for_text) Then
      varFilter = (varFilter + " AND ") _
         & "[TextFieldname]= '" & me.controlname_for_text  & "'"
   end if
 
   If not IsNull(me.controlname_for_date  ) Then
      varFilter = (varFilter + " AND ") _
         & "[DateFieldname]= #" & me.controlname_for_date  & "#"
   end if
 
   If not IsNull(me.controlname_for_number ) Then
      varFilter = (varFilter + " AND ") _
         & "[NumericFieldname]= " & me.controlname_for_number
   end if
 
   With Me
       If Not IsNull(varFilter )  Then
          .Filter = varFilter
          .FilterOn = true
      Else
          .FilterOn = false
      End if
      .Requery
   End With
 
End Function

WHERE
me.controlname_for_number, controlname_for_date, and controlname_for_text refer to the NAME property of a control on the form you are behind
(Me. represents the form -- kinda like "me" for me is not "me" for you )

delimiters are used for data that is not a number
quote marks ' or " for text
number signs # for dates

varFilter is a variant that will hold the string you are building for each condition -- but if nothing is specified in the filter control (IsNull), then that addition to the filter string is skipped.

(varFilter + " AND ")

If something is already in the filter, the word AND will be added. The beauty of using the + operator is that nothing + something = nothing ... so if nothing is there then nothing is added before the new criteria

~~~
finally, when the filter string is done, it is applied to your form if it has something in it. If not, then all the records will be displayed

That means that as you flip through records, only records matching that filter will show

if you are wanting to filter a subform, instead of using
With Me
you would use -->

   With Me.subform_controlname.form

WHERE
.subform_controlname is the Name property of the subform control

if the form to get criteria from is in a subform of the same main form, use -->

   With me.parent.subform_controlname.form

> "am using an image that will be clicked instead of a command button and this time is called [image57]"

images cannot take the focus (which means the control you came from will not be 'done') -- better to use a command button and set the Picture property

image57: before you write code, change the Name property of the control to something logical ~ makes code much easier to understand

 

[strive4peace]

Hi Ryan,

> "Not sure what you mean in relation to the LinkMaster fields, do you mean what are the relationsips within the database?"

No -- how records in the subform relate to records on the main form ... because usually they do, or should.

This is a general guideline that helps when you are beginning to use Access. As you get better and realize that certain records must be created before related records, you can bend this rule.

Create the main form and the form(s) that will be used as subform(s) -- make sure to put the linking key fields on them (usually ID fields).

to put a subform on a main form:

Create a subform control on your main form using the subform/subreport tool in the toolbox (Cancel the wizard if it pops up and fill properties manually)

Then, from the design view of the main form:

1. turn on the properties window – Right-Click anywhere and choose Properties from the shortcut menu

2. Click ONE time on the subform control if it is not already selected

3. Click on the DATA tab of the Properties window

SourceObject --> drop list and choose the name of the form you will use as a subform (You can also Drag a form object from the database window and drop it on the main form. This automatically sets the SourceObject property.)

LinkMasterFields --> MainID
LinkChildFields --> MainID

If you have multiple fields, delimit the list with semi-colon

LinkMasterFields --> MainID;Fieldname_main
LinkChildFields --> MainID;Fieldname_child

WHERE:
-- MainID is replaced with your field name holding an AutoNumber field (usually) in the parent table and a Long Integer field in the child table. Fieldname_main is the fieldname in the main RecordSet – and it is best to actually put the field on the main form. Fieldname_child is the name of a field in the child RecordSet – and, once again, it is best that this field actually be ON the related subform.

Even though the Help for Access says that the linking fields do not have to be ON the forms, I find this not be to be the case. It is best that you reference fields that are ON each of the respective forms. If a control is bound, I usually make the Name of the control the same as the ControlSource (what is in it). This does not follow standards but I find that it eases confusion. There are those who disagree and insist that controls should be named according to their favorite naming convention such as Leszynski/Reddick

It is common to set the Visible property to No for the control(s) containing the field(s) used in LinkChildFields

4. while still on the subform control, Click the ALL tab in the Properties window -- change the Name property to match the SourceObject property (minus Form. in the beginning if Access puts it there).

Difference between Subform Control and Subform

The first Click on a subform control puts handles* around the subform control.
*black squares in the corners and the middle of each side -- resizing handles

The subform control has properties such as
Name
SourceObject
LinkMasterFields
LinkChildFields
Visible
Locked
Left
Top
Width
Height

The subform control is the container for the subform or subreport used as the SourceObject.

The second Click on a subform control gets you INTO the subform that is contained by the subform control …then when you first Click on the contained form, you will see a black square where the rulers intersect in the upper left of the "form" you are "in"

me.subform_controlname --> the subform control
me.subform_controlname.form --> the form inside the subform control
me.subform_controlname.form.controlname --> a control on the form contained by the subform control

The form that is contained by the subform control is an independent form -- you can open it directly from the database window, just as you can with any other form. It is often referred to as a "subform" because of the way it is being used.

It is advisable to edit forms used as subforms directly, instead of within the main form.

Be sure to disable Name AutoCorrect

Failures caused by Name AutoCorrect
Microsoft Access Flaws - Failures caused by Name Auto-Correct

 

[rondeondo]

Hi Ryman

I think the main problem here is you want the report to work as a form, they don't. reports are for printing or e-mailing output and not really intended to be a working part of the user interface. If you want to be able to do another search from within your search results without closing the report and re-using your search form then you will be better served changing your report to a form. then the issue of making a sub form will be less problematic.

Ron