getting certain fields from a particular record

D

DawnTreader

Hello All

i am uncertain as to the best way to do this. i am creating a function to
check out the validity of something that is based on a lot of criteria. i
want to know is it better to build a record set or get the fields i need by
dlookup?

for instance this code:

strProdType = DLookup("ProductTypeID", "tblProductList", "ProductID = "
& Product_ID)
dtDateToCompare = Date
dtDateShipped = DLookup("DateShipped", "tblProductList", "ProductID = "
& Product_ID)
dtDateCommissioned = DLookup("DateCommissioned", "tblProductList",
"ProductID = " & Product_ID)
lngCommissionedMonths = DLookup("WarrantyMonths", "tblProductList",
"ProductID = " & Product_ID)
lngShippedMonths = DLookup("WarrantyShippedMonths", "tblProductList",
"ProductID = " & Product_ID)
lngHours = Nz(DLookup("WarrantyHours", "tblProductList", "ProductID = "
& Product_ID), 4000)
booExtended = DLookup("WarrantyExtended", "tblProductList", "ProductID =
" & Product_ID)
dtDateExtendedFrom = DLookup("DateWarrantyExtended", "tblProductList",
"ProductID = " & Product_ID)
lngHoursExtended = Nz(DLookup("HoursExtended", "tblProductList",
"ProductID = " & Product_ID), 1000)
lngMonthsExtended = Nz(DLookup("MonthsExtended", "tblProductList",
"ProductID = " & Product_ID), 5)

is what i was thinking of using. each of these variables will be from the
same record, so i am thinking that i need an easier way of finding these
fields and turning them in to variables. the function then takes those
variables and makes some decisions and then spits out an answer. that logic i
have all worked out, but the thing to get the necessary information to make
those decisions i wasnt sure how to get it done.

as always, any and all help appreciated.
 
P

Piet Linden

Hello All

i am uncertain as to the best way to do this. i am creating a function to
check out the validity of something that is based on a lot of criteria. i
want to know is it better to build a record set or get the fields i need by
dlookup?

for instance this code:

    strProdType = DLookup("ProductTypeID", "tblProductList", "ProductID = "
& Product_ID)
    dtDateToCompare = Date
    dtDateShipped = DLookup("DateShipped", "tblProductList", "ProductID = "
& Product_ID)
    dtDateCommissioned = DLookup("DateCommissioned", "tblProductList",
"ProductID = " & Product_ID)
    lngCommissionedMonths = DLookup("WarrantyMonths", "tblProductList",
"ProductID = " & Product_ID)
    lngShippedMonths = DLookup("WarrantyShippedMonths", "tblProductList",
"ProductID = " & Product_ID)
    lngHours = Nz(DLookup("WarrantyHours", "tblProductList", "ProductID = "
& Product_ID), 4000)
    booExtended = DLookup("WarrantyExtended", "tblProductList", "ProductID =
" & Product_ID)
    dtDateExtendedFrom = DLookup("DateWarrantyExtended", "tblProductList",
"ProductID = " & Product_ID)
    lngHoursExtended = Nz(DLookup("HoursExtended", "tblProductList",
"ProductID = " & Product_ID), 1000)
    lngMonthsExtended = Nz(DLookup("MonthsExtended", "tblProductList",
"ProductID = " & Product_ID), 5)

is what i was thinking of using. each of these variables will be from the
same record, so i am thinking that i need an easier way of finding these
fields and turning them in to variables. the function then takes those
variables and makes some decisions and then spits out an answer. that logic i
have all worked out, but the thing to get the necessary information to make
those decisions i wasnt sure how to get it done.

as always, any and all help appreciated.
Click to expand...

One way of doing this would be to declare the variables at form level,
then open a recordset to grab all the values at once, since all the
variables but one depend on ProductID. THen you could do something
like this:

Dim rs As DAO.Recordset
Dim strSQL as string

strSQL = "SELECT ProductTypeID, DateShipped, DateCommissioned,
WarrantyMonths, WarrantyShippedMonths, WarrantyHours,
WarrantyExtended, DateWarrantyExtended, HoursExtended, MonthsExtended
FROM tblProductList WHERE ProductID = " & ProductID

set rs=dbEngine(0)(0).OpenRecordset(strSQL, dbOpenSnapshot)

'do this for each variable...
strProductType=rs.Fields("ProductTypeID")
etc etc

rs.Close
Set rs=Nothing
 
D

DawnTreader

Hello

so basically your way is just using a recordset loaded at the form, mine
hunt and pecks through the table. is there really no more eficient way?

basically i have 2 versions of the same function. one gathers all the
information by the query behind a form, or from the table the form is based
on. this version gets one piece of information and then finds the rest
through dlookups.

either way works, but one is less dependant on the form having all the data,
which is why i was creating this version. i was just hoping for something
that might work both ways without a lot of inefficient code.

which is faster, your way or dlookups?
 
J

John Spencer

Using a recordset would be faster = One query versus ten queries.

Assigning the values to variables is quick.

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