Filter not working with "Select All" checkbox control

[bre1603]

I have a continuous form in Access 2007 called “Leadership Contact List.” It has a checkbox control for each record (bound to a field in the underlying table) that is used to email or create mailing labels for the selected contacts. In the form header, there is a “Select All” checkbox control which (of course) selects all the checkboxes. The form also has a combo box in the header that filters the records according to “agency type.”

What I want is to create labels or email only selected contacts within the filtered set, with the option to select them all at once using the "Select All" control.

The “Select All” works great, until the form is filtered. The code behind “Select All” continues to set all the checkboxes as true, even if they aren’t showing in the form. So when the “Email Selected Contacts” or “Create Mailing Labels for Selected Contacts” buttons are clicked, the results are not filtered at all.

I have tried to add additional criteria to the Where clause in the SQL statement of the On Click event to account for the filter, but I get the error “Too few parameters. Expected 1” (error #3061). I don’t know if it’s because I have the syntax wrong or because I’m trying to do something I can’t.

Here’s the code that works:

If Me.SelectAll = True Then
    strSQL = "Update [Secondary ManualContacts Table] SET [Secondary ManualContacts Table].[Send Email] = True"
    strSQL = strSQL & " WHERE [Secondary ManualContacts Table].[Send Email] = False" & ";"
    CurrentDb.Execute strSQL, dbFailOnError
    Me.Refresh
        
    ElseIf Me.SelectAll = False Then
        strSQL = "Update [Secondary ManualContacts Table] SET [Secondary ManualContacts Table].[Send Email] = False"
        strSQL = strSQL & " WHERE [Secondary ManualContacts Table].[Send Email] = True" & ";"
        CurrentDb.Execute strSQL, dbFailOnError
        Me.Refresh
    End If

Here's the code that errors out:

If Me.SelectAll = True Then
        strSQL = "Update [Secondary ManualContacts Table] SET [Secondary ManualContacts Table].[Send Email] = True"
        strSQL = strSQL & " WHERE (([Secondary ManualContacts Table].[Send Email] = False) AND ([ManualContacts Table].[Agency Type] ='" & Forms![LeadershipEmailListALL].[cbofltrAgencyType] & "'))"
        CurrentDb.Execute strSQL, dbFailOnError
        Me.Refresh
        
        ElseIf Me.SelectAll = False Then
            strSQL = "Update [Secondary ManualContacts Table] SET [Secondary ManualContacts Table].[Send Email] = False"
            strSQL = strSQL & " WHERE (([Secondary ManualContacts Table].[Send Email] = True) AND ([ManualContacts Table].[Agency Type]='" & Forms![LeadershipEmailListALL].[cbofltrAgencyType] & "'))"
            CurrentDb.Execute strSQL, dbFailOnError
            Me.Refresh
        End If

Any help (or alternative suggestions) would be greatly appreciated!

Thanks in advance.

[nico5038]

The error message is an indication that you have a typo in one of the field names.

Personally I would use the Me.Filter property of the form to execute the UPDATE query, as Access does allow additional filtering with the right-click pop-up menu.

Using the Me.Filter can be done with this code:

' test or filter has been set
IF Me.Filteron = False then
   currentdb.execute ("Update [Secondary ManualContacts Table] SET [Secondary ManualContacts Table].[Send Email] = True")
else
   currentdb.execute ("Update [Secondary ManualContacts Table] SET [Secondary ManualContacts Table].[Send Email] = True WHERE " & Me.Filter)
End if

Getting the idea ?

Nic;o)

[bre1603]

The error message is an indication that you have a typo in one of the field names.

Personally I would use the Me.Filter property of the form to execute the UPDATE query, as Access does allow additional filtering with the right-click pop-up menu.

Using the Me.Filter can be done with this code:

' test or filter has been set
IF Me.Filteron = False then
   currentdb.execute ("Update [Secondary ManualContacts Table] SET [Secondary ManualContacts Table].[Send Email] = True")
else
   currentdb.execute ("Update [Secondary ManualContacts Table] SET [Secondary ManualContacts Table].[Send Email] = True WHERE " & Me.Filter)
End if

Getting the idea ?

Nic;o)

Thanks for the reply. I'm anxious to get some fresh perspective on this.

Okay, so I tried the form filter (after looking at my current code and unable to see the typo...)

When I first clicked the SelectAll checkbox, it seemed to work. All boxes were selected. But then I tried to filter it. And got the same error - Too Few Parameters.

Any ideas?

[nico5038]

Looks to me we first have to check the SQL.

The best way is to place a break point in the code (e.g. click left ruler for getting a dot) and execute the code till the strSQL is filled. Next type in the immediate window:

? strSQL

to get the string printed and copy the result.

Next open the query editor, create a new query without tables and switch to SQL mode. There post the SQL en execute it. Access will now give a pop-up window asking the value for a specific field it doesn't find i your table(s).

Nic;o)

[NeoPa]

If the code that is running is within Forms![LeadershipEmail ListALL] then you can (and should) replace that with Me.
IE. Me.[cbofltrAgencyTy pe]

Otherwise, check the types of the field and the return from the ComboBox. ComboBoxes particularly, are often set to show text whereas they return another (generally numeric) value.

[parodux]

Updata recordset

You could do something like this:

private sub SelectAll_click()
dim rst as dao.recordset
set rst = me.recordset
rst.movefirst
do until rst.eof
rst![send email] = true
loop
rst.update
rst.close
set rst.nothing
end sub

eg. Too few parameters

You can declare the parameters in the query designer or if it's a dao recordset, you can use:

dim db as dao.database
dim qdf as dao.querydefs
dim rst as dao.recordset
set db = currentdb()
set qdf = db.querydefs("NameOfQuery")
qdf.parameters(0) = forms!YourForm!YourCriteriaField
qdf.parameters(1).... etc
set rst = qdf.openrecordset
with rst
.movefirst
etc.. etc.. etc!~)

[bre1603]

If the code that is running is within Forms![LeadershipEmail ListALL] then you can (and should) replace that with Me.
IE. Me.[cbofltrAgencyTy pe]

Otherwise, check the types of the field and the return from the ComboBox. ComboBoxes particularly, are often set to show text whereas they return another (generally numeric) value.

I changed my original code to me.cbofltrAgenc yType, to no avail. I still got the error about too few parameters.

I also checked the types of the field and the return of the combo box data - it's a text field all the way back to the table. The record source only has one column - the Agency Type, which is a text field. The SQL is as follows:
SELECT ManualContacts.[Agency Type] FROM ManualContacts GROUP BY ManualContacts.[Agency Type];

I was alomost hoping this was what I was missing. It would make sense as far as the error I'm getting. But I guess it's not going to be that easy...

[nico5038]

Did you check your query string strSQL in the query editor ?

Nic;o)

[bre1603]

Looks to me we first have to check the SQL.

The best way is to place a break point in the code (e.g. click left ruler for getting a dot) and execute the code till the strSQL is filled. Next type in the immediate window:

? strSQL

to get the string printed and copy the result.

Next open the query editor, create a new query without tables and switch to SQL mode. There post the SQL en execute it. Access will now give a pop-up window asking the value for a specific field it doesn't find i your table(s).

Nic;o)

I checked the SQL and the problem comes from [ManualContacts].[Agency Type]. It (of course) is not in the table "Secondary ManualContacts Table", which is what I'm trying to update.

So I'm wondering if I can reference a qry or alter the SQL to Update the "Secondary ManualContacts Table" while using parameters from both tables (the other one being "ManualContacts ") to filter what gets updated?

[bre1603]

I added the ManualContacts Table to the SQL (after "Update")-

If Me.SelectAll = True Then
        strSQL = "Update [Secondary ManualContacts Table],[ManualContacts] SET [Secondary ManualContacts Table].[Send Email] = True"
        strSQL = strSQL & " WHERE (([Secondary ManualContacts Table].[Send Email] = " & False & ") AND ([ManualContacts].[Agency Type] ='" & Me.[cbofltrAgencyType] & "'))"
        CurrentDb.Execute strSQL, dbFailOnError
        Me.Refresh
       
        ElseIf Me.SelectAll = False Then
            strSQL = "Update [Secondary ManualContacts Table],[ManualContacts] SET [Secondary ManualContacts Table].[Send Email] = False"
            strSQL = strSQL & " WHERE (([Secondary ManualContacts Table].[Send Email] = True) AND ([ManualContacts].[Agency Type]='" & Me.[cbofltrAgencyType] & "'))"
            CurrentDb.Execute strSQL, dbFailOnError
            Me.Refresh
        End If

And I didn't the error!

However, it didn't filter what was selected when the SelectAll checkbox was clicked. All records were "selected" (their checkboxes filled), even those that were filtered out. And I'm back to the beginning!

Good times...

[NeoPa]

bre1603: I changed my original code to me.cbofltrAgenc yType, to no avail. I still got the error about too few parameters.

This was not a fix to your problem, but just a simplification of your code. As a general rule, simpler is more reliable and leads to fewer problems.

bre1603: I also checked the types of the field and the return of the combo box data - it's a text field all the way back to the table. The record source only has one column - the Agency Type, which is a text field. The SQL is as follows:
SELECT ManualContacts.[Agency Type] FROM ManualContacts GROUP BY ManualContacts.[Agency Type];

That's important information. We can now rule that out as being a problem (as your SQL is correctly formatted for string data).

I would only echo Nico's request now that you print off the actual SQL before it is executed and post it in here for our perusal. Sometimes issues that aren't mentioned show up in the results. They may be overlooked for many reasons, chief of these is that the OP had no reason to suppose they were important. Post what you get anyway and let's see if our more experinced eyes can pick anything up.

[parodux]

You are missing the relation between [Secondary ManualContacts Table] and the [ManualContacts Table]

Right now you are just telling the db to update all that is false en [Secondary....] and that's what's happening!~)

[parodux]

This was not a fix to your problem, but just a simplification of your code. As a general rule, simpler is more reliable and leads to fewer problems.

the change from forms!etc. to me.etc. would have made the 'too few parameters' error go away, because VBA is now putting the string in as a criteria. Where as before it's was a parametre that SQL was missing...

(Sorry about my English!~)

...and my confusion about this reply system...

[bre1603]

This was not a fix to your problem, but just a simplification of your code. As a general rule, simpler is more reliable and leads to fewer problems.

That's important information. We can now rule that out as being a problem (as your SQL is correctly formatted for string data).

I would only echo Nico's request now that you print off the actual SQL before it is executed and post it in here for our perusal. Sometimes issues that aren't mentioned show up in the results. They may be overlooked for many reasons, chief of these is that the OP had no reason to suppose they were important. Post what you get anyway and let's see if our more experinced eyes can pick anything up.

Here's what I get when I print off the actual SQL before it's executed:

Update [Secondary ManualContacts Table],[ManualContacts] SET [Secondary ManualContacts Table].[Send Email] = True WHERE (([Secondary ManualContacts Table].[Send Email] = False) AND ([ManualContacts].[Agency Type]=''))

This is when the cbofltrAgencyTy pe is null. When there's a value in it, it ends like this:

... AND ([ManualContacts].[Agency Type]='PS'))

Please peruse away. :D

[parodux]

Could you tell us what the post/recordsource for the form is.. That would help!~)