Defining Parameters

[mike]

Hi. I have a form that is executing some send mail events
but I'm hung up a recorset problem. I have a table tblMain
that has one occurence of all of our salespeople and their
corresponding territory. I have a query
tblShippedTerritoryTotalToPlanWH that has a bunch of
products and how much has shipped. I'm getting an error in
rs1 and rs2 on the [Territory]=rs![Territory] part of the
expression. I guess it's having trouble reading rs!
[Territory]. The error tells me I have Too Few Parameters,
Expected 1. I guess I have to define. rs!Territory but I'm
not sure how. Any suggestions would be great. Thanks!

Set db = CurrentDb

Set rs = db.OpenRecordset("SELECT * " & _
"FROM [tblMain]" & _
"WHERE [Job]='TM' And [LeftCompany]=0")

Set rs2 = db.OpenRecordset("SELECT * " & _
"FROM [tblShippedTerritoryTotalToPlanWH] " & _
"WHERE [Territory]=rs![Territory] and [Product]='PFR'")

Set rs3 = db.OpenRecordset("SELECT * " & _
"FROM [tblShippedTerritoryTotalToPlanWH] " & _
"WHERE [Territory]=rs![Territory] and [Product]='URO'")

 

[John Spencer (MVP)]

If Rs!Territory is a string then you must extract the value of it and surround
it with apostrophes.

Set rs2 = db.OpenRecordset("SELECT * " & _
"FROM [tblShippedTerritoryTotalToPlanWH] " & _
"WHERE [Territory]='" & rs![Territory] & "' and [Product]='PFR'")

 

[mike]

Awesome! Thanks!

-----Original Message-----
If Rs!Territory is a string then you must extract the value of it and surround
it with apostrophes.

Set rs2 = db.OpenRecordset("SELECT * " & _
"FROM [tblShippedTerritoryTotalToPlanWH] " & _
"WHERE [Territory]='" & rs![Territory] & "' and [Product] ='PFR'")

Hi. I have a form that is executing some send mail events
but I'm hung up a recorset problem. I have a table tblMain
that has one occurence of all of our salespeople and their
corresponding territory. I have a query
tblShippedTerritoryTotalToPlanWH that has a bunch of
products and how much has shipped. I'm getting an error in
rs1 and rs2 on the [Territory]=rs![Territory] part of the
expression. I guess it's having trouble reading rs!
[Territory]. The error tells me I have Too Few Parameters,
Expected 1. I guess I have to define. rs!Territory but I'm
not sure how. Any suggestions would be great. Thanks!

Set db = CurrentDb

Set rs = db.OpenRecordset("SELECT * " & _
"FROM [tblMain]" & _
"WHERE [Job]='TM' And [LeftCompany]=0")

Set rs2 = db.OpenRecordset("SELECT * " & _
"FROM [tblShippedTerritoryTotalToPlanWH] " & _
"WHERE [Territory]=rs![Territory] and [Product]='PFR'")

Set rs3 = db.OpenRecordset("SELECT * " & _
"FROM [tblShippedTerritoryTotalToPlanWH] " & _
"WHERE [Territory]=rs![Territory] and [Product]='URO'")

.