Adding data to a table

[pp8082]

Hi, I'm new to ACCESS


What I am trying to do is:

I am looping the the table SIGNALS and would like to append data to the table EQUITY.

The following code is just a shell. Just to prove the logic works.
Well, it doesn't

You help is appreciated.

Public Sub trade()

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

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


Dim tradecount As Integer

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

' loop thru the rst SIGNALS until you reach the end of the file
Do Until rst.EOF


' increment the counter
tradecount = tradecount + 1

' Add a row of data to rst2 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




End Sub

 

[Rx_]

Please put the code inside Code Tags - it improves spacing and prevents funny characters from appearing.
Open square brackets CODE Close square brackets
The Code
Open square brackets slash CODE Close square brackets

Would suggest replacing the loop and move next with Execute (sql statement) that returns a count. The (sql statement) can include all records or any filter needed such as the select * from signals or select * from signals where (some condition).
The SQL statement follows this:

INSERT INTO target [(field1[, field2[, …]])]
VALUES (value1[, value2[, …]])

RunSQL and OpenQuery are the simplest to invoke with vba, but they display warnings, which require user interaction. One option for suppressing the messages is to SetWarnings False before running the queries, then SetWarnings True after:

  DoCmd.SetWarnings False

  ' OpenQuery executes a saved query 
  ' cstrQueryName is a constant with the name of an action query
  DoCmd.OpenQuery cstrQueryName   ' example only

  ' RunSQL executes a SQL string ' You could create an Append query for your example that updates the Count value.
  DoCmd.RunSQL "DELETE FROM tblMyTable WHERE Bad = True;"
DoCmd.SetWarnings True

 ' something along this line is probably best for your situation
dbs.Execute "INSERT INTO tblDailyCustSales( [InvoiceNumber] )" & _
               " VALUES ( " & Me.txtCustSalesID & " )", dbFailOnError 
          ' you simply design an append query and add the criteria
          ' this shows the value of a text box, but it could be a variable with your count.

For obtaining a record count in a variable - this is an example:

Dim db As DAO.Database  ' you can find an equal in ADO
Dim rs As DAO.Recordset

Dim rsCount As Integer
Dim queryNameOrSQL As String

queryNameOrSQL = "<Query Name or the SQL Statement goes here>"

Set db = CurrentDb
Set rs = db.OpenRecordset(queryNameOrSQL)
     rs.movelast ' forces cursor to last record in larger recordsets
rsCount = rs.RecordCount

 

[pp8082]

Thanks for responding, but I need to loop thru the SIGNALS table. This fields in this table are Date , Flag. Flag can be 0 or 1. I'll be using code to determine from and to dates and other date fields that then get loaded into the EQUITY table. The Tradecount field is just a transaction number that will be generated.