Filtering within a main form and display results on a sub form.

[fletchy888]

Hi there,

I'm quite a novice access user, especially when it comes to utilising and creating a customised forms what I currently require.



The table of data is GM - BOX (1000KVA) that has been assigned as the source data.
In the mainform, combo boxes have been designed with using the columns from GM - BOX (1000KVA) datatable as the record sources for the combo boxes. So when the criteria is entered in the combo boxes, I want to use a command button as the event performer, to filter the results in a subform below the mainform.

I am aware that I can use a query or a filter. However, this is to be designed so it is very convinient to use for the users that will be using it.

Thank-you for reading this thread, and really look forward to your reply.

Regards

Fletchy888

[June7]

Check out this tutorial http://www.datapigtechnologies.com/f...tomfilter.html

[fletchy888]

Thanks for the video, June7. There is some useful information in there. However, trialing it out on a dummy form, it will not work at all. And that's just using one combo box to filter the records as a trial. Also in the combo box there values that are duplicated in other records, so it is acceptable to use value list instead of table/query in the row source type? (what i mean is 2 possible options will be listed as many records as there are) What method do you use for linking up the subform to the mainform properly? The mainform is acting as the filter but also displays the information of the records. The subform displays records in a table format.
My progress is feeling rubbish for my project at the moment.

Many thanks

[June7]

Can use value list or SELECT DISTINCT as row source.

I am confused. The mainform displays same data as the subform? The two forms have the same RecordSource? I just went through this with another poster and I redesigned their form so did not have subform.

Need to explain more exactly what you are trying to. Do you want to provide project for analysis?

[fletchy888]

Yes they both use the same record source and yes they display the same data technically. The subform is useless in its current state, except for the capability to add new new records.

My database is provided in the attachment if you want to have a look for analysis.

If you have a look at the table, the columns are linked to some of the combo boxes and the text boxes in the main form.

The subform is to be used to show the filtered results, with showing all the records when unfiltered, to show the filtered results in the subform is the intention anyway. The filter command button is what I want to do the event of the filter once all the variants have been entered in the main form,, and then that will filter down the results.

Many thanks

[fletchy888]

The VBA coding that I am trying to use for the filter command button, follows this format for all the variants in the table:

Private Sub Results_command_Click()
On Error GoTo Err_Results_command_Click
'The following filters the LK kits that match the specification'
Dim strWhere As String
Dim IngLen As Long
If Not IsNull(Me.Filter_F) Then
strWhere = strWhere & "([F]=""" & Me.Filter_F & """)AND"
End If
If IngLen>0 Then
Me.Filter = Left(strWhere,Inglen)
Me.FilterOn=True
Else
Me.FilterOn=True
End If

I've been informed that it should work if the 5th,4th & 2nd last lines need to change and it should it work. I'm not sure to work thou.......

[June7]

You did not include the subform in the project.

In the code you posted and in the project, you declare variable LngLen but nothing in the code will change it, it will always be 0