Adding record to a table

[pp8082]

Hi,

I am new to ACCESS and need help with this.

I have a table named SIGNALS. It has 5000 records in it.
I want to loop through this table. Do some calculations with the data
and add a record to a table named EQUITY.

I can't get the code to work after days of trying. Ready to give up.

Below is a shell just to prove the concept.



Public Sub test()

On Error GoTo errorrtn

Dim tradecount As Integer

'Declare record sets
Dim rst As ADODB.Recordset
Set rst = New ADODB.Recordset
Dim rst2 As ADODB.Recordset
Set rst2 = New ADODB.Recordset

'Set up the connection, name it cnn1 .
Dim cnn1 As ADODB.Connection
Set cnn1 = New ADODB.Connection






rst.LockType = adLockOptimistic
rst.Open "select * from signals", CurrentProject.Connection

Do Until rst.EOF


tradecount = tradecount + 1

' Add a new record to EQUITY table
With rst2
.ActiveConnection = CurrentProject.Connection
.CursorType = adOpenKeyset
.LockType = adLockOptimistic
.Open "select * from equity where id = 0 "
.AddNew
!id = tradecount
.Update
End With




rst.MoveNext
Loop
errorrtn:

Exit Sub

End Sub

 

[plog]

Do some calculations with the data
and add a record to a table named EQUITY.

Why does this have to be done with record sets in VBA? My first inclination would be to do it with an APPEND query. Actually, strike that, my first inclination would just be to create a SELECT query that has all the data you need in it and not 'put' this data into a new table.

So why does this data need to be added to a table?

 

[pp8082]

i don't have an answer for you. This is just the way I approached .
I need to calculate the following:
the date I bought the stock,
the date I sold it
length of time in the trade
profit
percentage gained
trade drawndown (based on hightest equity and lowest equity

If it can be done with a select query, then I don't know how

 

[plog]

Can you provide the table name, field names, sample data in the table and then what the result should be based on that sample data? Use this format to post:

TableName
fieldname1, fieldname2, fieldname3
17, 1/3/2013, "David"
19, 2/1/2013, "Sally"