A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Access » Using Forms
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Search form with a checkbox.



 
 
Thread Tools Display Modes
  #1  
Old April 27th, 2010, 10:37 PM posted to microsoft.public.access.forms
JOSELUIS via AccessMonster.com
external usenet poster
 
Posts: 26
Default Search form with a checkbox.

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.

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...forms/201004/1

  #2  
Old April 28th, 2010, 02:15 PM posted to microsoft.public.access.forms
Daryl S[_2_]
external usenet poster
 
Posts: 881
Default Search form with a checkbox.

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.

--
Daryl S


"JOSELUIS via AccessMonster.com" wrote:

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.

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...forms/201004/1

.

  #3  
Old April 28th, 2010, 06:33 PM posted to microsoft.public.access.forms
JOSELUIS via AccessMonster.com
external usenet poster
 
Posts: 26
Default Search form with a checkbox.

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
Daryl S wrote:
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.


--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...forms/201004/1

  #4  
Old April 28th, 2010, 08:05 PM posted to microsoft.public.access.forms
Daryl S[_2_]
external usenet poster
 
Posts: 881
Default Search form with a checkbox.

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


"JOSELUIS via AccessMonster.com" wrote:

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
Daryl S wrote:
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.


--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...forms/201004/1

.

  #5  
Old April 28th, 2010, 10:29 PM posted to microsoft.public.access.forms
JOSELUIS via AccessMonster.com
external usenet poster
 
Posts: 26
Default Search form with a checkbox.

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?
Daryl S wrote:
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.


--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...forms/201004/1

  #6  
Old April 29th, 2010, 03:00 PM posted to microsoft.public.access.forms
Daryl S[_2_]
external usenet poster
 
Posts: 881
Default Search form with a checkbox.

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


"JOSELUIS via AccessMonster.com" wrote:

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?
Daryl S wrote:
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.


--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...forms/201004/1

.

  #7  
Old April 30th, 2010, 10:26 PM posted to microsoft.public.access.forms
JOSELUIS via AccessMonster.com
external usenet poster
 
Posts: 26
Default Search form with a checkbox.

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)

Daryl S wrote:
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]
Whenever I tryed to execute the search an Error # 3061#is displayed and
the code is interrupted in this line.


--
Message posted via http://www.accessmonster.com

 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 09:00 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.