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. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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 | |
|
|