Recordset/query filtering

  • Thread starter Thread starter TM
  • Start date Start date
T

TM

I am trying to filter through some records and set a
listbox recordsource equal to this recordset. I need to
have records which are not equal to three different
parameters which can vary, so I am using variable which
point to the appropriat object on the appropriate form.
Hope this is making sense. Anyway, doing a simple select
query with "or" statements doesn't seem to work. So, I
am trying to build an initial recordset and then filter
out the next set of records and then the third. This is
what I have...

strCritC = "SELECT tblMain.SOPid, tblMain.[SOP Name],
tblMain.Category, tblMain.Area, tblMain.Index FROM
tblMain WHERE " & strCat & ";"
strCritA = "SELECT tblMain.SOPid, tblMain.[SOP Name],
tblMain.Category, tblMain.Area, tblMain.Index FROM
tblMain WHERE " & strArea & ";"
strCritI = "SELECT tblMain.SOPid, tblMain.[SOP Name],
tblMain.Category, tblMain.Area, tblMain.Index FROM
tblMain WHERE " & strIndex & " ORDER BY tblMain.[SOP
Name]"

Set rstCat = db.OpenRecordset(strCritC, dbOpenSnapshot)
Set rstCat = db.OpenRecordset(strCritA, dbOpenSnapshot)
Set rstCat = db.OpenRecordset(strCritI, dbOpenSnapshot)

Me![lblListSOPs].Caption = "Other"
Me![lstSOPs].RowSource = rstCat

I'm hoping this will work - for now I am getting a "too
few parameters expecting 1" error message (I believe no
3051). I have used the debug feature and checked the
select statement. I actually get the error on the
first "set rstCat" line. Any suggestions? I am open to
different ideas on how to do this AND ways to fix this
current error.

Thanks in advance!
 
No, you can't do it that way, but first I need to understand what it is
you're trying to do. What's in strCat, strArea, and strIndex? You should
be able to build an SQL string and assign that to the RowSource property.

--
John Viescas, author
"Microsoft Office Access 2003 Inside Out"
"Running Microsoft Access 2000"
"SQL Queries for Mere Mortals"
http://www.viescas.com/
(Microsoft Access MVP since 1993)
http://www.deanforamerica.com/johnviescas
 
I need to find all the "other" records. The user has the
option to view records matching in "Category", "Area",
and "Index" and then I want them to see the records that
do not match those items (the "leftovers"). But, it
doesn't work to do not Cat AND not Area AND not Index
because I don't want the records; then when I do the OR,
I get all the records. because one may not match one but
will match another - hope this makes sense! Also, the
user can access this form from 3 different forms, so the
source will not always be the same - that's why I need
the variable. I hope I have explained this correctly! I
also tried the UNION, but didn't have luck on that
either... I have to rethink this about every time!
-----Original Message-----
No, you can't do it that way, but first I need to understand what it is
you're trying to do. What's in strCat, strArea, and strIndex? You should
be able to build an SQL string and assign that to the RowSource property.

--
John Viescas, author
"Microsoft Office Access 2003 Inside Out"
"Running Microsoft Access 2000"
"SQL Queries for Mere Mortals"
http://www.viescas.com/
(Microsoft Access MVP since 1993)
http://www.deanforamerica.com/johnviescas
I am trying to filter through some records and set a
listbox recordsource equal to this recordset. I need to
have records which are not equal to three different
parameters which can vary, so I am using variable which
point to the appropriat object on the appropriate form.
Hope this is making sense. Anyway, doing a simple select
query with "or" statements doesn't seem to work. So, I
am trying to build an initial recordset and then filter
out the next set of records and then the third. This is
what I have...

strCritC = "SELECT tblMain.SOPid, tblMain.[SOP Name],
tblMain.Category, tblMain.Area, tblMain.Index FROM
tblMain WHERE " & strCat & ";"
strCritA = "SELECT tblMain.SOPid, tblMain.[SOP Name],
tblMain.Category, tblMain.Area, tblMain.Index FROM
tblMain WHERE " & strArea & ";"
strCritI = "SELECT tblMain.SOPid, tblMain.[SOP Name],
tblMain.Category, tblMain.Area, tblMain.Index FROM
tblMain WHERE " & strIndex & " ORDER BY tblMain.[SOP
Name]"

Set rstCat = db.OpenRecordset(strCritC, dbOpenSnapshot)
Set rstCat = db.OpenRecordset(strCritA, dbOpenSnapshot)
Set rstCat = db.OpenRecordset(strCritI, dbOpenSnapshot)

Me![lblListSOPs].Caption = "Other"
Me![lstSOPs].RowSource = rstCat

I'm hoping this will work - for now I am getting a "too
few parameters expecting 1" error message (I believe no
3051). I have used the debug feature and checked the
select statement. I actually get the error on the
first "set rstCat" line. Any suggestions? I am open to
different ideas on how to do this AND ways to fix this
current error.

Thanks in advance!
Click to expand...


.
Click to expand...
 
Well, I SEEM to have come to a solution. I created
an "unmatched" query with a wizard and then used that sql
statement in VBA. So far it seems to work - I am still
open to hearing other possibilities though! Thanks for
the help!!!
-----Original Message-----
No, you can't do it that way, but first I need to understand what it is
you're trying to do. What's in strCat, strArea, and strIndex? You should
be able to build an SQL string and assign that to the RowSource property.

--
John Viescas, author
"Microsoft Office Access 2003 Inside Out"
"Running Microsoft Access 2000"
"SQL Queries for Mere Mortals"
http://www.viescas.com/
(Microsoft Access MVP since 1993)
http://www.deanforamerica.com/johnviescas
I am trying to filter through some records and set a
listbox recordsource equal to this recordset. I need to
have records which are not equal to three different
parameters which can vary, so I am using variable which
point to the appropriat object on the appropriate form.
Hope this is making sense. Anyway, doing a simple select
query with "or" statements doesn't seem to work. So, I
am trying to build an initial recordset and then filter
out the next set of records and then the third. This is
what I have...

strCritC = "SELECT tblMain.SOPid, tblMain.[SOP Name],
tblMain.Category, tblMain.Area, tblMain.Index FROM
tblMain WHERE " & strCat & ";"
strCritA = "SELECT tblMain.SOPid, tblMain.[SOP Name],
tblMain.Category, tblMain.Area, tblMain.Index FROM
tblMain WHERE " & strArea & ";"
strCritI = "SELECT tblMain.SOPid, tblMain.[SOP Name],
tblMain.Category, tblMain.Area, tblMain.Index FROM
tblMain WHERE " & strIndex & " ORDER BY tblMain.[SOP
Name]"

Set rstCat = db.OpenRecordset(strCritC, dbOpenSnapshot)
Set rstCat = db.OpenRecordset(strCritA, dbOpenSnapshot)
Set rstCat = db.OpenRecordset(strCritI, dbOpenSnapshot)

Me![lblListSOPs].Caption = "Other"
Me![lstSOPs].RowSource = rstCat

I'm hoping this will work - for now I am getting a "too
few parameters expecting 1" error message (I believe no
3051). I have used the debug feature and checked the
select statement. I actually get the error on the
first "set rstCat" line. Any suggestions? I am open to
different ideas on how to do this AND ways to fix this
current error.

Thanks in advance!
Click to expand...


.
Click to expand...
 
It sounds like you solved it! As I noted earlier, you cannot assign a
Recordset object to a combo or list box Row Source. If you came up with the
SQL that solves the problem, then assigning that (the SQL text) to the Row
Source is the way to go.

--
John Viescas, author
"Microsoft Office Access 2003 Inside Out"
"Running Microsoft Access 2000"
"SQL Queries for Mere Mortals"
http://www.viescas.com/
(Microsoft Access MVP since 1993)

TM said:
Well, I SEEM to have come to a solution. I created
an "unmatched" query with a wizard and then used that sql
statement in VBA. So far it seems to work - I am still
open to hearing other possibilities though! Thanks for
the help!!!
-----Original Message-----
No, you can't do it that way, but first I need to understand what it is
you're trying to do. What's in strCat, strArea, and strIndex? You should
be able to build an SQL string and assign that to the RowSource property.

--
John Viescas, author
"Microsoft Office Access 2003 Inside Out"
"Running Microsoft Access 2000"
"SQL Queries for Mere Mortals"
http://www.viescas.com/
(Microsoft Access MVP since 1993)
http://www.deanforamerica.com/johnviescas
I am trying to filter through some records and set a
listbox recordsource equal to this recordset. I need to
have records which are not equal to three different
parameters which can vary, so I am using variable which
point to the appropriat object on the appropriate form.
Hope this is making sense. Anyway, doing a simple select
query with "or" statements doesn't seem to work. So, I
am trying to build an initial recordset and then filter
out the next set of records and then the third. This is
what I have...

strCritC = "SELECT tblMain.SOPid, tblMain.[SOP Name],
tblMain.Category, tblMain.Area, tblMain.Index FROM
tblMain WHERE " & strCat & ";"
strCritA = "SELECT tblMain.SOPid, tblMain.[SOP Name],
tblMain.Category, tblMain.Area, tblMain.Index FROM
tblMain WHERE " & strArea & ";"
strCritI = "SELECT tblMain.SOPid, tblMain.[SOP Name],
tblMain.Category, tblMain.Area, tblMain.Index FROM
tblMain WHERE " & strIndex & " ORDER BY tblMain.[SOP
Name]"

Set rstCat = db.OpenRecordset(strCritC, dbOpenSnapshot)
Set rstCat = db.OpenRecordset(strCritA, dbOpenSnapshot)
Set rstCat = db.OpenRecordset(strCritI, dbOpenSnapshot)

Me![lblListSOPs].Caption = "Other"
Me![lstSOPs].RowSource = rstCat

I'm hoping this will work - for now I am getting a "too
few parameters expecting 1" error message (I believe no
3051). I have used the debug feature and checked the
select statement. I actually get the error on the
first "set rstCat" line. Any suggestions? I am open to
different ideas on how to do this AND ways to fix this
current error.

Thanks in advance!
Click to expand...


.
Click to expand...
Click to expand...
 
Back
Top