Option group not working

[ClaraBarton]

To keep from dragging things down I have a default of choosing only the current year transactions.

1. If I choose Current year, it works
2. If I choose All, it works
3. If I choose Current year after choosing all, it works
4. I cannot choose All after choosing current year. It just keeps returning current year even if I click on a new account. It stays current year.

5. Private Sub TransactionBtn_Click()
   Dim intDate As Integer
   intDate = Me!frameLimit
       
       Select Case intDate
           Case 1   'Current Year
               DoCmd.OpenForm "frmRegister", _
                WhereCondition:="Year([CkDate]) = " & Year(Date)
   
           Case 2    'All Transactions
               DoCmd.OpenForm "frmRegister"
                   
       End Select
         
           'Change register if click on an different account
       If IsLoaded("frmRegister") Then
           Forms!frmRegister.Requery
       End If
   End Sub

 

[theDBguy]

When you say you cannnot choose All, are you saying the selection doesn't change, or just the code is not branching to that block?

 

[ClaraBarton]

The selection works fine, just the transactions don't change. I wonder if having no WhereCondition assumes the same as first? How do you say "no WhereCondition"?

 

[pbaldy]

If the form is already open, change its FilterOn property to False in your code.

 

[Pat Hartman]

Make sure the form is closed before you open it. Opening a form that is already open does not requery the RecordSource. You can add additional filters and so that is why current works after all but not vice versa.

 

[ClaraBarton]

It needs to stay open.... I switch between accounts on one form to change the transactions on the other. Same for date. Note the last three lines. Would a requery be required somewhere? I tried one before option 2 but doesn't change the records.

 

[theDBguy]

The selection works fine, just the transactions don't change. I wonder if having no WhereCondition assumes the same as first? How do you say "no WhereCondition"?

What happens if you try it this way?

    Select Case intDate
        Case 1   'Current Year
            DoCmd.OpenForm "frmRegister", _
             WhereCondition:="Year([CkDate]) = " & Year(Date)

        Case 2    'All Transactions
            DoCmd.OpenForm "frmRegister", WhereCondition:="1=1"
              
    End Select

 

[ClaraBarton]

FilterOn=False did the trick! Thank you so much!

 

[pbaldy]

FilterOn=False did the trick! Thank you so much!

Happy to help! Since the form stays open, rather than having your code open it, set the Filter and FilterOn properties as appropriate to the selection.

 

[ClaraBarton]

WhereCondition:="1-1" Works too! What does that even mean? Thank you for your help.

 

[theDBguy]

WhereCondition:="1-1" Works too! What does that even mean? Thank you for your help.

It's the inverse of no filter - like all filter.

 

[cheekybuddha]

WhereCondition:="1-1" Works too! What does that even mean? Thank you for your help.

You really want:

WhereCondition:="1=1"

(But by dumb luck "1-1" will also work)

 

[cheekybuddha]

You could also adjust your logic slightly:

Private Sub TransactionBtn_Click()
     
  If IsLoaded("frmRegister") Then
    Forms.frmRegister.Requery
  Else
    DoCmd.OpenForm "frmRegister", _
                   WhereCondition:="Year([CkDate]) = " & Year(Date)
  End If
  Forms.frmRegister.FilterOn = (Me.frameLimit = 1)
 
End Sub

 

[The_Doc_Man]

WhereCondition:="1-1" Works too! What does that even mean? Thank you for your help.

The WHERE condition is actually just a list of sub-expressions linked by AND, OR, or XOR, and with possible prefixes of NOT. Usually each sub-expression is a tiny conditional expression that will evaluate to TRUE or FALSE. Like "X>3" or "A=B" or "Name LIKE 'Smith*'" A record is selected when its WHERE clause returns a TRUE for whatever is in that clause. Therefore, a clause like "WHERE 1=1" returns TRUE no matter what is in the record. Not sure what you mean by "1-1 works", because that would evaluate to FALSE uniformly and thus should return NO records.

 

[cheekybuddha]

Not sure what you mean by "1-1 works", because that would evaluate to FALSE uniformly and thus should return NO records.

I didn't test, but wondered whether it might be interpreted as a literal string rather than an expression - obviously, now that I have had some sleep, I can see that it shouldn't (be interpreted as a string, since there are no quote marks) so I'm not sure why it worked for the OP ???

 

[The_Doc_Man]

IF it happens to be interpreted as a string for some reason, then the rules for BOOLEAN coercion are (I believe) that the first byte of the string compels the value. In the case of ASCII strings, only a string of one or two ASCII NUL characters would return FALSE. All other strings would return TRUE. It IS possible that the first TWO characters would compel the value since, if I recall correctly, the BOOLEAN data type is actually not a BYTE integer but instead is a WORD integer. But I would have to look up string-to-Boolean coercion to be sure and at the moment I have something else going on.

 

[cheekybuddha]

IF it happens to be interpreted as a string for some reason

No, it was just late last night when I was replying and ClaraBarton said it worked so I didn't double-check against what DBG actually typed to see that it was actually a typo.

So my needing sleep state conjured up a reason why it might have worked (interpretation as a string literal).

Really I was trying to write:
"If it works for you then it is down to dumb luck"

 

[ClaraBarton]

Sorry! 1-1 was just a typo. I used 1=1!