Eval() & database.recordsets

[Zakraket]

For a certain database I'm looping a record which fills an Excel worksheet. All working fine.

in simple:

Sub WriteExcel()
rst as recordset
rstDifferent as recordset

for each Record in RST
   Wsht.Cells(t, "A") = "EUR" & "000"
   Wsht.Cells(t, "B") = Round(RST!Amount,2) * 100
   Wsht.Cells(t, "C") = left(RST!name, 18)
   t = t + 1
next record

So, each record creates a line in Excel with certain values.

However, for different users the code should insert entirely different data/formulas, so the above is for user1, but for user2 the Cells should be filled as follows
User2:
Cell A should contain: format(now(), "ddmmyy")
Cell B should contain: rstDifferent!field1 * 19%
Cell C should contain: rst!Adress

In other words, completely different values/formulas

To solve this I could copy the entire Sub and make a version which "handles" different variables into the cells, but I don't want to do this because it's a very complex function which should only differ in the values that are written in the cells. Everything else is the same and I only want to maintain one function.

Another approach would be to place the userspecific pieces of code in a table/recordset (or maybe a INI-file) and use the EVAL()-function to evaluate the specific user-formulas

So it would be something like

Sub WriteExcel(strUser as string)
rst as recordset
rstDifferent as recordset

for each Record in RST
   Wsht.Cells(t, "A") = Eval(GetValue(strUser,"A"))
   Wsht.Cells(t, "B") = Eval(GetValue(strUser,"B"))
   Wsht.Cells(t, "C") = Eval(GetValue(strUser,"C"))
   t = t + 1
next record

'GetValue(strUser, Field) = a function that returns the wanted "code" for a specific user from a recordset or INI-file

In this case, for user1 it would work like this when going through the cells:
Wsht.Cells(t, "B") = Eval(Round(RST!Amount,2) * 100)
where Round(RST!Amount,2) * 100 is the value returned by the GetValue() function

This works, however, only for situations where I do not want a value from a recordset: the EVAL()-function cannot "see" the recordsets that are active, so I cannot use this for values like RST!Amount or rstDifferent!field1

So, it works for cells A, but not for B and C

There is a Database.recordsets-collection (currentDB.recordsets). I think I could use this in a way like
currentdb.recordsets("RST").fields("Amount"), so the Eval would be

Wsht.Cells(t, "B") = Eval(Round(currentdb.recordsets("RST").fields("Amount"),2) * 100)

This doens't work however. When I make a simple sub that loops all recordsets it doesn't return anything: there are no "rst"s so it doesn't go to the debug.
This collection always seems to be empty

Public Sub testrecordsets()
Dim rst As dao.Recordset
For Each rst In CurrentDb.Recordsets
    'never reached
    Debug.Print rst.Name
Next rst
End Sub

How/when can I point to this currentdb.recordsets-collection?



Or maybe...what other solution is there to solve this problem

edit: hmm...a possibility could be to Replace() a certain value in the result of the GetValue()-function with the value of the wanted field.

So:
- GetValue returns "Round(RSTFIELD,2) * 100" and then
- Eval(Replace(GetValue(),"RSTFIELD", rst!Field))

This way, Eval will be Evaluating "Round(123.044324,2)*100"

It will work, but it limits me in a way that the actual used recordset is still predetermined in the code (the blue field above)
Remember, I want to use different fields from different recordsets per user

If I could refer to a recordset recordsets("rst") like I can to a field rst("field") my problem would be solved.

 

[pr2-eugin]

Please refrain from Double Posting !