Search form with a checkbox.

[JOSELUIS via AccessMonster.com]

There is a field in tblContacts named Residents.txt which is a yes/no field.
What I´m trying to create is a checkbox in my fdlgsearch [chkResidents] which
filters my records. I mean if [chkResidents] is not selected all the records
should appear but if [chkResidents] = True only the records in my
frmContacts that are residents should appear.
I have put the following code of a sample database but it doesn´t work.
Could anybody give me a hint ?

Private Sub cmdSearch_Click()
Dim varWhere As Variant
Dim rst As DAO.Recordset

' Initialize to Null
varWhere = Null
...
' Do Residentes next
If (Me.chkResidentes = True) Then
' Build a filter to include only resident contacts
varWhere = (varWhere + " AND ") & _
"(Residentes = True)"

Set rst = CurrentDb.OpenRecordset("SELECT tblContacts.* FROM tblContacts
WHERE " & varWhere)
Whenever I tryed to execute the search an Error # 3061#is displayed and
the code is interrupted in this line.

 

[Daryl S]

Joseluis -

I suspect you have a leading AND in your WHERE clause. Right before your
'Set rst' statement, add a debug.print line, like this:

Debug.print varWhere

The results will be in the immediate window when you step through the code.
I suspect your varWhere is "AND (Residentes = True), but if there are no
other prior conditions, it should just be (Residentes = True), that is,
without the AND. Since not all the code is in your posting, it is hard to
tell.

If this doesn't resolve it, post the value of the varWhere by copy/pasting
it from the immediate window (from the debug.print) into your next posting.

 

[JOSELUIS via AccessMonster.com]

Thank you Daryl S for your time , the code os the fdlgSearch is as following:

Private Sub cmdSearch_Click()
Dim varWhere As Variant
Dim rst As DAO.Recordset

' Initialize to Null
varWhere = Null


' OK, start building the filter
' If specified a contact type value
If Not IsNothing(Me.cmbEmpleo) Then
' .. build the predicate
varWhere = "(Empleo = '" & Me.cmbEmpleo & "')"
End If

' Do Last Name next
If Not IsNothing(Me.txtLastName) Then
' .. build the predicate
' Note: taking advantage of Null propogation
' so we don't have to test for any previous predicate
varWhere = (varWhere + " AND ") & "([LastName] LIKE '" & Me.
txtLastName & "*')"
End If

' Do First Name next
If Not IsNothing(Me.txtFirstName) Then
' .. build the predicate
varWhere = (varWhere + " AND ") & "([FirstName] LIKE '" & Me.
txtFirstName & "*')"
End If

' Do Company next
If Not IsNothing(Me.cmbCompanyID) Then
' .. build the predicate
' Must use a subquery here because the value is in a linking table...
varWhere = (varWhere + " AND ") & _
"([ContactID] IN (SELECT ContactID FROM qryCompanyContacts " & _
"WHERE qryCompanyContacts.CompanyID = " & Me.cmbCompanyID & "))"
End If

' Do City next
If Not IsNothing(Me.txtCity) Then
' .. build the predicate
' Test for both Work and Home city
varWhere = (varWhere + " AND ") & "(([WorkCity] LIKE '" & Me.txtCity
& "*')" & _
" OR ([HomeCity] LIKE '" & Me.txtCity & "*'))"
End If

' Do State next
If Not IsNothing(Me.txtDNI) Then
' .. build the predicate
' Test for both Work and Home state
varWhere = (varWhere + " AND ") & "(([DNI] LIKE '" & Me.txtDNI & "*'))
"
End If
' Do Residentes next
If (Me.chkResidentes = True) Then
' Build a filter to exclude no resident contacts
varWhere = "(Residentes = True)"

End If

' Check to see that we built a filter
If IsNothing(varWhere) Then
MsgBox "Debe introducir al menos un criterio de busqueda.",
vbInformation, gstrAppTitle
Exit Sub
End If

' Open a recordset to see if any rows returned with this filter
Set rst = CurrentDb.OpenRecordset("SELECT tblContacts.* FROM tblContacts
WHERE " & varWhere)
' See if found none
If rst.RecordCount = 0 Then
MsgBox "Ninguna persona aparece con este criterio.", vbInformation,
gstrAppTitle
' Clean up recordset
rst.Close
Set rst = Nothing
Exit Sub
End If

' Hide me to fix later focus problems
Me.Visible = False
' Move to last to find out how many
rst.MoveLast
' If 5 or less or frmMembers already open,
If (rst.RecordCount < 6) Or IsFormLoaded("frmContacts1") Then
' Open Contacts filtered
' Note: if form already open, this just applies the filter
DoCmd.OpenForm "frmContacts1", WhereCondition:=varWhere
' Make sure focus is on contacts
Forms!frmContacts1.SetFocus
Else
' Ask if they want to see a summary list first
If vbYes = MsgBox("Su busqueda encontró " & rst.RecordCount & "
personas. " & _
"Desea ver una lista resumen primero?", _
vbQuestion + vbYesNo, gstrAppTitle) Then
' Show the summary
DoCmd.OpenForm "frmContacts1Summary", WhereCondition:=varWhere
' Make sure focus is on contact summary
Forms!frmContacts1Summary.SetFocus
Else
' Show the full contacts info filtered
DoCmd.OpenForm "frmContacts1", WhereCondition:=varWhere
' Make sure focus is on contacts
Forms!frmContacts1.SetFocus
End If
End If

' Done
DoCmd.Close acForm, Me.Name
' Clean up recordset
rst.Close
Set rst = Nothing

End Sub

This sample code works fine but I need to create in frmContacts a field
Residents and therefore I´d like to add this Search in fdlgSearch

Joseluis -

I suspect you have a leading AND in your WHERE clause. Right before your
'Set rst' statement, add a debug.print line, like this:

Debug.print varWhere

The results will be in the immediate window when you step through the code.
I suspect your varWhere is "AND (Residentes = True), but if there are no
other prior conditions, it should just be (Residentes = True), that is,
without the AND. Since not all the code is in your posting, it is hard to
tell.

If this doesn't resolve it, post the value of the varWhere by copy/pasting
it from the immediate window (from the debug.print) into your next posting.

There is a field in tblContacts named Residents.txt which is a yes/no field.
What I´m trying to create is a checkbox in my fdlgsearch [chkResidents] which

[quoted text clipped - 21 lines]

Whenever I tryed to execute the search an Error # 3061#is displayed and
the code is interrupted in this line.

 

[Daryl S]

Joseluis -

OK, you won't have a leading AND unless Me.cmbEmpleo is empty.

Add the debug.print varWhere as I mentioned before - right before the Set
rst statement. Step through it and post what is displayed on that step in
the immediate window. You may spot the issue from that yourself. Otherwise
post it and we can help spot the issue.

--
Daryl S

Thank you Daryl S for your time , the code os the fdlgSearch is as following:

Private Sub cmdSearch_Click()
Dim varWhere As Variant
Dim rst As DAO.Recordset

' Initialize to Null
varWhere = Null


' OK, start building the filter
' If specified a contact type value
If Not IsNothing(Me.cmbEmpleo) Then
' .. build the predicate
varWhere = "(Empleo = '" & Me.cmbEmpleo & "')"
End If

' Do Last Name next
If Not IsNothing(Me.txtLastName) Then
' .. build the predicate
' Note: taking advantage of Null propogation
' so we don't have to test for any previous predicate
varWhere = (varWhere + " AND ") & "([LastName] LIKE '" & Me.
txtLastName & "*')"
End If

' Do First Name next
If Not IsNothing(Me.txtFirstName) Then
' .. build the predicate
varWhere = (varWhere + " AND ") & "([FirstName] LIKE '" & Me.
txtFirstName & "*')"
End If

' Do Company next
If Not IsNothing(Me.cmbCompanyID) Then
' .. build the predicate
' Must use a subquery here because the value is in a linking table...
varWhere = (varWhere + " AND ") & _
"([ContactID] IN (SELECT ContactID FROM qryCompanyContacts " & _
"WHERE qryCompanyContacts.CompanyID = " & Me.cmbCompanyID & "))"
End If

' Do City next
If Not IsNothing(Me.txtCity) Then
' .. build the predicate
' Test for both Work and Home city
varWhere = (varWhere + " AND ") & "(([WorkCity] LIKE '" & Me.txtCity
& "*')" & _
" OR ([HomeCity] LIKE '" & Me.txtCity & "*'))"
End If

' Do State next
If Not IsNothing(Me.txtDNI) Then
' .. build the predicate
' Test for both Work and Home state
varWhere = (varWhere + " AND ") & "(([DNI] LIKE '" & Me.txtDNI & "*'))
"
End If
' Do Residentes next
If (Me.chkResidentes = True) Then
' Build a filter to exclude no resident contacts
varWhere = "(Residentes = True)"

End If

' Check to see that we built a filter
If IsNothing(varWhere) Then
MsgBox "Debe introducir al menos un criterio de busqueda.",
vbInformation, gstrAppTitle
Exit Sub
End If

' Open a recordset to see if any rows returned with this filter
Set rst = CurrentDb.OpenRecordset("SELECT tblContacts.* FROM tblContacts
WHERE " & varWhere)
' See if found none
If rst.RecordCount = 0 Then
MsgBox "Ninguna persona aparece con este criterio.", vbInformation,
gstrAppTitle
' Clean up recordset
rst.Close
Set rst = Nothing
Exit Sub
End If

' Hide me to fix later focus problems
Me.Visible = False
' Move to last to find out how many
rst.MoveLast
' If 5 or less or frmMembers already open,
If (rst.RecordCount < 6) Or IsFormLoaded("frmContacts1") Then
' Open Contacts filtered
' Note: if form already open, this just applies the filter
DoCmd.OpenForm "frmContacts1", WhereCondition:=varWhere
' Make sure focus is on contacts
Forms!frmContacts1.SetFocus
Else
' Ask if they want to see a summary list first
If vbYes = MsgBox("Su busqueda encontró " & rst.RecordCount & "
personas. " & _
"Desea ver una lista resumen primero?", _
vbQuestion + vbYesNo, gstrAppTitle) Then
' Show the summary
DoCmd.OpenForm "frmContacts1Summary", WhereCondition:=varWhere
' Make sure focus is on contact summary
Forms!frmContacts1Summary.SetFocus
Else
' Show the full contacts info filtered
DoCmd.OpenForm "frmContacts1", WhereCondition:=varWhere
' Make sure focus is on contacts
Forms!frmContacts1.SetFocus
End If
End If

' Done
DoCmd.Close acForm, Me.Name
' Clean up recordset
rst.Close
Set rst = Nothing

End Sub

This sample code works fine but I need to create in frmContacts a field
Residents and therefore I´d like to add this Search in fdlgSearch

Joseluis -

I suspect you have a leading AND in your WHERE clause. Right before your
'Set rst' statement, add a debug.print line, like this:

Debug.print varWhere

The results will be in the immediate window when you step through the code.
I suspect your varWhere is "AND (Residentes = True), but if there are no
other prior conditions, it should just be (Residentes = True), that is,
without the AND. Since not all the code is in your posting, it is hard to
tell.

If this doesn't resolve it, post the value of the varWhere by copy/pasting
it from the immediate window (from the debug.print) into your next posting.

There is a field in tblContacts named Residents.txt which is a yes/no field.
What I´m trying to create is a checkbox in my fdlgsearch [chkResidents] which

[quoted text clipped - 21 lines]

Whenever I tryed to execute the search an Error # 3061#is displayed and
the code is interrupted in this line.

--



.

 

[JOSELUIS via AccessMonster.com]

Sorry because I misunderstood you before. I add the debug.print and this is
what is displayed in the inmediate window:
(Residents = True)
I´ve tried to change chkResidents= -1 but I haven´t solved the problem. Any
suggestions?

Joseluis -

OK, you won't have a leading AND unless Me.cmbEmpleo is empty.

Add the debug.print varWhere as I mentioned before - right before the Set
rst statement. Step through it and post what is displayed on that step in
the immediate window. You may spot the issue from that yourself. Otherwise
post it and we can help spot the issue.

Thank you Daryl S for your time , the code os the fdlgSearch is as following:

[quoted text clipped - 141 lines]

 

[Daryl S]

Joseluis -

The True is the same as -1 (False is 0), so don't worry about that.

Try this:
Debug.Print ("SELECT tblContacts.* FROM tblContacts
WHERE " & varWhere)

Another thought on your syntax. As to the CurrentDB - I always used that as
a function to return the current database to a database variable. You may
want to include this:

dim db1 As Database
Set db1 = CurrentDB()

Then in your recordset statement, use db1 instead of CurrentDB.

Try those two things and let us know what you get.

--
Daryl S

Sorry because I misunderstood you before. I add the debug.print and this is
what is displayed in the inmediate window:
(Residents = True)
I´ve tried to change chkResidents= -1 but I haven´t solved the problem. Any
suggestions?

Joseluis -

OK, you won't have a leading AND unless Me.cmbEmpleo is empty.

Add the debug.print varWhere as I mentioned before - right before the Set
rst statement. Step through it and post what is displayed on that step in
the immediate window. You may spot the issue from that yourself. Otherwise
post it and we can help spot the issue.

Thank you Daryl S for your time , the code os the fdlgSearch is as following:

[quoted text clipped - 141 lines]

Whenever I tryed to execute the search an Error # 3061#is displayed and
the code is interrupted in this line.

--



.

 

[JOSELUIS via AccessMonster.com]

I tried these two things but the problem is still there however i take an
example provided by Allen Browne, I change fdlgSearch chkResidents into an
unbound cboFilterResidents. In the RecordSource property :-
1;"Residents";0;"Not Residents" and put this code:

If Me.cboFilterResidents = -1 Then
' .. build the predicate
varWhere = varWhere & "([Resident]= True ) "
ElseIf Me.cboFilterResidents = 0 Then
varWhere = varWhere & "([Resident]= False )"
End If
And it works perfectly.Thank you very much for your time.
Best regards
Jose Luis (Spain)

Joseluis -

The True is the same as -1 (False is 0), so don't worry about that.

Try this:
Debug.Print ("SELECT tblContacts.* FROM tblContacts
WHERE " & varWhere)

Another thought on your syntax. As to the CurrentDB - I always used that as
a function to return the current database to a database variable. You may
want to include this:

dim db1 As Database
Set db1 = CurrentDB()

Then in your recordset statement, use db1 instead of CurrentDB.

Try those two things and let us know what you get.

Sorry because I misunderstood you before. I add the debug.print and this is
what is displayed in the inmediate window:

[quoted text clipped - 15 lines]