By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
460,031 Members | 1,248 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 460,031 IT Pros & Developers. It's quick & easy.

Filter not working with "Select All" checkbox control

bre1603
P: 39
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:

Expand|Select|Wrap|Line Numbers
  1. If Me.SelectAll = True Then
  2.     strSQL = "Update [Secondary ManualContacts Table] SET [Secondary ManualContacts Table].[Send Email] = True"
  3.     strSQL = strSQL & " WHERE [Secondary ManualContacts Table].[Send Email] = False" & ";"
  4.     CurrentDb.Execute strSQL, dbFailOnError
  5.     Me.Refresh
  6.  
  7.     ElseIf Me.SelectAll = False Then
  8.         strSQL = "Update [Secondary ManualContacts Table] SET [Secondary ManualContacts Table].[Send Email] = False"
  9.         strSQL = strSQL & " WHERE [Secondary ManualContacts Table].[Send Email] = True" & ";"
  10.         CurrentDb.Execute strSQL, dbFailOnError
  11.         Me.Refresh
  12.     End If

Here's the code that errors out:

Expand|Select|Wrap|Line Numbers
  1. If Me.SelectAll = True Then
  2.         strSQL = "Update [Secondary ManualContacts Table] SET [Secondary ManualContacts Table].[Send Email] = True"
  3.         strSQL = strSQL & " WHERE (([Secondary ManualContacts Table].[Send Email] = False) AND ([ManualContacts Table].[Agency Type] ='" & Forms![LeadershipEmailListALL].[cbofltrAgencyType] & "'))"
  4.         CurrentDb.Execute strSQL, dbFailOnError
  5.         Me.Refresh
  6.  
  7.         ElseIf Me.SelectAll = False Then
  8.             strSQL = "Update [Secondary ManualContacts Table] SET [Secondary ManualContacts Table].[Send Email] = False"
  9.             strSQL = strSQL & " WHERE (([Secondary ManualContacts Table].[Send Email] = True) AND ([ManualContacts Table].[Agency Type]='" & Forms![LeadershipEmailListALL].[cbofltrAgencyType] & "'))"
  10.             CurrentDb.Execute strSQL, dbFailOnError
  11.             Me.Refresh
  12.         End If
Any help (or alternative suggestions) would be greatly appreciated!

Thanks in advance.
Jul 14 '10 #1

✓ answered by NeoPa

If the results of that QueryDef ([cbofltrAgencyType]) are updatable (I expect they are otherwise you wouldn't be able to make any amendments to it via the form) then do your UPDATE query on that ([cbofltrAgencyType]) instead of the table.

That last post makes everything clear again. Well done. That's not often easy.

Share this Question
Share on Google+
41 Replies


nico5038
Expert 2.5K+
P: 3,072
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:
Expand|Select|Wrap|Line Numbers
  1. ' test or filter has been set
  2. IF Me.Filteron = False then
  3.    currentdb.execute ("Update [Secondary ManualContacts Table] SET [Secondary ManualContacts Table].[Send Email] = True")
  4. else
  5.    currentdb.execute ("Update [Secondary ManualContacts Table] SET [Secondary ManualContacts Table].[Send Email] = True WHERE " & Me.Filter)
  6. End if
  7.  
Getting the idea ?

Nic;o)
Jul 14 '10 #2

bre1603
P: 39
@nico5038
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?
Jul 15 '10 #3

nico5038
Expert 2.5K+
P: 3,072
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)
Jul 15 '10 #4

NeoPa
Expert Mod 15k+
P: 31,769
If the code that is running is within Forms![LeadershipEmailListALL] then you can (and should) replace that with Me.
IE. Me.[cbofltrAgencyType]

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.
Jul 15 '10 #5

P: 26
You could do something like this:
Expand|Select|Wrap|Line Numbers
  1. private sub SelectAll_click()
  2. dim rst as dao.recordset
  3. set rst = me.recordset
  4. rst.movefirst
  5. do until rst.eof
  6. rst![send email] = true
  7. loop
  8. rst.update
  9. rst.close
  10. set rst.nothing
  11. 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:
Expand|Select|Wrap|Line Numbers
  1. dim db as dao.database
  2. dim qdf as dao.querydefs
  3. dim rst as dao.recordset
  4. set db = currentdb()
  5. set qdf = db.querydefs("NameOfQuery")
  6. qdf.parameters(0) = forms!YourForm!YourCriteriaField
  7. qdf.parameters(1).... etc
  8. set rst = qdf.openrecordset
  9. with rst
  10. .movefirst
  11. etc.. etc.. etc!~)
Jul 15 '10 #6

bre1603
P: 39
@NeoPa
I changed my original code to me.cbofltrAgencyType, 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...
Jul 15 '10 #7

nico5038
Expert 2.5K+
P: 3,072
Did you check your query string strSQL in the query editor ?

Nic;o)
Jul 15 '10 #8

bre1603
P: 39
@nico5038
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?
Jul 15 '10 #9

bre1603
P: 39
I added the ManualContacts Table to the SQL (after "Update")-

Expand|Select|Wrap|Line Numbers
  1. If Me.SelectAll = True Then
  2.         strSQL = "Update [Secondary ManualContacts Table],[ManualContacts] SET [Secondary ManualContacts Table].[Send Email] = True"
  3.         strSQL = strSQL & " WHERE (([Secondary ManualContacts Table].[Send Email] = " & False & ") AND ([ManualContacts].[Agency Type] ='" & Me.[cbofltrAgencyType] & "'))"
  4.         CurrentDb.Execute strSQL, dbFailOnError
  5.         Me.Refresh
  6.  
  7.         ElseIf Me.SelectAll = False Then
  8.             strSQL = "Update [Secondary ManualContacts Table],[ManualContacts] SET [Secondary ManualContacts Table].[Send Email] = False"
  9.             strSQL = strSQL & " WHERE (([Secondary ManualContacts Table].[Send Email] = True) AND ([ManualContacts].[Agency Type]='" & Me.[cbofltrAgencyType] & "'))"
  10.             CurrentDb.Execute strSQL, dbFailOnError
  11.             Me.Refresh
  12.         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...
Jul 15 '10 #10

NeoPa
Expert Mod 15k+
P: 31,769
bre1603: I changed my original code to me.cbofltrAgencyType, 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.
Jul 15 '10 #11

P: 26
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!~)
Jul 15 '10 #12

P: 26
@NeoPa
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...
Jul 15 '10 #13

bre1603
P: 39
@NeoPa
Here's what I get when I print off the actual SQL before it's executed:
Expand|Select|Wrap|Line Numbers
  1. 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 cbofltrAgencyType is null. When there's a value in it, it ends like this:
Expand|Select|Wrap|Line Numbers
  1. ... AND ([ManualContacts].[Agency Type]='PS'))
Please peruse away. :D
Jul 15 '10 #14

P: 26
Could you tell us what the post/recordsource for the form is.. That would help!~)
Jul 15 '10 #15

bre1603
P: 39
@parodux
The form's recordsource is "qryLeadershipEmailListALL" - which includes tables "ManualContacts" and "Secondary ManualContacts Table"

The query includes [Send Email] (a checkbox control), as well as [Agency Type], and the name and email address for the contacts. The only criteria is that the contact name is not null.

HTH
Jul 15 '10 #16

P: 26
What is the SQL for qryLeadershipEmailListALL?~)
Jul 15 '10 #17

P: 26
It's more than night time here... so I will switch off.. but make a copy of qryLeadershipEmailListALL and change it into your update query, that will work... Good night!~)
Jul 15 '10 #18

bre1603
P: 39
@parodux
I'll give it a try, thanks
Jul 15 '10 #19

NeoPa
Expert Mod 15k+
P: 31,769
Bre,

I think there might be some confusion here. It is rarely a good idea to mix up instructions from different posters in the same thread. That is not to say members shouldn't post their ideas, but you should treat them separately.

I was interested in the situation as you'd described it prior to your making the change to add an extra table into the UPDATE clause. I'm afraid I cannot see why that would be recommended and it's not something I would recommend. That SQL I wouldn't expect to work. What I'm interested in is the difference between known working SQL and that which doesn't work when you've tried the suggestion I was working on (put forward by Nico I believe). It gets very difficult to follow if you mix up different conversations.
Jul 15 '10 #20

NeoPa
Expert Mod 15k+
P: 31,769
parodux: 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...
I don't believe that's true. It should have no real difference at all. The effect would be nil. It's simply another, much simpler, way to reference the same object.
Jul 15 '10 #21

NeoPa
Expert Mod 15k+
P: 31,769
bre1603:
Expand|Select|Wrap|Line Numbers
  1. ... WHERE (([Secondary ManualContacts Table].[Send Email] = False) AND ([ManualContacts].[Agency Type]=''))
Expand|Select|Wrap|Line Numbers
  1. ... WHERE (([Secondary ManualContacts Table].[Send Email] = False) AND ([ManualContacts].[Agency Type]='PS'))
I looked again just at the WHERE clauses.
The first is incorrect, as when [cbofltrAgencyType] is Null then presumably there is no filter applied. This is not the same as filter [Agency Type] by an empty string (as you have it in your SQL).
As for the second version, the WHERE clause seems spot on. I can't answer for the rest of the SQL but that part should work for you.

I'd like to know what results each produced, but not while the UPDATE clause is like that. It makes no sense to me, so I wouldn't know how to interpret any such results.
Jul 15 '10 #22

bre1603
P: 39
I apologize for the confusion... I'm a little confused myself! I've got three different scenarios I'm testing and it seems like nothing is working.

As for the difference between the working SQL and the one I'm trying to create - it's the filter. Without the reference to [ManualContacts].[Agency Type] = me.cbofltrAgenctType, the SQL executes without any issues. I thought this might be because I hadn't referenced the table "ManualContacts" anywhere else in the SQL, so that's why I added it to the UPDATE clause. I’ll take it out.

Here’s the adjusted SQL:
Expand|Select|Wrap|Line Numbers
  1. Update [Secondary ManualContacts Table]
  2. SET    [Secondary ManualContacts Table].[Send Email] = False
  3. WHERE  [Secondary ManualContacts Table].[Send Email] = True
  4.   AND  [ManualContacts].[Agency Type]= ''
(*sorry for the lack of [code] tags on that last post...I wasn't sure if there was a length requirement or not.)
Jul 15 '10 #23

bre1603
P: 39
@NeoPa
So how do I handle the SQL when there isn't a filter applied (they want to see all agencies at the same time)?

Also, the above SQL still gives me the error of too few parameters. Even when there is a filter applied. And I just opened up the form again, and the SelectAll checkbox isn't even selecting anything.

Am I missing something here?
Jul 16 '10 #24

bre1603
P: 39
@NeoPa
The following code is highlighted when I get the Too Few Parameters" error:
Expand|Select|Wrap|Line Numbers
  1. CurrentDb.Execute strSQL, dbFailOnError
The cursor shows "dbFailOnError = 128"

I've done a quick search of the forum for this, but I haven't found anything similar to what I'm doing. I don't see any reason why it should get hung up here.
Jul 16 '10 #25

nico5038
Expert 2.5K+
P: 3,072
The 128 is the numerical value of the dbFailOnError parameter.

The error is caused by a typo (or non existing field) as described earlier.

In general to be able to update a database you should use tables in the UPDATE statement and no queries, as they are often "read only".

It might help when you attach (part of) the .mdb here so NeoPa and I can have a look.

Nic;o)
Jul 16 '10 #26

NeoPa
Expert Mod 15k+
P: 31,769
This is more complicated than I thought. There appear to be a number of issues involved in this which tends to muddy the waters somewhat. Parodux saw one of them and tried to help, but I'm afraid I didn't realise what he was saying clearly enough.

When you update a recordset (normally a simple table but it can be more complicated) and want to filter out some of the records, the filter should be specified relative to the recordset available. In your case you seem to be attempting something akin to :
Update all records in A where items in B qualify.
As there is no correlation defined between A and B the SQL engine hasn't enough information to work out what you want. [ManualContacts].[Agency Type] is something in the SQL that the SQL engine has no reference for. Hence it asks for the extra parameter. It would help here to have a clearer idea of what you want to do and why. Particularly why the filtering would pertain to a separate table.

The other issue is the one I mentioned before. When there is no filtering, remove the part of the string that filters rather than filter against an empty string. The latter will have a completely different effect than the one required. Essentially bypassing most, if not all, of the records.

As for posting code. It certainly helps if SQL is formulated for reading within the relatively narrow confines of our CODE boxes, but it will take any width. Using the CODE boxes is mandatory. Posting wide data in there simply puts people off from helping you.
Jul 16 '10 #27

P: 26
@NeoPa
thanks for clearing this issue.. I was getting a bit tense here!~)
Jul 16 '10 #28

NeoPa
Expert Mod 15k+
P: 31,769
My apologies. I had been getting a bit confused myself I must admit.

Normally we like to encourage different posters to submit their ideas, even if they sometimes disagree. In some cases though, like this one, this can make it somewhat difficult for the OP to manage. It is still the right thing to do in my opinion. Just that sometimes it gets complicated. Better complicated than answers not offered though. It's rare enough to be a small issue.

By the way, it's always good to see a new member posting help so early. Welcome to Bytes!
Jul 16 '10 #29

P: 26
thanks, now I'm going to stay!~)
Jul 16 '10 #30

bre1603
P: 39
Well I certainly don't want to put anyone off from helping me. I've never posted on a forum in my life though, so I might need a bit of time to learn the ropes. Thank you in advance for your understanding.
NeoPa: It would help here to have a clearer idea of what you want to do and why. Particularly why the filtering would pertain to a separate table.
Maybe we should go back to the beginning (pre confusing-cross-replies).

I have two tables, one with agency information, "ManualContacts" and one with the agency leaders' contact information, "Secondary ManualContacts Table". These tables make up the query "qryLeadershipEmailListALL" which my form "LeadershipEmailListALL" is based on.

The form is a continuous form that shows the following fields for each record:
[Send Email] – a checkbox control
[Agency Number] – text control
[Agency Type] – text control
[Agency Name] – text control
[Leadership Contact] – text control
[Leadership Email] – text control

In the form header, there is a checkbox control, [SelectAll] that fills and clears all the checkboxes below with an event procedure in the OnClick event. The form header also has a combo box, [cbofltrAgencyType] that uses a query to filter the records by Agency Type (code takes place in the AfterUpdate event).
(There are command buttons in the header as well that allow the user to email selected contacts or create mailing labels for them.)

What I need is for the user to be able to filter the form to show only one agency type and then use the [SelectAll] checkbox to select all the records of that agency type to either email or create mailing labels.

The code I was using in the [SelectAll] OnClick event updates the Secondary Manual Table, and it works great. But not when the data is filtered. In that case, even records that are filtered out still have [Send Email] selected/updated to True or False, making the filtering pointless.

That’s why I was trying to alter the code to include the filter as a parameter to the Update clause.

So is there a way that I can get what I want out of this form? If not, I'll be building 5 identical forms, one per agency type.

I really hope that's not the case...
Jul 16 '10 #31

NeoPa
Expert Mod 15k+
P: 31,769
If the results of that QueryDef ([cbofltrAgencyType]) are updatable (I expect they are otherwise you wouldn't be able to make any amendments to it via the form) then do your UPDATE query on that ([cbofltrAgencyType]) instead of the table.

That last post makes everything clear again. Well done. That's not often easy.
Jul 16 '10 #32

bre1603
P: 39
@NeoPa
Thank you. I'm finding it's very easy to get muddled going back and forth between posts! I'm happy to clarify because it helps me get back to what I was trying to do in the first place. And I figure if I can explain it, I understand it (which is good).

And thank you - I'll give it a try and let you know how it goes.
Jul 16 '10 #33

NeoPa
Expert Mod 15k+
P: 31,769
Please do. I'm confident we have it cracked now though :)
Jul 16 '10 #34

bre1603
P: 39
SUCCESS!!! Well, sort of. I'm considering it a definite step in the right direction.

For the first time, I was able to select just the filtered records using [SelectAll]. However, now I can't select anything with the [SelectAll] checkbox if there's not a filter applied. I thought I took care of this in the code, but it would appear not. Here's what I've got:

Expand|Select|Wrap|Line Numbers
  1. Private Sub SelectAll_Click()
  2. On Error GoTo SelectAll_Error
  3.  
  4. If Me.Dirty Then Me.Dirty = False
  5.  
  6.     Dim strSQL As String
  7.  
  8.   If Me.SelectAll = True Then
  9.     If Me.cbofltrAgencyType = Null Then
  10.         strSQL = "UPDATE qryLeadershipEmailListALL SET qryLeadershipEmailListALL.[Send Email] = True"
  11.         strSQL = strSQL & " WHERE [qryLeadershipEmailListALL].[Send Email] = False"
  12.         CurrentDb.Execute strSQL, dbFailOnError
  13.         Me.Refresh
  14.         Else
  15.             strSQL = "UPDATE qryLeadershipEmailListALL SET qryLeadershipEmailListALL.[Send Email] = True"
  16.             strSQL = strSQL & " WHERE [qryLeadershipEmailListALL].[Send Email] = False AND [qryLeadershipEmailListALL].[Agency Type]= '" & Me.[cbofltrAgencyType] & "'"
  17.             CurrentDb.Execute strSQL, dbFailOnError
  18.             Me.Refresh
  19.         End If
  20.  
  21.     ElseIf Me.SelectAll = False Then
  22.         If Me.cbofltrAgencyType = Null Then
  23.             strSQL = "UPDATE [qryLeadershipEmailListALL] SET [qryLeadershipEmailListALL].[Send Email] = False"
  24.             strSQL = strSQL & " WHERE [qryLeadershipEmailListALL].[Send Email] = True"
  25.             CurrentDb.Execute strSQL, dbFailOnError
  26.             Me.Refresh
  27.             Else
  28.                 strSQL = "UPDATE [qryLeadershipEmailListALL] SET [qryLeadershipEmailListALL].[Send Email] = False"
  29.                 strSQL = strSQL & " WHERE [qryLeadershipEmailListALL].[Send Email] = True AND [qryLeadershipEmailListALL].[Agency Type]= '" & Me.[cbofltrAgencyType] & "'"
  30.                 CurrentDb.Execute strSQL, dbFailOnError
  31.                 Me.Refresh
  32.             End If
  33.     End If
  34.  
  35. SelectAll_Exit:
  36.         Exit Sub
  37. SelectAll_Error:
  38.         MsgBox "Error# " & Err.Number & " " & Err.Description
  39.         Resume SelectAll_Exit
  40.  
  41. End Sub
But at least I'm not erroring out! :)
Jul 16 '10 #35

bre1603
P: 39
I feel like it might have something to do with the combo box/filter [cbofltrAgencyType]. After filter is removed / when the box is empty, [SelectAll] still sees only the filtered records. Those filtered out initially are left "un-selected."

Here's the code for the combo box:

Expand|Select|Wrap|Line Numbers
  1. Private Sub cbofltrAgencyType_AfterUpdate()
  2. On Error GoTo cbofltrAgencyType_AfterUpdate_Err
  3.  
  4.     If Me.cbofltrAgencyType = Null Then
  5.         Me.FilterOn = False
  6.         Me.Filter = vbNullString
  7.         Else
  8.             DoCmd.OpenForm "LeadershipEmailListALL", acNormal, "qryLeadershipEmailListALL_Filter"
  9.              ' Find the record that matches the control.
  10.             Forms![LeadershipEmailListALL].RecordsetClone.FindFirst "[Agency Type] = '" & Me![cbofltrAgencyType] & "'"
  11.             Forms![LeadershipEmailListALL].Bookmark = Forms![LeadershipEmailListALL].RecordsetClone.Bookmark
  12.            'Forms![LeadershipEmailListALL].cbofltrAgencyType = ""
  13.             End If
  14.  
  15. cbofltrAgencyType_AfterUpdate_Exit:
  16.     Exit Sub
  17.  
  18. cbofltrAgencyType_AfterUpdate_Err:
  19.     If Err.Number = 2465 Then
  20.         'Forms![LeadershipEmailListALL].cbofltrAgencyType = ""
  21.         Resume cbofltrAgencyType_AfterUpdate_Exit
  22.     Else
  23.         MsgBox Error$
  24.         Me.FilterOn = False
  25.         Me.Filter = vbNullString
  26.     End If
  27.     Resume cbofltrAgencyType_AfterUpdate_Exit
  28.  
  29. End Sub
Any ideas?
Jul 16 '10 #36

NeoPa
Expert Mod 15k+
P: 31,769
Expand|Select|Wrap|Line Numbers
  1. If Me.cbofltrAgencyType = Null Then
On line #4 you are comparing the value against the value of Null. This doesn't work as Null is an absence of a value. IsNull() returns True or False depending on the contents, but the value can never be equal to Null. This should read :
Expand|Select|Wrap|Line Numbers
  1. If IsNull(Me.cbofltrAgencyType) Then
Jul 19 '10 #37

NeoPa
Expert Mod 15k+
P: 31,769
bre1603: For the first time, I was able to select just the filtered records using [SelectAll]. However, now I can't select anything with the [SelectAll] checkbox if there's not a filter applied.
I see nothing wrong with your code (The logic at least). Possibly using parentheses around the separate sections joined together by AND may help in the WHERE clause, but fundametally nothing I'm afraid.

I would suggest however, especially when posting publicly, but also for your own benefit, that you use indenting more consistently. Indenting is not random, and if it's used at all (Highly recommended) it absolutely must be done consistently so all code that is at the same level is indented the same amount. I say must, because indenting without consistency is (a lot) worse than no indenting at all. It is actually misleading and uncomfortable to read (as you get normal clues from it, but you have to ignore them as they are often meaningless).

This may sound critical, but it's not supposed to. I merely try to help you understand the importance, and ultimately the benefits, of properly indenting code (and the reverse of course).

While I'm here, why don't I give an example, and I'll simplify the code somewhat while I'm about it :
Expand|Select|Wrap|Line Numbers
  1. Private Sub SelectAll_Click()
  2.     Dim strSQL As String, strSet As String, strClear As String
  3.  
  4. On Error GoTo SelectAll_Error
  5.  
  6.     With Me
  7.         'Save record if changes pending
  8.         If .Dirty Then .Dirty = False
  9.  
  10.         strSet = Format(.SelectAll, "True/False")
  11.         strClear = IIf(strSet = "True", "False", "True")
  12.         strSQL = "UPDATE [qryLeadershipEmailListALL] " & _
  13.                  "SET    [Send Email]=%S " & _
  14.                  "WHERE  ([Send Email]=%C)"
  15.         'Apply the correct True/False or False/True depending on [SelectAll]
  16.         strSQL = Replace(strSQL, "%S", Format(.SelectAll, "True/False")
  17.         strSQL = Replace(strSQL, "%C", Format(Not .SelectAll, "True/False")
  18.  
  19.         'Only if necessary add the AgencyType filtering
  20.         If Not IsNull(.cbofltrAgencyType) Then _
  21.             strSQL = strSQL & " AND ([Agency Type]='" & .[cbofltrAgencyType] & "')"
  22.         'SQL all ready to execute now
  23.         CurrentDb.Execute strSQL, dbFailOnError
  24.         Me.Refresh
  25.     End With
  26.  
  27. SelectAll_Exit:
  28.     Exit Sub
  29.  
  30. SelectAll_Error:
  31.     MsgBox "Error# " & Err.Number & " " & Err.Description
  32.     Resume SelectAll_Exit
  33. End Sub
Jul 19 '10 #38

bre1603
P: 39
Okay, I made the changes you suggested - I added parentheses around the separate sections in the WHERE clause, and changed “= Null” to “IsNull()”.

And good news, it worked! I think it was changing the Null code that really did it. I changed it in the [cbofltrAgencyType] code as well as in the [SelectAll] coding, and that made all the difference.

I’ve been struggling with this form for some time now and I’m ecstatic now that it’s working how I wanted it to. Thank you so much for all your help.

Your simplified code was much, well, simpler! I’m not to the point where I can write code that looks like that (I tend to take the long way around), but maybe someday. ;)

I’m pretty new to VBA and I’m still figuring out all the nuances (there seem to be a lot, so I don’t feel too bad about it). But I like trying it and I learn something every time! Thanks for the tip on using indenting. I was aware it was preferred, but not that it was super important. I’ll have to educate myself more on it now that I know I’m not doing it correctly/consistently. Maybe I’ll be able to clean up some coding in the process.

Thanks again for all the help NeoPa and Nico!
Jul 19 '10 #39

bre1603
P: 39
P.S. I found some code that works very well with the SelectAll checkbox, and also worked with my filter combo box. If I'd been able to make it work sooner, I might have saved myself some time and frustration. For those of you wishing to avoid that part, you might try this first:

"Loop through checkboxes using ADO - Access 2003" (the code works for DAO too). Here's the link to the string:

http://bytes.com/topic/access/answer...-access-2003-a

Good luck. :)
Jul 19 '10 #40

NeoPa
Expert Mod 15k+
P: 31,769
On the plus side - you learnt about a much better way (SQL is so much more scalable than Recordset coding). Learning is often the result of painful times. In this case I'm sure the benefits outweigh the setbacks.
Jul 19 '10 #41

bre1603
P: 39
Too true... I'm glad I pushed through - it was a close one. But I do learn the most when I'm about to go insane. And it makes the solution that much sweeter, of course.

And this "experience" helps me in more ways than one. By applying my new knowledge to a sister form, it will mean building one form instead of five. And the user gets what he wants, which is good news for him and me! Those are pretty big benefits...
Jul 19 '10 #42

Post your reply

Sign in to post your reply or Sign up for a free account.