recordsetclone filters Simeon Cheeseman 09.09.2006 03:45:02 hi there i have the following code to filter my query, and none of the filters seem to be working. as the recorset initially gives one or two records back and then after filtering it never changes. Dim date_id1 As Integer Dim date_id2 As Integer Dim date_id3 As Integer Dim childm As Integer Dim child1 As Integer Dim child2 As Integer Dim child3 As Integer Dim qry1 As String Dim qry1filter1 As String Dim qry1filter2 As String Dim qry1filter3 As String Dim qry2filter1 As String Dim qry2filter2 As String Dim qry2filter3 As String Dim where1 As String Dim where2 As String Dim where3 As String where1 = "((child_id) = " & child1 & ")" where2 = "((child_id) = " & child2 & ")" where3 = "((child_id) = " & child3 & ")" childm = id_str child1 = date_id1 child2 = date_id2 child3 = date_id3 qry1filter1 = "((([child_1]) = " & child1 & ") OR (([child_2]) = " & child1 & "))" qry1filter2 = "((([child_1]) = " & child2 & ") OR (([child_2]) = " & child2 & "))" qry1filter3 = "((([child_1]) = " & child3 & ") OR (([child_2]) = " & child3 & "))" qry2filter1 = "(([child_id_1]) = " & child1 & ") OR (([child_id_2]) = " & child1 & ")" qry2filter2 = "(([child_id_1]) = " & child2 & ") OR (([child_id_2]) = " & child2 & ")" qry2filter3 = "(([child_id_1]) = " & child3 & ") OR (([child_id_2]) = " & child3 & ")" qry1 = "SELECT child_1, child_2 " & _ "FROM tbl_risks " & _ "WHERE (((child_1) = " & childm & ")) OR (((child_2) = " & childm & "));" Me.RecordSource = qry1 Me.Requery Set rst = Me.RecordsetClone If child1 <> 0 Then rst.Filter = qry1filter1 If rst.EOF = True Then rst.Filter = adFilterNone Else: DoCmd.OpenForm errorform, , , where1 End If End If Set rst = Me.RecordsetClone If child2 <> 0 Then rst.Filter = qry1filter2 If rst.EOF = True Then rst.Filter = adFilterNone Else: DoCmd.OpenForm errorform, , , where2 End If End If Set rst = Me.RecordsetClone If child3 <> 0 Then rst.Filter = qry1filter3 If rst.EOF = True Then rst.Filter = adFilterNone Else: DoCmd.OpenForm errorform, , , where3 End If End If rst.Filter = adFilterNone any help would be apprecited

Re: recordsetclone filters "J. Goddard" <jrgoddard[ at ]NO_cyberus_SPAM.ca> 09.09.2006 04:32:54 In this bit of the code: > where1 = "((child_id) = " & child1 & ")" > where2 = "((child_id) = " & child2 & ")" > where3 = "((child_id) = " & child3 & ")" > childm = id_str > child1 = date_id1 > child2 = date_id2 > child3 = date_id3 when you set where1 etc, child1 etc have no values (at least none that you set - they are 0) put the child assignments above the where assignments as see if that works. Other questions: What is id_str, and therefore what is the value of childm? where do date_id1 etc. get their values? Do you code with Option Explicit in all your modules? I highly recommend using it, as it catches a lot of mistakes. Simeon Cheeseman wrote: [Quoted Text] > hi there i have the following code to filter my query, and none of the > filters seem to be working. as the recorset initially gives one or two > records back and then after filtering it never changes. > > Dim date_id1 As Integer > Dim date_id2 As Integer > Dim date_id3 As Integer > Dim childm As Integer > Dim child1 As Integer > Dim child2 As Integer > Dim child3 As Integer > Dim qry1 As String > Dim qry1filter1 As String > Dim qry1filter2 As String > Dim qry1filter3 As String > Dim qry2filter1 As String > Dim qry2filter2 As String > Dim qry2filter3 As String > Dim where1 As String > Dim where2 As String > Dim where3 As String > > where1 = "((child_id) = " & child1 & ")" > where2 = "((child_id) = " & child2 & ")" > where3 = "((child_id) = " & child3 & ")" > childm = id_str > child1 = date_id1 > child2 = date_id2 > child3 = date_id3 > qry1filter1 = "((([child_1]) = " & child1 & ") OR (([child_2]) = " & > child1 & "))" > qry1filter2 = "((([child_1]) = " & child2 & ") OR (([child_2]) = " & > child2 & "))" > qry1filter3 = "((([child_1]) = " & child3 & ") OR (([child_2]) = " & > child3 & "))" > qry2filter1 = "(([child_id_1]) = " & child1 & ") OR (([child_id_2]) = " > & child1 & ")" > qry2filter2 = "(([child_id_1]) = " & child2 & ") OR (([child_id_2]) = " > & child2 & ")" > qry2filter3 = "(([child_id_1]) = " & child3 & ") OR (([child_id_2]) = " > & child3 & ")" > > qry1 = "SELECT child_1, child_2 " & _ > "FROM tbl_risks " & _ > "WHERE (((child_1) = " & childm & ")) OR (((child_2) = " & > childm & "));" > > Me.RecordSource = qry1 > Me.Requery > > Set rst = Me.RecordsetClone > If child1 <> 0 Then > rst.Filter = qry1filter1 > If rst.EOF = True Then > rst.Filter = adFilterNone > Else: DoCmd.OpenForm errorform, , , where1 > End If > End If > Set rst = Me.RecordsetClone > If child2 <> 0 Then > rst.Filter = qry1filter2 > If rst.EOF = True Then > rst.Filter = adFilterNone > Else: DoCmd.OpenForm errorform, , , where2 > End If > End If > Set rst = Me.RecordsetClone > If child3 <> 0 Then > rst.Filter = qry1filter3 > If rst.EOF = True Then > rst.Filter = adFilterNone > Else: DoCmd.OpenForm errorform, , , where3 > End If > End If > rst.Filter = adFilterNone > > any help would be apprecited

Re: recordsetclone filters Simeon Cheeseman 09.09.2006 10:14:02 thankyou for your reply, i fixed my problem by creating a sql statement then adding the where code on the end. i apologise that all my data was out of order. and what does Option Explicit do as i have come across it but never understood or used it. thanks simeon "J. Goddard" wrote: [Quoted Text] > In this bit of the code: > > > where1 = "((child_id) = " & child1 & ")" > > where2 = "((child_id) = " & child2 & ")" > > where3 = "((child_id) = " & child3 & ")" > > childm = id_str > > child1 = date_id1 > > child2 = date_id2 > > child3 = date_id3 > > when you set where1 etc, child1 etc have no values (at least none that > you set - they are 0) > > put the child assignments above the where assignments as see if that works. > > Other questions: > > What is id_str, and therefore what is the value of childm? > > where do date_id1 etc. get their values? > > Do you code with Option Explicit in all your modules? I highly > recommend using it, as it catches a lot of mistakes. > > > Simeon Cheeseman wrote: > > hi there i have the following code to filter my query, and none of the > > filters seem to be working. as the recorset initially gives one or two > > records back and then after filtering it never changes. > > > > Dim date_id1 As Integer > > Dim date_id2 As Integer > > Dim date_id3 As Integer > > Dim childm As Integer > > Dim child1 As Integer > > Dim child2 As Integer > > Dim child3 As Integer > > Dim qry1 As String > > Dim qry1filter1 As String > > Dim qry1filter2 As String > > Dim qry1filter3 As String > > Dim qry2filter1 As String > > Dim qry2filter2 As String > > Dim qry2filter3 As String > > Dim where1 As String > > Dim where2 As String > > Dim where3 As String > > > > where1 = "((child_id) = " & child1 & ")" > > where2 = "((child_id) = " & child2 & ")" > > where3 = "((child_id) = " & child3 & ")" > > childm = id_str > > child1 = date_id1 > > child2 = date_id2 > > child3 = date_id3 > > qry1filter1 = "((([child_1]) = " & child1 & ") OR (([child_2]) = " & > > child1 & "))" > > qry1filter2 = "((([child_1]) = " & child2 & ") OR (([child_2]) = " & > > child2 & "))" > > qry1filter3 = "((([child_1]) = " & child3 & ") OR (([child_2]) = " & > > child3 & "))" > > qry2filter1 = "(([child_id_1]) = " & child1 & ") OR (([child_id_2]) = " > > & child1 & ")" > > qry2filter2 = "(([child_id_1]) = " & child2 & ") OR (([child_id_2]) = " > > & child2 & ")" > > qry2filter3 = "(([child_id_1]) = " & child3 & ") OR (([child_id_2]) = " > > & child3 & ")" > > > > qry1 = "SELECT child_1, child_2 " & _ > > "FROM tbl_risks " & _ > > "WHERE (((child_1) = " & childm & ")) OR (((child_2) = " & > > childm & "));" > > > > Me.RecordSource = qry1 > > Me.Requery > > > > Set rst = Me.RecordsetClone > > If child1 <> 0 Then > > rst.Filter = qry1filter1 > > If rst.EOF = True Then > > rst.Filter = adFilterNone > > Else: DoCmd.OpenForm errorform, , , where1 > > End If > > End If > > Set rst = Me.RecordsetClone > > If child2 <> 0 Then > > rst.Filter = qry1filter2 > > If rst.EOF = True Then > > rst.Filter = adFilterNone > > Else: DoCmd.OpenForm errorform, , , where2 > > End If > > End If > > Set rst = Me.RecordsetClone > > If child3 <> 0 Then > > rst.Filter = qry1filter3 > > If rst.EOF = True Then > > rst.Filter = adFilterNone > > Else: DoCmd.OpenForm errorform, , , where3 > > End If > > End If > > rst.Filter = adFilterNone > > > > any help would be apprecited > >

Re: recordsetclone filters "J. Goddard" <jrgoddard[ at ]NO_cyberus_SPAM.ca> 09.09.2006 13:25:39 Hi - Option explicit forces you to explicitly declare all your variables by using Dim statements. You'd be amazed how often that will trap spelling mistakes and omissions. The compiler detects undeclared variables. One trick I use is to make my variables a combination of upper and lower case in the Dim statement as in " Dim TableName as string " Then, when I am typing the code, I just type "tablename", and Access automatically changes it to "TableName" after the statement is complete. It really helps. John Simeon Cheeseman wrote: [Quoted Text] > thankyou for your reply, i fixed my problem by creating a sql statement then > adding the where code on the end. > > i apologise that all my data was out of order. > > and what does Option Explicit do as i have come across it but never > understood or used it. > > thanks simeon > > "J. Goddard" wrote: > > >>In this bit of the code: >> >> > where1 = "((child_id) = " & child1 & ")" >> > where2 = "((child_id) = " & child2 & ")" >> > where3 = "((child_id) = " & child3 & ")" >> > childm = id_str >> > child1 = date_id1 >> > child2 = date_id2 >> > child3 = date_id3 >> >>when you set where1 etc, child1 etc have no values (at least none that >>you set - they are 0) >> >>put the child assignments above the where assignments as see if that works. >> >>Other questions: >> >>What is id_str, and therefore what is the value of childm? >> >>where do date_id1 etc. get their values? >> >>Do you code with Option Explicit in all your modules? I highly >>recommend using it, as it catches a lot of mistakes. >> >> >>Simeon Cheeseman wrote: >> >>>hi there i have the following code to filter my query, and none of the >>>filters seem to be working. as the recorset initially gives one or two >>>records back and then after filtering it never changes. >>> >>> Dim date_id1 As Integer >>> Dim date_id2 As Integer >>> Dim date_id3 As Integer >>> Dim childm As Integer >>> Dim child1 As Integer >>> Dim child2 As Integer >>> Dim child3 As Integer >>> Dim qry1 As String >>> Dim qry1filter1 As String >>> Dim qry1filter2 As String >>> Dim qry1filter3 As String >>> Dim qry2filter1 As String >>> Dim qry2filter2 As String >>> Dim qry2filter3 As String >>> Dim where1 As String >>> Dim where2 As String >>> Dim where3 As String >>> >>> where1 = "((child_id) = " & child1 & ")" >>> where2 = "((child_id) = " & child2 & ")" >>> where3 = "((child_id) = " & child3 & ")" >>> childm = id_str >>> child1 = date_id1 >>> child2 = date_id2 >>> child3 = date_id3 >>> qry1filter1 = "((([child_1]) = " & child1 & ") OR (([child_2]) = " & >>>child1 & "))" >>> qry1filter2 = "((([child_1]) = " & child2 & ") OR (([child_2]) = " & >>>child2 & "))" >>> qry1filter3 = "((([child_1]) = " & child3 & ") OR (([child_2]) = " & >>>child3 & "))" >>> qry2filter1 = "(([child_id_1]) = " & child1 & ") OR (([child_id_2]) = " >>>& child1 & ")" >>> qry2filter2 = "(([child_id_1]) = " & child2 & ") OR (([child_id_2]) = " >>>& child2 & ")" >>> qry2filter3 = "(([child_id_1]) = " & child3 & ") OR (([child_id_2]) = " >>>& child3 & ")" >>> >>> qry1 = "SELECT child_1, child_2 " & _ >>> "FROM tbl_risks " & _ >>> "WHERE (((child_1) = " & childm & ")) OR (((child_2) = " & >>>childm & "));" >>> >>> Me.RecordSource = qry1 >>> Me.Requery >>> >>> Set rst = Me.RecordsetClone >>> If child1 <> 0 Then >>> rst.Filter = qry1filter1 >>> If rst.EOF = True Then >>> rst.Filter = adFilterNone >>> Else: DoCmd.OpenForm errorform, , , where1 >>> End If >>> End If >>> Set rst = Me.RecordsetClone >>> If child2 <> 0 Then >>> rst.Filter = qry1filter2 >>> If rst.EOF = True Then >>> rst.Filter = adFilterNone >>> Else: DoCmd.OpenForm errorform, , , where2 >>> End If >>> End If >>> Set rst = Me.RecordsetClone >>> If child3 <> 0 Then >>> rst.Filter = qry1filter3 >>> If rst.EOF = True Then >>> rst.Filter = adFilterNone >>> Else: DoCmd.OpenForm errorform, , , where3 >>> End If >>> End If >>> rst.Filter = adFilterNone >>> >>>any help would be apprecited >> >>

Re: recordsetclone filters Simeon Cheeseman 11.09.2006 09:01:02 thanks for that I will try to remember to use that, "J. Goddard" wrote: [Quoted Text] > Hi - > > Option explicit forces you to explicitly declare all your variables by > using Dim statements. You'd be amazed how often that will trap spelling > mistakes and omissions. The compiler detects undeclared variables. > > One trick I use is to make my variables a combination of upper and lower > case in the Dim statement as in " Dim TableName as string " > > Then, when I am typing the code, I just type "tablename", and Access > automatically changes it to "TableName" after the statement is complete. > It really helps. > > John > > > Simeon Cheeseman wrote: > > > thankyou for your reply, i fixed my problem by creating a sql statement then > > adding the where code on the end. > > > > i apologise that all my data was out of order. > > > > and what does Option Explicit do as i have come across it but never > > understood or used it. > > > > thanks simeon > > > > "J. Goddard" wrote: > > > > > >>In this bit of the code: > >> > >> > where1 = "((child_id) = " & child1 & ")" > >> > where2 = "((child_id) = " & child2 & ")" > >> > where3 = "((child_id) = " & child3 & ")" > >> > childm = id_str > >> > child1 = date_id1 > >> > child2 = date_id2 > >> > child3 = date_id3 > >> > >>when you set where1 etc, child1 etc have no values (at least none that > >>you set - they are 0) > >> > >>put the child assignments above the where assignments as see if that works. > >> > >>Other questions: > >> > >>What is id_str, and therefore what is the value of childm? > >> > >>where do date_id1 etc. get their values? > >> > >>Do you code with Option Explicit in all your modules? I highly > >>recommend using it, as it catches a lot of mistakes. > >> > >> > >>Simeon Cheeseman wrote: > >> > >>>hi there i have the following code to filter my query, and none of the > >>>filters seem to be working. as the recorset initially gives one or two > >>>records back and then after filtering it never changes. > >>> > >>> Dim date_id1 As Integer > >>> Dim date_id2 As Integer > >>> Dim date_id3 As Integer > >>> Dim childm As Integer > >>> Dim child1 As Integer > >>> Dim child2 As Integer > >>> Dim child3 As Integer > >>> Dim qry1 As String > >>> Dim qry1filter1 As String > >>> Dim qry1filter2 As String > >>> Dim qry1filter3 As String > >>> Dim qry2filter1 As String > >>> Dim qry2filter2 As String > >>> Dim qry2filter3 As String > >>> Dim where1 As String > >>> Dim where2 As String > >>> Dim where3 As String > >>> > >>> where1 = "((child_id) = " & child1 & ")" > >>> where2 = "((child_id) = " & child2 & ")" > >>> where3 = "((child_id) = " & child3 & ")" > >>> childm = id_str > >>> child1 = date_id1 > >>> child2 = date_id2 > >>> child3 = date_id3 > >>> qry1filter1 = "((([child_1]) = " & child1 & ") OR (([child_2]) = " & > >>>child1 & "))" > >>> qry1filter2 = "((([child_1]) = " & child2 & ") OR (([child_2]) = " & > >>>child2 & "))" > >>> qry1filter3 = "((([child_1]) = " & child3 & ") OR (([child_2]) = " & > >>>child3 & "))" > >>> qry2filter1 = "(([child_id_1]) = " & child1 & ") OR (([child_id_2]) = " > >>>& child1 & ")" > >>> qry2filter2 = "(([child_id_1]) = " & child2 & ") OR (([child_id_2]) = " > >>>& child2 & ")" > >>> qry2filter3 = "(([child_id_1]) = " & child3 & ") OR (([child_id_2]) = " > >>>& child3 & ")" > >>> > >>> qry1 = "SELECT child_1, child_2 " & _ > >>> "FROM tbl_risks " & _ > >>> "WHERE (((child_1) = " & childm & ")) OR (((child_2) = " & > >>>childm & "));" > >>> > >>> Me.RecordSource = qry1 > >>> Me.Requery > >>> > >>> Set rst = Me.RecordsetClone > >>> If child1 <> 0 Then > >>> rst.Filter = qry1filter1 > >>> If rst.EOF = True Then > >>> rst.Filter = adFilterNone > >>> Else: DoCmd.OpenForm errorform, , , where1 > >>> End If > >>> End If > >>> Set rst = Me.RecordsetClone > >>> If child2 <> 0 Then > >>> rst.Filter = qry1filter2 > >>> If rst.EOF = True Then > >>> rst.Filter = adFilterNone > >>> Else: DoCmd.OpenForm errorform, , , where2 > >>> End If > >>> End If > >>> Set rst = Me.RecordsetClone > >>> If child3 <> 0 Then > >>> rst.Filter = qry1filter3 > >>> If rst.EOF = True Then > >>> rst.Filter = adFilterNone > >>> Else: DoCmd.OpenForm errorform, , , where3 > >>> End If > >>> End If > >>> rst.Filter = adFilterNone > >>> > >>>any help would be apprecited > >> > >> > >

Re: recordsetclone filters Simeon Cheeseman 11.09.2006 09:09:01 thanks john for that, i will now use that as spelling errors seem to make up a lot of my errors in my database. thanks Simeon. "J. Goddard" wrote: [Quoted Text] > Hi - > > Option explicit forces you to explicitly declare all your variables by > using Dim statements. You'd be amazed how often that will trap spelling > mistakes and omissions. The compiler detects undeclared variables. > > One trick I use is to make my variables a combination of upper and lower > case in the Dim statement as in " Dim TableName as string " > > Then, when I am typing the code, I just type "tablename", and Access > automatically changes it to "TableName" after the statement is complete. > It really helps. > > John > > > Simeon Cheeseman wrote: > > > thankyou for your reply, i fixed my problem by creating a sql statement then > > adding the where code on the end. > > > > i apologise that all my data was out of order. > > > > and what does Option Explicit do as i have come across it but never > > understood or used it. > > > > thanks simeon > > > > "J. Goddard" wrote: > > > > > >>In this bit of the code: > >> > >> > where1 = "((child_id) = " & child1 & ")" > >> > where2 = "((child_id) = " & child2 & ")" > >> > where3 = "((child_id) = " & child3 & ")" > >> > childm = id_str > >> > child1 = date_id1 > >> > child2 = date_id2 > >> > child3 = date_id3 > >> > >>when you set where1 etc, child1 etc have no values (at least none that > >>you set - they are 0) > >> > >>put the child assignments above the where assignments as see if that works. > >> > >>Other questions: > >> > >>What is id_str, and therefore what is the value of childm? > >> > >>where do date_id1 etc. get their values? > >> > >>Do you code with Option Explicit in all your modules? I highly > >>recommend using it, as it catches a lot of mistakes. > >> > >> > >>Simeon Cheeseman wrote: > >> > >>>hi there i have the following code to filter my query, and none of the > >>>filters seem to be working. as the recorset initially gives one or two > >>>records back and then after filtering it never changes. > >>> > >>> Dim date_id1 As Integer > >>> Dim date_id2 As Integer > >>> Dim date_id3 As Integer > >>> Dim childm As Integer > >>> Dim child1 As Integer > >>> Dim child2 As Integer > >>> Dim child3 As Integer > >>> Dim qry1 As String > >>> Dim qry1filter1 As String > >>> Dim qry1filter2 As String > >>> Dim qry1filter3 As String > >>> Dim qry2filter1 As String > >>> Dim qry2filter2 As String > >>> Dim qry2filter3 As String > >>> Dim where1 As String > >>> Dim where2 As String > >>> Dim where3 As String > >>> > >>> where1 = "((child_id) = " & child1 & ")" > >>> where2 = "((child_id) = " & child2 & ")" > >>> where3 = "((child_id) = " & child3 & ")" > >>> childm = id_str > >>> child1 = date_id1 > >>> child2 = date_id2 > >>> child3 = date_id3 > >>> qry1filter1 = "((([child_1]) = " & child1 & ") OR (([child_2]) = " & > >>>child1 & "))" > >>> qry1filter2 = "((([child_1]) = " & child2 & ") OR (([child_2]) = " & > >>>child2 & "))" > >>> qry1filter3 = "((([child_1]) = " & child3 & ") OR (([child_2]) = " & > >>>child3 & "))" > >>> qry2filter1 = "(([child_id_1]) = " & child1 & ") OR (([child_id_2]) = " > >>>& child1 & ")" > >>> qry2filter2 = "(([child_id_1]) = " & child2 & ") OR (([child_id_2]) = " > >>>& child2 & ")" > >>> qry2filter3 = "(([child_id_1]) = " & child3 & ") OR (([child_id_2]) = " > >>>& child3 & ")" > >>> > >>> qry1 = "SELECT child_1, child_2 " & _ > >>> "FROM tbl_risks " & _ > >>> "WHERE (((child_1) = " & childm & ")) OR (((child_2) = " & > >>>childm & "));" > >>> > >>> Me.RecordSource = qry1 > >>> Me.Requery > >>> > >>> Set rst = Me.RecordsetClone > >>> If child1 <> 0 Then > >>> rst.Filter = qry1filter1 > >>> If rst.EOF = True Then > >>> rst.Filter = adFilterNone > >>> Else: DoCmd.OpenForm errorform, , , where1 > >>> End If > >>> End If > >>> Set rst = Me.RecordsetClone > >>> If child2 <> 0 Then > >>> rst.Filter = qry1filter2 > >>> If rst.EOF = True Then > >>> rst.Filter = adFilterNone > >>> Else: DoCmd.OpenForm errorform, , , where2 > >>> End If > >>> End If > >>> Set rst = Me.RecordsetClone > >>> If child3 <> 0 Then > >>> rst.Filter = qry1filter3 > >>> If rst.EOF = True Then > >>> rst.Filter = adFilterNone > >>> Else: DoCmd.OpenForm errorform, , , where3 > >>> End If > >>> End If > >>> rst.Filter = adFilterNone > >>> > >>>any help would be apprecited > >> > >> > >