Using DAO

[Matt Pierringer]

Alright I am using DAO to loop through a recordset(like my other
question, I still need help in my other question as well, but maybe I
can get a smaller answer here), but I am going to be way more specific
on what I need.

Set strSql = "SELECT tblProducts.Catalog, tblProducts.MaterialNumber,
tblProducts.Manufacturer, tblProducts.GMR, tblProducts.Category,
tblProducts.Description, tblProducts.[Sub-Category],
tblProducts.SortOrder, tblProducts.AddedNote, tblProducts.Required,
tblProducts.NoList, tblProducts.Hyper_Link, tblProducts.ProductID,
tblProducts.Deleted, tblProducts.Cost From tblProducts WHERE
(((tblProducts.Manufacturer) Is Not Null And
(tblProducts.Manufacturer) Like " & rsQuery!Manufacturers & ") AND
((tblProducts.Deleted)=False));"


My question is when I am using DAO how should I be referring to what I
need "rsQuery!Manufacturers" or should it be a string like strManuf
and how would I go about declaring that (as DAO.recordset?)


Thanks,
Matt

 

[Guest]

Dim rs as new DAO.recordset

rs.fields("Fieldname").Value

don't foreget do do a
rs.close
set rs = nothing
at the end of your procedure

-Dorian

 

[Guest]

By the way your 'SELECT' statement is invalid. You need a FROM clause.

-Dorian

 

[John W. Vinson]

Alright I am using DAO to loop through a recordset(like my other
question, I still need help in my other question as well, but maybe I
can get a smaller answer here), but I am going to be way more specific
on what I need.

Set strSql = "SELECT tblProducts.Catalog, tblProducts.MaterialNumber,
tblProducts.Manufacturer, tblProducts.GMR, tblProducts.Category,
tblProducts.Description, tblProducts.[Sub-Category],
tblProducts.SortOrder, tblProducts.AddedNote, tblProducts.Required,
tblProducts.NoList, tblProducts.Hyper_Link, tblProducts.ProductID,
tblProducts.Deleted, tblProducts.Cost From tblProducts WHERE
(((tblProducts.Manufacturer) Is Not Null And
(tblProducts.Manufacturer) Like " & rsQuery!Manufacturers & ") AND
((tblProducts.Deleted)=False));"


My question is when I am using DAO how should I be referring to what I
need "rsQuery!Manufacturers" or should it be a string like strManuf
and how would I go about declaring that (as DAO.recordset?)

You're not using DAO, not yet anyway. You've set up a text string and (in your
posted code) you've said nothing at all about recordsets. Where is rsQuery
defined? Do you have a previous OpenRecordset action?

I'd avoid using LIKE unless you explicitly want to use wildcards. The =
operator is more efficient. If Manufacturer is a Text field you'll also need
quotemark - Chr(34) is " -delimiters:

tblProducts.Manufacturer = " & Chr(34) & rsQuery!Manufacturers & Chr(34) & ")

John W. Vinson [MVP]

 

[David W. Fenton]

Alright I am using DAO to loop through a recordset

What are you doing when looping through the recordset? If you're
updating a bunch of records to the same value or to some value that
can be calculated from the values in the same row, then a SQL UPDATE
query would likely be vastly faster.