For a certain database I'm looping a record which fills an Excel worksheet. All working fine.
in simple:
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
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
How/when can I point to this currentdb.recordsets-collection?
Or maybe...what other solution is there to solve this problem
in simple:
Code:
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
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
Code:
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
Code:
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