where in recordset

[samotek via AccessMonster.com]

I have the following recordset:

Dim rs As DAO.Recordset
Set rs = CurrentDb.OpenRecordset("SELECT Productid, code, categoryid,
supplierid, size, ddu, grossprice,

office,cons,vip,dealer,pref ,bosch,drummer,oem,rig FROM products")

' fix office prices
rs.MoveFirst
Do
.............
I need to fix up the prices only in those cases where supplierid = 2.how can
i change the recordset?

 

[Alex Dybenko]

Hi,
open recordset filtered:
Set rs = CurrentDb.OpenRecordset("SELECT Productid, code, categoryid,
supplierid, size, ddu, grossprice, office,cons,vip,dealer,pref
,bosch,drummer,oem,rig FROM products Where supplierid=2")


--
Best regards,
___________
Alex Dybenko (MVP)
http://accessblog.net
http://www.PointLtd.com

 

[samotek via AccessMonster.com]

Hi! it works ! Thank you !

Hi,
open recordset filtered:
Set rs = CurrentDb.OpenRecordset("SELECT Productid, code, categoryid,
supplierid, size, ddu, grossprice, office,cons,vip,dealer,pref
,bosch,drummer,oem,rig FROM products Where supplierid=2")

I have the following recordset:

[quoted text clipped - 11 lines]

can
i change the recordset?

 

[John Spencer]

Why do it the hard way? This sounds like an update query.
For instance to update all prices by 10 percent.

UPDATE Products
SET GrossPrice = GrossPrice * 1.1
WHERE SupplierID = 2

If you have to do it using a record set then add the where clause to the SQL
statement that is creating the recordset

Dim rs As DAO.Recordset
Dim strSQL as String
strSQL = "SELECT Productid, code, categoryid, supplierid, size" & _
", ddu, grossprice, office,cons,vip,dealer,pref " & _
",bosch,drummer,oem,rig" & _
" FROM products" & _
" WHERE SupplierID = 2"
Set rs = CurrentDb.OpenRecordset(strSQL)

If rs.RecordCount> 0 then
With rs
Do
.Edit
.Fields("GrossPrice") = ??????
.Update
.MoveNext
....

End With
End If 'at least one record


John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County