Using Recordsets and a called Function 1

[mmogul]

I have a recordset of a small file and depending upon values in another file, I retrieve values from the recordset.

Since this process is used frequently in this application, I would like to put it in a function. But since I have opened the recordset in the calling Procedure, the function fails -- I think because it doesn't recognize the open recordset.

My code is:

rs.Open "tblTempMfgProductData", CurrentProject.Connection, adOpenStatic

        strElement = "Weight"
        strSupplier = Left(Trim(Supplier), 6)

'******************************************************
'**  I would like this next line to be the function call
'        NetWeight = getElement(strSupplier, strElement)
'********************************************************

'** This next section would be the function -- getElement(Supplier, Element)        
        iRow = DLookup("[Row]", "tblElements", "[Supplier] = '" & strSupplier & "'" _
            & " AND [Element] = '" & strElement & "'")
        iColumn = DLookup("[Column]", "tblElements", "[Supplier] = '" & strSupplier & "'" _
            & " AND [Element] = '" & strElement & "'")
'********************************
' When this is set up as a function, the "rs.movefirst" fails 
' because it the recordset is not recognized in the function
        rs.MoveFirst
        rs.Move (0 + iRow - 1)
        NetWeight = rs(iColumn - 1)

Please suggest how I could use a function for this process.

I thought about opening and closing the recordset in the function, but since it would be called frequently, I didn't think that would be efficient.

Thanks.

 

[Remou]

This makes me a little nervous in that it seems to me that it should be possible to join the two tables and return a result. It is not proper in a relational database to move a number of rows to get a result. Perhaps you could supply a little data from the two tables?

 

[mmogul]

We receive product information from our suppliers in various excel formats. We are developing a process to put this information in a relational database so we can run cost trend reports, comparison reports etc.

While each supplier is consistent in the format they use from period to period, they are different from one another. We have been unable to establish a standard (too many reasons to get into).

So instead of having a customized program for each format, I have a table that identifies where key data elements (ex: Product Name, Product Weight) are located in the spreadsheet (ex: cell a1 for Supplier ABC stores Product Name, but it is in cell b1 for Supplier DEF).

I load the supplier's spreadsheet into a temporary table. Then I use the identification table to extract the key elements I need. I then place those into a relational db.

These tables are not joinable.

Hope that helps.



These

 

[Remou]

I will make a few notes that can be expanded upon, if you wish to choose an idea.

* You can pass a recordset to a function:

[tt]Function getElement(Supplier, Element, rs As Recordset)[/tt]

* You can store tables in global arrays, if they are not too big. The variable can be declared in a general module and array can be build in a suitable open event. There is a problem with global arrays in that they get reset if you have unhandled errors, but this should be easy enough to cope with:

[tt]gvarMfgData=rs.GetRows

row1 = LBound(gvarMfgData, 2)
rown = UBound(gvarMfgData, 2)
field0 = LBound(gvarMfgData, 1)
fieldn = UBound(gvarMfgData, 1)[/tt]

 

[mmogul]

Thanks - I thinks that sets me in the right direction. I appreciate your time.