Create Command Button to select / deselect all in a yes/no field

[Valery]

Hi All,

I am running MS Access 2010. I would like to have a toggle button that selects and deselects a check box field. I have this SQL but it is returning a compile error.

Can you please assist? (please provide FULL coding - I did not write this one - I am at a beginner-intermediate level).

Private Sub cmdLabelFlagSelectAll_Click()
    If Me.cmdLabelFlagSelectAll.Caption = "Select All" Then
       strSQL = "UPDATE [qry_R_LabelSelectAll] SET [LabelFlag] = True"
              CurrentDb.Execute strSQL, dbFailOnError
              Me.Requery
     Else
       strSQL = "UPDATE [qry_R_LabelDeselectAll] SET [LabelFlag] = False"
              CurrentDb.Execute strSQL, dbFailOnError
              Me.Requery
     End If
End Sub

These are the queries:

qry_R_LabelSelectAll

UPDATE tblTenant SET tblTenant.LabelFlag = -1
WHERE (((tblTenant.MailList)=True) AND ((tblTenant.Deactivate)="N") AND ((tblTenant.Status)="M" Or (tblTenant.Status)="F"));

qry_R_LabelDeselectAll

UPDATE tblTenant SET tblTenant.LabelFlag = 0;

 

[pbaldy]

What's the error message? You never declare strSQL.

 

[Valery]

Thank you Baldy for responding. Hope you can help.

It comes up with Compile error: Variable not defined. It highlights the first instance of the word "strSQL".

I would not know how to "declare strSQL". Sorry.

 

[pbaldy]

Dim strSQL As String

 

[pbaldy]

In all your other code, where are the Dim statements? I'm guessing right at the beginning.

 

[Valery]

I tried it before I answered you. Sorry should have sent this: Returns:
Run-time error 3417 - An action query cannot be used as a row source + highlights line CurrentDbExecute strSQL, dbFailOnError, in yellow.

Why and how does it work for you?

Here is the code, updated, as I think you meant:

Private Sub cmdLabelFlagSelectAll_Click()
Dim strSQL As String
    If Me.cmdLabelFlagSelectAll.Caption = "Select All" Then
       strSQL = "UPDATE [qry_R_LabelSelectAll] SET [LabelFlag] = True"
              CurrentDb.Execute strSQL, dbFailOnError
              Me.Requery
     Else
       strSQL = "UPDATE [qry_R_LabelUnselectAll] SET [LabelFlag] = False"
              CurrentDb.Execute strSQL, dbFailOnError
              Me.Requery
     End If
End Sub

 

[pbaldy]

Oh, I didn't look at the queries. You can't update an UPDATE query. I'd expect a table there, perhaps a SELECT query.

 

[pbaldy]

Are you just trying to run those queries? If so:

CurrentDb.Execute "qry_R_LabelSelectAll", dbFailOnError

 

[Valery]

Thank you for trying to assist. As mentioned in my thread:

I am running MS Access 2010. I would like to have a toggle button that selects and deselects a check box field. I have this SQL but it is returning a compile error.

Can you please assist? (please provide FULL coding - I did not write this one - I am at a beginner-intermediate level).

Does anyone know how to update the code? Thank you

 

[pbaldy]

No offense, but you've been here a while. Try to understand what people give you. Ask questions. Don't just paste code into your db. Try

    If Me.cmdLabelFlagSelectAll.Caption = "Select All" Then
       CurrentDb.Execute "qry_R_LabelSelectAll", dbFailOnError
     Else
       CurrentDb.Execute "qry_R_LabelUnselectAll", dbFailOnError
     End If
     Me.Requery

 

[Valery]

No offense taken - I DID TRY and way before even posting this thread. Also, it is not as if I came here empty handed. I created the queries, I applied coding behind two buttons I created and did my best to manipulate the event to make it work - it did not.

How can you judge me like that? How do you know the amount of effort I put into learning and trying?

I did clearly indicate I would like a full code - because indeed I had tried and I knew that whatever was missing or needed to be added was beyond my level of comprehension - at this time. I don't see any humiliation in that. People can help out or not. Bless the ones who do.

TY

 

[pbaldy]

Did you notice that I gave you full code to try?

 

[Valery]

I sure did - have not had a minute - son is sick and wants "mommy"~!. Thank you Baldy - Much Appreciated! Once I have it working, I will rate and mark the thread as successfull and complete.

 

[Valery]

Hi Paul,

2am here and finally got a chance to apply the code you generously provided. Now it seems to work - not getting any error message. But there are two things:

1) When the records are flagged, some of the other fields are "reacting" - "shaking / moving"... it is just eerie. Like the form is going to crash or something. Don't know that anything can be done about it.

2) I understood (and could be wrong!!) that the code indicates the command button is a toggle button. But it does not deselect. No error, but just does nothing, on "re-click".

Have no idea what I can do...

Can anyone help? Really need this event for the labels.

THANK YOU.

 

[pbaldy]

The code does not indicate the button is a toggle button. It tests the caption, which is presumably set elsewhere. You'd test True/False for a toggle:

If Me.cmdLabelFlagSelectAll = True Then

 

[Valery]

Please - UPDATE - thank you.

    If Me.cmdLabelFlagSelectAll.Caption = "Select All" Then
       CurrentDb.Execute "qry_R_LabelSelectAll", dbFailOnError
     Else
       CurrentDb.Execute "qry_R_LabelUnselectAll", dbFailOnError
     End If
     Me.Requery