"Where" Problems When Passing Parameters.

[Guest]

Problem:

I receive the 'OpenForm Action was canceled' pop-up. Code works for CmdRegions...But for CmbAccess/[Electronic Evaluations?] it does not. As [Electronic Evaluations?] is a Yes/No field, could this be the problem...and what code would work for Yes/No checkboxes?

Code:

Private Sub CmdOK_Click()
On Error GoTo Err_CmdOK_Click
Dim strWhere As String
Dim strLink As String
Const strcJetDate = "\#mm\/dd\/yyyy\#"
If Len(Me.CmbFilter & vbNullString) = 0 Then
MsgBox "Please select one of the options.", vbInformation, "Selection Required"
ElseIf Me.CmbFilter = "See All" Then
DoCmd.OpenForm "Clients Test"
DoCmd.Close acForm, "Clients w Filters"
ElseIf Me.CmbFilter = "Filter By.." Then
If Not IsNull(Me.CmbRegions) And CmbRegions.Value <> "All" Then
strWhere = strWhere & strLink & "[Regions]=""" & CmbRegions & """"
strLink = " And "
If Me.CmbRegions.Value = All Then
strWhere = strWhere & strLink
strLink = " And "
End If
End If
If Not IsNull(Me.CmbAccess) And CmbAccess.Value <> "All" Then
strWhere = strWhere & strLink & "[Electronic Evaluations?]=""" & CmbAccess & """"
strLink = " And "
If Me.CmbAccess.Value = "All" Then
strWhere = strWhere & strLink
strLink = " And "
End If
End If
DoCmd.OpenForm "Clients Test", acNormal, , strWhere
DoCmd.Close acForm, "Clients w Filters"

 

[SteveS]

Problem:

I receive the 'OpenForm Action was canceled' pop-up. Code works for CmdRegions...But for CmbAccess/[Electronic Evaluations?] it does not. As [Electronic Evaluations?] is a Yes/No field, could this be the problem...and what code would work for Yes/No checkboxes?

Code:

Private Sub CmdOK_Click()
On Error GoTo Err_CmdOK_Click
Dim strWhere As String
Dim strLink As String
Const strcJetDate = "\#mm\/dd\/yyyy\#"
If Len(Me.CmbFilter & vbNullString) = 0 Then
MsgBox "Please select one of the options.", vbInformation, "Selection Required"
ElseIf Me.CmbFilter = "See All" Then
DoCmd.OpenForm "Clients Test"
DoCmd.Close acForm, "Clients w Filters"
ElseIf Me.CmbFilter = "Filter By.." Then
If Not IsNull(Me.CmbRegions) And CmbRegions.Value <> "All" Then
strWhere = strWhere & strLink & "[Regions]=""" & CmbRegions & """"
strLink = " And "
If Me.CmbRegions.Value = All Then
strWhere = strWhere & strLink
strLink = " And "
End If
End If
If Not IsNull(Me.CmbAccess) And CmbAccess.Value <> "All" Then
strWhere = strWhere & strLink & "[Electronic Evaluations?]=""" & CmbAccess & """"
strLink = " And "
If Me.CmbAccess.Value = "All" Then
strWhere = strWhere & strLink
strLink = " And "
End If
End If
DoCmd.OpenForm "Clients Test", acNormal, , strWhere
DoCmd.Close acForm, "Clients w Filters"

A Yes/No field evaluates to 0 (No) or -1 (Yes) so you shouldn't have
quotes in the strWhere for [Electronic Evaluations?] field.

You could move the line: strLink = " And " above the first IF statement
and remove the four lines in the code.

Also, if this isn't all of the code, then you are missing an 'End If'.

Here is the modified code: (watch for line wrap)

'-----------------------
Private Sub CmdOK_Click()
On Error GoTo Err_CmdOK_Click
Dim strWhere As String
Dim strLink As String

Const strcJetDate = "\#mm\/dd\/yyyy\#"

strLink = " And "

If Len(Me.CmbFilter & vbNullString) = 0 Then
MsgBox "Please select one of the options.", vbInformation,
"Selection Required"
ElseIf Me.CmbFilter = "See All" Then
DoCmd.OpenForm "Clients Test"
DoCmd.Close acForm, "Clients w Filters"
ElseIf Me.CmbFilter = "Filter By.." Then
If Not IsNull(Me.CmbRegions) And CmbRegions.Value <> "All" Then
strWhere = strWhere & strLink & "[Regions]=""" & CmbRegions & """"
If Me.CmbRegions.Value = All Then
strWhere = strWhere & strLink
End If
End If
If Not IsNull(Me.CmbAccess) And CmbAccess.Value <> "All" Then
strWhere = strWhere & strLink & "[Electronic Evaluations?]=" &
CmbAccess
If Me.CmbAccess.Value = "All" Then
strWhere = strWhere & strLink
End If
End If
DoCmd.OpenForm "Clients Test", acNormal, , strWhere
DoCmd.Close acForm, "Clients w Filters"

'-----------------------

HTH