No current record.

[Guest]

These are the messages that came up in order of appearance when I ran this
code.

Once you click Yes you can’t use the Undo command to reverse the changes.
Are you sure you want to append the selected rows?

Microsoft Access has set 0 field(s) to Null due to a type conversion failure
and it didn’t add 1 record(s) to the table due to key violations, 0 record(s)
due to lock violations and 0 record(s) due to validation rule violations. Do
you want to run the action query anyway? To ignore the error(s) and run the
query, click Yes.
For an explanation of the causes of the violations, click Help.

No current record.

What does that mean? It is very different from before. I changed 'rst' to
'Dim rst As DAO.Recordset', which caused the change. If anyone has any ideas,
I'd appretiate it.


Option Compare Database
Option Explicit

Dim strSymbol As String
Dim intRef As Integer
Dim strSQL As String
Dim dteLineDate As Date
Dim rst As DAO.Recordset

Private Sub CopyData()
On Error GoTo Err_CopyData

strSymbol = InputBox("Enter Symbol Code:")
intRef = -63
'Starting value for dteLineDate 1 less than first Raw Data date:
strSQL = "SELECT TOP 1 [RAW DATA].[DATE] AS TopDate FROM [RAW DATA] " & _
"WHERE [RAW DATA].SYMBOL = '" & strSymbol & "'"
Set rst = CurrentDb.OpenRecordset(strSQL)
dteLineDate = rst!TopDate - 1

'Now a loop copies 64 records to Intermediate with ascending
'date and incremented REF:

While intRef < 1
strSQL = "INSERT INTO INTERMEDIATE (SYMBOL, [DATE], HIGH, " & _
"LOW, [LAST], VOLUME) SELECT TOP 1 SYMBOL, [DATE], HIGH, " & _
"LOW, [LAST], VOLUME FROM [RAW DATA] WHERE " & _
"[RAW DATA].SYMBOL = '" & strSymbol & "' AND " & _
"[RAW DATA].[DATE] > #" & dteLineDate & "# " & _
"ORDER BY [RAW DATA].[DATE];"
DoCmd.RunSQL strSQL

strSQL = "SELECT TOP 1 INTERMEDIATE.[DATE] AS ThisDate FROM INTERMEDIATE "
& _
"WHERE (INTERMEDIATE.SYMBOL = '" & strSymbol & "' AND " & _
"INTERMEDIATE.REF IS NULL) ORDER BY INTERMEDIATE.[DATE];"
Set rst = CurrentDb.OpenRecordset(strSQL)
dteLineDate = rst!ThisDate
strSQL = "UPDATE INTERMEDIATE SET REF = " & intRef & _
" WHERE (INTERMEDIATE.SYMBOL = '" & strSymbol & "') AND " & _
"(INTERMEDIATE.[DATE] = #" & dteLineDate & "#);"
DoCmd.RunSQL strSQL

intRef = intRef + 1
Wend

Exit Sub

Err_CopyData:
MsgBox Err.Description
Exit Sub

End Sub

 

[Guest]

it didn’t add 1 record(s) to the table due to key violations
I would check the data for duplicates in the primary key field of the data
being inserted.

 

[Van T. Dinh]

I think there are quite a few things you need to fix to make your code works
reliably:

1. If you use the TOP predicate in the SQL String, then you need to use the
ORDER BY clause in your SQL. Since JET retrieves records in order that it
thinks as the most efficient way, this mean the records can be retrieved
randomly and TOP 1 could mean any record that meet the selection criteria.
The exception is that you expect the selection criteria to return only 1
record but then, in this case, you don't need the TOP 1 predicate.

I noted that you use the ORDER BY clause in the 3rd SQL (2nd SQL with TOP 1)
but not the first SQL.

2. When you want to get the Field value of a Recordset, you need to check
for empty Recordset *before* referencing the Field (of the current row of
the Recordset). In fact, my guess is that the error "No current record." is
caused by the statement:

dteLineDate = rst!ThisDate

and the Recordset rst is empty (and rst may be empty because of the TOP 1
predicate in 1 returns a value different from what you expected.

3. Check Access VB Help on the Execute method of the Database object and
use Execute instead of RunSQL. From what I read, Execute is more efficient
and you have the dbFailOnError option so that you can trap the error.

4. I have not read your code carefully but it looks like your code appends
a record and then goes through some more just to update this new record???
If that's the case, you should be able to append the record with correct
data without needing to update the record.

5. You can also use Recordset to append / update record in the recordset.
It seems to me that your code switch between Recordset just to retrieve
value(s) and the use SQL to append / update. IMHO, it is much cleaner
coding and easier to read if you use the Recordset base on Table
[INTERMEDIATE] to retrieve value(s), to append and to update rather than the
Recordset and SQLs.

6. In addition, WHILE ... WEND is the older construct and you should use DO
WHILE ... LOOP and actually since you have a fixed number of iterations,
the FOR ... NEXT construct is probably more appropriate.

7. Check Access VB Help also on the SetWarnings method if you still want to
use RunSQL rather than Execute.

--
HTH
Van T. Dinh
MVP (Access)

These are the messages that came up in order of appearance when I ran this
code.

Once you click Yes you can't use the Undo command to reverse the changes.
Are you sure you want to append the selected rows?

Microsoft Access has set 0 field(s) to Null due to a type conversion
failure
and it didn't add 1 record(s) to the table due to key violations, 0
record(s)
due to lock violations and 0 record(s) due to validation rule violations.
Do
you want to run the action query anyway? To ignore the error(s) and run
the
query, click Yes.
For an explanation of the causes of the violations, click Help.

No current record.

What does that mean? It is very different from before. I changed 'rst' to
'Dim rst As DAO.Recordset', which caused the change. If anyone has any
ideas,
I'd appretiate it.


Option Compare Database
Option Explicit

Dim strSymbol As String
Dim intRef As Integer
Dim strSQL As String
Dim dteLineDate As Date
Dim rst As DAO.Recordset

Private Sub CopyData()
On Error GoTo Err_CopyData

strSymbol = InputBox("Enter Symbol Code:")
intRef = -63
'Starting value for dteLineDate 1 less than first Raw Data date:
strSQL = "SELECT TOP 1 [RAW DATA].[DATE] AS TopDate FROM [RAW DATA] " & _
"WHERE [RAW DATA].SYMBOL = '" & strSymbol & "'"
Set rst = CurrentDb.OpenRecordset(strSQL)
dteLineDate = rst!TopDate - 1

'Now a loop copies 64 records to Intermediate with ascending
'date and incremented REF:

While intRef < 1
strSQL = "INSERT INTO INTERMEDIATE (SYMBOL, [DATE], HIGH, " & _
"LOW, [LAST], VOLUME) SELECT TOP 1 SYMBOL, [DATE], HIGH, " & _
"LOW, [LAST], VOLUME FROM [RAW DATA] WHERE " & _
"[RAW DATA].SYMBOL = '" & strSymbol & "' AND " & _
"[RAW DATA].[DATE] > #" & dteLineDate & "# " & _
"ORDER BY [RAW DATA].[DATE];"
DoCmd.RunSQL strSQL

strSQL = "SELECT TOP 1 INTERMEDIATE.[DATE] AS ThisDate FROM INTERMEDIATE
"
& _
"WHERE (INTERMEDIATE.SYMBOL = '" & strSymbol & "' AND " & _
"INTERMEDIATE.REF IS NULL) ORDER BY INTERMEDIATE.[DATE];"
Set rst = CurrentDb.OpenRecordset(strSQL)
dteLineDate = rst!ThisDate
strSQL = "UPDATE INTERMEDIATE SET REF = " & intRef & _
" WHERE (INTERMEDIATE.SYMBOL = '" & strSymbol & "') AND " & _
"(INTERMEDIATE.[DATE] = #" & dteLineDate & "#);"
DoCmd.RunSQL strSQL

intRef = intRef + 1
Wend

Exit Sub

Err_CopyData:
MsgBox Err.Description
Exit Sub

End Sub