Search form with a checkbox.
in [Form]
|
Prev: Make invisible with a checkbox
Next: Combo box problem
From: JOSELUIS via AccessMonster.com on 27 Apr 2010 17:37 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/Forums.aspx/access-forms/201004/1
From: Daryl S on 28 Apr 2010 09:15 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/Forums.aspx/access-forms/201004/1 > > . >
From: JOSELUIS via AccessMonster.com on 28 Apr 2010 13:33 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/Forums.aspx/access-forms/201004/1
From: Daryl S on 28 Apr 2010 15:05 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/Forums.aspx/access-forms/201004/1 > > . >
From: JOSELUIS via AccessMonster.com on 28 Apr 2010 17:29
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/Forums.aspx/access-forms/201004/1 |