Filtering Multiple data set from a single column Field

[June7]

The RecordSource of form or report can be reference to a table or a query or can be an SQL statement.

Even if the RecordSource is table, the form Filter property can be set to filter records. Code can set the Filter property.

The issue is applying filter criteria by the rules you define. I offered a query in post 27 that filters records. That query could be the RecordSource of form or report.

[mr879]

The problem is when i used the code for the large amount of records the access file froze.

[mr879]

would the code be like this then?


Dim rs As DAO.Recordset
Set rs = CurrentDb.OpenRecordset("Table1")
increment = 1
If rs.RecordCount <> 0 Then
rs.MoveFirst
While Not rs.EOF
'do stuff here
increment = increment + 1
rs.MoveNext
Wend
End If
MsgBox increment
rs.Close
Set rs = Nothing

[June7]

That code will not accomplish filtering records for display on form or report.

Very rough example of a function to return a value to query and use that to filter records:

Function MyFunction(strInput As String) As Boolean
Dim rs As DAO.Recordset
Set rs = CurrentDb.OpenRecordset("Table1")
While Not rs.EOF
'do something with strInput and recordset
rs.MoveNext
Wend
MyFunction = True or False as determined by doing something with strInput and recordset
End Function

Now call function from query:

SELECT * FROM tablename WHERE MyFunction([fieldname]) = True;

[mr879]

I have like 1,000 line of code so far that solely refers to the table for filtering and such but now I am worried that if I use a query instead of a table I may have issues later down the line.

[June7]

I don't understand how code is filtering table.

[mr879]

The table under the form (split form) shows the filtered values once the user submits the values in the text fields in the form where the UI is.

[June7]

Then the code is setting the form Filter property?

[mr879]

Yes the form which is the UI has code associated with the event handlers such as the submit, filter, unfilter buttons I would like to only use vba to do what I need to do, I am pretty sure I cannot use the query properly in this matter I have tried a few times and it tends to freeze up on me.

[June7]

Sorry the query does not handle that number of records. That is surprising but I don't have experience with SQL databases. If that query fails then I suspect a function called by query would be just as bad or worse.

You might test the query with Access table of the 1,000,000 records imported from the SQL db. If that runs well then it indicates the issue is with the SQL backend and/or running query over the network.

[mr879]

Can this process be done just by using vba alone? That way I can rely on the table instead of the query and that should work properly.

[June7]

The VBA would have to output something somewhere.

Either a function called by a query as shown in example.

Or write records to another table.

[mr879]

what do you mean output something somewhere? The output values should be the result of the filtered values which should show up at the bottom of the split form which is the table portion, any filter criteria or action done in the UI will affect the table at the bottom that is the main point of the split form so that you can display your results of the new table at the bottom.

[June7]

Options for using code:

1. call a function procedure from query, the function returns a value to the query

2. a sub procedure can write records to a table

3. a sub procedure can update a field of existing records - the value used in the update can be the result of some calculation in VBA

4. a sub procedure can construct a filter string that can be applied to form Filter property or can construct an SQL statement that can be applied to form RecordSource property

In all cases the code produces something that is used somewhere.

What do you mean by 'new' table? Isn't the split form bound to a table or query or an SQL statement in the RecordSource? Option 4 can be used to filter a form's records. However, in this case, the resulting filter or SQL statement would the same as the query you say fails on 1,000,000 records.

[mr879]

By new table i just mean a table that is a result of the user in put in the UI not a whole different table. So what would be my best option in this case should I just use record source to do this? It seems that a query in this case will not work properly.