Assign a value from a report calculation to a table field

[JohnLee]

Hi,

Hopefully someone can help me here, I have created a report which has objects that use expressions as shown below to calculate volumes:

=DCount("*","qryDDMandateHistoryDGI_DailyCount","[strLetterCode]Like ""ADDB""")

What I now want to do is to assign that value to a field in seperate table.

I have created an array in the report on load event as follows:

Dim Stats(2) As String

Stats(0) = Format(Now(), "dd/mm/yy")
Stats(1) = "ADDB"
Stats(2) = DCount("*", "qryDDMandateHistoryDGI_DailyCount", "[strLetterCode]Like ""ADDB""")

Dim Stats0 As Date
Dim Stats1 As String
Dim Stats2 As Integer

Stats0 = Stats(0)
Stats1 = Stats(1)
Stats2 = Stats(2)

I want to assign the values of Stats0, Stats1 and Stats2 to fields in a new table, those fields being

DtmDate for Stats0
txtLetterType for Stats1
lngVolume for Stats2

The table is called tblStatistics

If anyone can tell me how I can do this, I would be most grateful

John

 

[Rabbie]

In general you should not store calculated values in tables. This because of the overhead of updating these entries if any of the values used for the calculation change. You should calculate the values in a query when you need them thus ensuring they are accurate.

 

[JohnLee]

Hi,

Thanks for your reply, and I understand your advice, however, since these values are not going to change [hence the seperate table] doing the calculations that I require would be a bit too complex and would take the new report far too long to generate.

If you assist me with how I can assign the values I want to a table that would be great, if this is not possible then thanks once again for your reponse.

John

 

[s1police]

can you make it in this way?

Dim rs As Recordset

Set rs = CurrentDb.OpenRecordset("tblStatistics")

'I think there should have a function to purge the record in tblStatistics. You can try 'yourself

rs.AddNew
rs("DtmDate ") = Stats(0)
rs("StxtLetterType") = Stats(1)
rs("lngVolume ") = Stats(2)

rs.Update

Correct me if I am wrong.