Save subforms filtered results to table.

[lewis1682]

Hi

I have a form called filter which has some textboxes on it. Also in the form I have a subform called "View_Contacts_subform" This is bound to a table called "Contacts" The subform has the following fields: FirstName, LastName, Company, County, Category.

I am using my filter form to filter the subform which works great it shows what I want to see. However I want to be able to save these results into a table which will be overwritten each time. This table will then be used to email them and make emails. If possible I would also like to include a checkbox which the user can untick if they do not want them to be used to it is removed from the table. The table I want all this information to be stored on is called "Filter_Results"

I have tried using a SQL statement to do this, however it only adds one record not all of them that are showing on the form. For example I have 5 records. I filter the records down to A and B. When it saves them to the table it only saves record A not B. I am only saving the FirstName for now because that is easier for me to work with then I will add the other fields.

I was thinking is there a way to count the number of records then loop though each one, that is beyond me, here is my code on the "Search" button on my Filter_Contacts form which holds the subform

Dim strWhere As String    Dim lngLen As Long
    
    If Not IsNull(Me.s_PostalCode) Then
        strWhere = strWhere & "([PostalCode] = """ & s_PostalCode & """) AND "
    End If
    
    If Not IsNull(Me.s_Company) Then
        strWhere = strWhere & "([Company] Like ""*" & Me.s_Company & "*"") AND "
    End If


    If Not IsNull(Me.s_County) Then
        strWhere = strWhere & "([County] Like ""*" & Me.s_County & "*"") AND "
    End If
    
    If Not IsNull(Me.CategoryList) Then
        strWhere = strWhere & "([Category] Like ""*" & Me.CategoryList & "*"") AND "
    End If
    
    If Me.AGM = -1 Then
        strWhere = strWhere & "([AGM] = true) AND "
    End If
    
    If Me.s_Newsletter = -1 Then
        strWhere = strWhere & "([Newsletter] = true) AND "
    End If
    


    lngLen = Len(strWhere) - 5
    If lngLen <= 0 Then
        MsgBox "No criteria", vbInformation, "Nothing to do."
    Else
        strWhere = Left$(strWhere, lngLen)
        
        Me.View_Contacts_subform.Form.Filter = strWhere
        Me.View_Contacts_subform.Form.FilterOn = True
    End If
    Dim MySQL As String
    
    MySQL = "INSERT INTO Filter_Results (FirstName) SELECT [Forms]![Filter_Contacts]![View_Contacts_subform].[Form]![FirstName] AS FirstName;"
    DoCmd.RunSQL MySQL

[mrojas]

Have you tried, on your MySQL statement:
MySQL= "INSERT INTO Filter_Results " & strWhere

[June7]

As mrojas, shows, must concatenate variables. Enclosing within quotes results in the literal string that is the variable name, not the contents of the variable. And why would you reference the FirstName control instead of the filter string constructed by code?

[ItsMe]

I built a warehouse module where I use loop commands to bring records from a purchase order table to an inventory table. I can post an example using DAO later today if you are interested in going that route.

[lewis1682]

After changing my code to:

    Dim strWhere As String    Dim lngLen As Long
    
    If Not IsNull(Me.s_PostalCode) Then
        strWhere = strWhere & "([PostalCode] = """ & s_PostalCode & """) AND "
    End If
    
    If Not IsNull(Me.s_Company) Then
        strWhere = strWhere & "([Company] Like ""*" & Me.s_Company & "*"") AND "
    End If


    If Not IsNull(Me.s_County) Then
        strWhere = strWhere & "([County] Like ""*" & Me.s_County & "*"") AND "
    End If
    
    If Not IsNull(Me.CategoryList) Then
        strWhere = strWhere & "([Category] Like ""*" & Me.CategoryList & "*"") AND "
    End If
    
    If Me.AGM = -1 Then
        strWhere = strWhere & "([AGM] = true) AND "
    End If
    
    If Me.s_Newsletter = -1 Then
        strWhere = strWhere & "([Newsletter] = true) AND "
    End If
    


    lngLen = Len(strWhere) - 5
    If lngLen <= 0 Then
        MsgBox "No criteria", vbInformation, "Nothing to do."
    Else
        strWhere = Left$(strWhere, lngLen)
        
        Me.View_Contacts_subform.Form.Filter = strWhere
        Me.View_Contacts_subform.Form.FilterOn = True
    End If
    Dim MySQL As String
    
    MySQL = "INSERT INTO Filter_Results" & strWhere
    DoCmd.RunSQL MySQL

I get a error saying "Error in INSERT INTO syntax"

[June7]

Your INSERT INTO does not identify fields to be populated nor where to select data from. Your first structure was closer to correct.

If you want to overwrite the records, then first delete all of them.

Instead of RunSQL which will trigger warning message unless you precede with SetWarnings = False (then reset with SetWarnings = True), try like:

CurrentDb.Execute "DELETE FROM Filter_Results"
CurrentDb.Execute "INSERT INTO Filter_Results(field1, field2, field3, field4, field5) SELECT field1, field2, field3, field4, field5 FROM tablename WHERE " & strWhere

[ItsMe]

I was able to duplicate your single record error. I was testing some code using ADO to create the new records and ran into the same problem during certain instances where I passed the filter set to and from an SQL string.

The single record symptom did not occur when I passed the where clause to the subform’s recordset verses passing it to the .Filter.

Example:
'start code
Dim rcd As Recordset
Dim stSQL As String
Dim strNewWhere As String
strNewWhere = Me.View_Contacts_subform.Form.Filter
Me.View_Contacts_subform.Form.FilterOn = False
stSQL = ""
stSQL = "SELECT * FROM [Contacts]"
stSQL = stSQL & " WHERE " & strNewWhere
stSQL = stSQL & " ORDER BY [LastName]"
Me.View_Contacts_subform.Form.RecordSource = stSQL
Me.View_Contacts_subform.Form.Requery
Set rcd = Me.View_Contacts_subform.Form.RecordsetClone 'This is your master recordset
Dim intCount As Integer
intCount = rcd.RecordCount
MsgBox intCount
'end code

I did not use the INSERT via SQL to update the table in the model I built. However, I did stumble upon a single record within the recordset when there should have been multiple records while using the .Filter exclusively. Perhaps passing your strWhere clause into the SQL example above will allow you to change your subform's recordset rather than using the subforms Filter.

It is just a suggestion. I was surprised to stumble across the single record count when I was looking at multiple filtered records. Somewhere between having the filtered records and adding another field using "AND" it would return a single record.

[lewis1682]

I added in the following code to the button:

CurrentDb.Execute "DELETE FROM Filter_Results"
CurrentDb.Execute "INSERT INTO Filter_Results(ID,Title,FirstName,LastName,JobTitle,Company,AddressLine1,AddressLine2,Town,County,PostalCode,Phone,Fax,Email,Website,Category,Include,Notes,Newsletter,AGM) SELECT ID,Title,FirstName,LastName,JobTitle,Company,AddressLine1,AddressLine2,Town,County,PostalCode,Phone,Fax,Email,Website,Category,Include,Notes,Newsletter,AGM FROM Filter_Results WHERE" & strWhere

There is no error when it runs however if I open up the table nothing is there, I don't get it! If I change it into a MySql statement yes it does ask if you want to append the rows, however it says do you want to append 0 rows meaning its not picking the data up using the strWhere from the Contacts Table.

[mrojas]

How about sharing with us a copy of the database so that we can take a closer look?

[ItsMe]

How about sharing with us a copy of the database so that we can take a closer look?

Just a heads up. OP states the main form is named "Filter".

[lewis1682]

I was able to duplicate your single record error. I was testing some code using ADO to create the new records and ran into the same problem during certain instances where I passed the filter set to and from an SQL string.

The single record symptom did not occur when I passed the where clause to the subform’s recordset verses passing it to the .Filter.

Example:
'start code
Dim rcd As Recordset
Dim stSQL As String
Dim strNewWhere As String
strNewWhere = Me.View_Contacts_subform.Form.Filter
Me.View_Contacts_subform.Form.FilterOn = False
stSQL = ""
stSQL = "SELECT * FROM [Contacts]"
stSQL = stSQL & " WHERE " & strNewWhere
stSQL = stSQL & " ORDER BY [LastName]"
Me.View_Contacts_subform.Form.RecordSource = stSQL
Me.View_Contacts_subform.Form.Requery
Set rcd = Me.View_Contacts_subform.Form.RecordsetClone 'This is your master recordset
Dim intCount As Integer
intCount = rcd.RecordCount
MsgBox intCount
'end code

I did not use the INSERT via SQL to update the table in the model I built. However, I did stumble upon a single record within the recordset when there should have been multiple records while using the .Filter exclusively. Perhaps passing your strWhere clause into the SQL example above will allow you to change your subform's recordset rather than using the subforms Filter.

It is just a suggestion. I was surprised to stumble across the single record count when I was looking at multiple filtered records. Somewhere between having the filtered records and adding another field using "AND" it would return a single record.

Is there a way I could use this method and then save the recordset into the "Filter_Results" table?

[ItsMe]

Is there a way I could use this method and then save the recordset into the "Filter_Results" table?

What you are looking at there is my effort to persuade you to change your search form so it saves the results as a full recordset and passes it to your subform's recordsource.

Your problem may be associated with using .Filter. However, .Filter is not a deal breaker and should work too. I only believe changing the form's recordsource is a more stable foundation to work with. Any search form or cascading pull down examples I have use .Recordsource.

MrOjas is requesting a copy of your DB. It seems there may be several small issues that compound symptoms or build hurdles into your foundation.

[lewis1682]

Okay so here is my database. You will have to bypass the start up using the shift key, you will also have to re-link the tables. I have removed all the forms that aren't linked to the Filter_Contacts form. This is the form which I am having the problem with, I need the tasks at the bottom to be able to read the data, which is why I think all the results should be stored on the local table "Filter_Results"DB Issues.zip

[ItsMe]

I got it working. I added a command button labeled New Command Button and named cmdNewExecute. I imported your BE tables to the front end for testing purposes.

I inserted the code I posted into the Command22 click event; at the end. It transfers your code to the subform's recordset. Maybe you can do some reverse engineering and decide how you want to approach it in the end.

Will post back in a few. Just want to smash it with a hammer a couple times before I upload the example.

[mrojas]

I've started to look into your challenge. First, you need to change your validation process from "If Not IsNull(Me.s_PostalCode)" because when you click on the clear button, you clear it by inserting a "blank" space, which then make the field non-null.
For instance, if after clicking the Clear button you enter a company for criteria and clicked on the search, your condition when checking the PostalCode is no longer null, but a space. When you do the search, your statement is looking for PostalCode="" and Company="whatever the user entered"; thus the search fails, unless there is a record with a "blank" space for postal code.

Will keep looking into other problem areas.