Checkboxes in a search form and VBA code

[Nicawette]

Dear all,

I've created a search form which contains different criteria to search
into a table "events". When I perform a search, a new form
"Browse_all_events" is open with the filtered results.

In the search form I've as criteria combo boxes (e.g. "Component" ) but
I want to insert also checkboxes. These check boxes will also refer to
the "events" table. For example I've a checkbox in the search form
called "service" which refers to the field "service" into the "events"
table. I want when I tick the Check box in the search form and perform
a search that the records found have been filtered with the field
"service"=true.

Here's the code

Private Sub Search_Click()
Const cInvalidDateError As String = "You have entered an invalid
date."
Dim strWhere As String
Dim strError As String
Dim CBValue As Variant

strWhere = "1=1"

If Nz(Me.Component) <> "" Then
strWhere = strWhere & " AND " & "Issues.Component = '" &
Me.Component & "'"
End If

' If service
If Me.service = True Then
CBValue = CBValue & " AND " & "Events.service = True" &
Me.service & "True"
End If

If strError <> "" Then
MsgBox strError

Else
'DoCmd.OpenForm "Browse Events", acFormDS, , strWhere, CBValue,
acFormEdit, acWindowNormal
If Not Me.FormFooter.Visible Then
Me.FormFooter.Visible = True
DoCmd.MoveSize Height:=Me.WindowHeight +
Me.FormFooter.Height
End If
Me.Browse_All_Events.Form.Filter = strWhere
Me.Browse_All_Events.Form.Filter = CBValue
Me.Browse_All_Events.Form.FilterOn = True
End If

But when I perform a search I've an error code for the checkbox, Could
you please check this code?

The code for the combo boxes is working.

Thank you

 

[Keith Wilby]

Dear all,

I've created a search form which contains different criteria to search
into a table "events". When I perform a search, a new form
"Browse_all_events" is open with the filtered results.

In the search form I've as criteria combo boxes (e.g. "Component" ) but
I want to insert also checkboxes. These check boxes will also refer to
the "events" table. For example I've a checkbox in the search form
called "service" which refers to the field "service" into the "events"
table. I want when I tick the Check box in the search form and perform
a search that the records found have been filtered with the field
"service"=true.

Here's the code

Private Sub Search_Click()
Const cInvalidDateError As String = "You have entered an invalid
date."
Dim strWhere As String
Dim strError As String
Dim CBValue As Variant

strWhere = "1=1"

If Nz(Me.Component) <> "" Then
strWhere = strWhere & " AND " & "Issues.Component = '" &
Me.Component & "'"
End If

' If service
If Me.service = True Then
CBValue = CBValue & " AND " & "Events.service = True" &
Me.service & "True"
End If

If strError <> "" Then
MsgBox strError

Else
'DoCmd.OpenForm "Browse Events", acFormDS, , strWhere, CBValue,
acFormEdit, acWindowNormal
If Not Me.FormFooter.Visible Then
Me.FormFooter.Visible = True
DoCmd.MoveSize Height:=Me.WindowHeight +
Me.FormFooter.Height
End If
Me.Browse_All_Events.Form.Filter = strWhere
Me.Browse_All_Events.Form.Filter = CBValue
Me.Browse_All_Events.Form.FilterOn = True
End If

But when I perform a search I've an error code for the checkbox, Could
you please check this code?

The code for the combo boxes is working.

Thank you

You've told us everything but the error message! Also, your code would be
easier to follow if your object names had prefixes such as "txt" for a text
box and "chk" for a check box.

This line looks wrong:

CBValue = CBValue & " AND " & "Events.service = True" & Me.service & "True"

That would appear to equate to whatever's in CBValue and " And
Events.service = True <False/True> True

but like I said, following code without prefixes is difficult (for me at any
rate).

Regards,
Keith.

 

[Nicawette]

Dear Keith thank you for you answer, you are right,

I'am returned the following error: Run-time error '3075' Syntax error
(missing operator) in query expression ' And Events.Service =
True-1True'

for the following code where Me.Service is the checkbox and
Events.service is the field in the table events.

If Me.service = True Then
CBValue = CBValue & " AND " & "Events.service = True" &
Me.ICTserv & "True"
End If

any proposal?

Thank you

Nic


Keith Wilby a écrit :

 

[Keith Wilby]

Dear Keith thank you for you answer, you are right,

I'am returned the following error: Run-time error '3075' Syntax error
(missing operator) in query expression ' And Events.Service =
True-1True'

for the following code where Me.Service is the checkbox and
Events.service is the field in the table events.

If Me.service = True Then
CBValue = CBValue & " AND " & "Events.service = True" &
Me.ICTserv & "True"
End If

any proposal?

Well CBValue needs to contain a credible string at run-time as your "where"
clause, so :

" And Events.Service = True-1True" should read

" And Me.chkService = -1"

assuming that "chkService" is the name of the check box on your form that is
bound to the field "Service" in the table "Events".

Instead of CBValue = CBValue & " AND " & "Events.service = True" &
Me.ICTserv & "True" try

CBValue = CBValue & " AND " & Me.chkService = -1 " AND " & Me.ICTserv = -1