recordsetclone filters

  • Thread starter Simeon Cheeseman
  • Start date
S

Simeon Cheeseman

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
 
J

J. Goddard

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
Click to expand...

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 said:
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
Click to expand...
 
S

Simeon Cheeseman

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 said:
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
Click to expand...

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 said:
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
Click to expand...
Click to expand...
 
J

J. Goddard

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 said:
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

:

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
Click to expand...

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 said:
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
Click to expand...
Click to expand...
Click to expand...
 
S

Simeon Cheeseman

thanks for that I will try to remember to use that,

J. Goddard said:
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 said:
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

:

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
Click to expand...
Click to expand...
Click to expand...
 
S

Simeon Cheeseman

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 said:
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 said:
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

:

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
Click to expand...
Click to expand...
Click to expand...
 
Top