INSERT SQL promting me for variables. Need help

[pp8082]

I am running ACCESS 2000.
I have stripped this down to the bare bones to figure it out. I am stumped.

i have a table named TRADES. It has 1 field in it which is also the primary key.

When I run the code below, the DoCmd.RunSql displays a window prompting me to enter the value.

I don't want a prompt . I want to loop throught the entire record set
and load the table with a record without a prompt.

What am I doing wrong??

tradeno: is a field in the table Trades. It is a primary key
reccount: is a variable field that I add to each time a process a record

--------------------------------------------------



mysqlstring = "INSERT INTO Trades (tradeno) values (reccount) "
Do Until rst.EOF
profit = profit + 1
reccount = reccount + 1

DoCmd.RunSQL mysqlstring

GoTo GetNextRec

GetNextRec:

rst.MoveNext
Loop
End Sub

 

[jdraw]

Please show us all of your code.
Why are you storing a count?

What are you trying to do in plain English?

 

[pp8082]

I am trying to insert rows into a table.
The reccount is just a sequence number that is unique and will serve as a key.

Since I posted this, I changed the sql string from:

mysqlstring = "INSERT INTO Trades (tradeno) values (reccount ) "

to:
mysqlstring = "INSERT INTO Trades (tradeno) values (" & reccount & ") "

When I run it this way, ACCESS displays:
I'm about to append 1 row

Then displays a second message saying it can't append record due to conversion failure, key violation , and record locks


below is all my code


Public Sub GainLoss()
On Error GoTo ErrorRtn
Dim mysqlstring As String
Dim reccount As Long
Dim profit As Single

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

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


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

mysqlstring = "INSERT INTO Trades (tradeno) values (" & reccount & ") "

Do Until rst.EOF
Initialize_Rtn:

reccount = reccount + 1

DoCmd.RunSQL mysqlstring



GoTo GetNextRec


GetNextRec:

rst.MoveNext
Loop
ErrorRtn:
Exit Sub
End Sub