I don't understand why I am getting the error message "No current record" at my code....Do While rs1!Symbol = rs2!Symbol....it seems that rs1 has values, but rs2 is empty. Any help? Many thanks!! - Private Sub cmbFindLastEssentialData_Click()
-
' On Error GoTo Exit_MyProc
-
-
DoCmd.RunSQL "DELETE * FROM LastGoodData;"
-
-
strSQL = "SELECT DailyPrice.Symbol, DailyPrice.LocateDate, DailyPrice.MarketPrice " & _
-
"FROM DailyPrice, TempSymbol " & _
-
"WHERE (((DailyPrice.Symbol) = [TempSymbol]![Symbol])) " & _
-
"ORDER BY DailyPrice.Symbol, DailyPrice.LocateDate DESC;"
-
-
strSQLTEMP = "SELECT TempSymbol.Symbol" & _
-
" FROM TempSymbol" & _
-
" WHERE (((TempSymbol.Symbol) Is Not Null))" & _
-
" ORDER BY TempSymbol.Symbol;"
-
Debug.Print strSQLTEMP
-
Set db = CurrentDb
-
Set rs1 = db.OpenRecordset(strSQL)
-
Set rs2 = db.OpenRecordset(strSQLTEMP)
-
-
rs2.MoveFirst
-
-
rs1.MoveFirst
-
Debug.Print rs1!Symbol & " " & rs2!Symbol
-
Do Until rs1.EOF
-
Do While rs1!Symbol = rs2!Symbol
-
If rs1!MarketPrice <> -(mLSGC) Or -5.25 Then
-
Debug.Print rs1!LocateDate, rs1!Symbol, rs1!MarketPrice
-
strINSERT = "INSERT INTO LastGoodData (LocateDate, Symbol, MarketPrice) VALUES (#" & rs1!LocateDate & "#, '" & rs1!Symbol & "', " & rs1!MarketPrice & ");"
-
Debug.Print strINSERT
-
db.Execute strINSERT, dbFailOnError
-
Exit Do
-
Else 'only for -5.25 data
-
Debug.Print "I'm here"
-
rs1.MoveNext
-
If rs1!Symbol <> rs2!Symbol Then
-
strINSERT = "INSERT INTO LastGoodData (LocateDate, Symbol, MarketPrice) VALUES (#" & rs1!LocateDate & "#, '" & rs1!Symbol & "', " & rs1!MarketPrice & ");"
-
Debug.Print strINSERT
-
db.Execute strINSERT, dbFailOnError
-
Exit Do
-
ElseIf rs1.EOF Then
-
MsgBox "rs1 eof"
-
strINSERT = "INSERT INTO LastGoodData (LocateDate, Symbol, MarketPrice) VALUES (#" & rs1!LocateDate & "#, '" & rs1!Symbol & "', " & rs1!MarketPrice & ");"
-
Debug.Print strINSERT
-
db.Execute strINSERT, dbFailOnError
-
Exit Do
-
End If
-
-
End If
-
Loop
-
rs2.MoveNext
-
Loop
-
' rs2.MoveNext
-
' If Not rs2.EOF Then
-
' Debug.Print rs2!Symbol
-
' End If
-
'Loop
-
MsgBox "Table updated successfully"
-
-
rs1.Close
-
rs2.Close
-
Set rs1 = Nothing
-
Set rs2 = Nothing
-
Set db = Nothing
-
-
End Sub
3 2801 NeoPa 32,556
Expert Mod 16PB
You do an rs2.MoveNext at the end of your Do Loop but don't ever seem to check for rs2.EOF. I expect this would cause you to hit that error.
NeoPa 32,556
Expert Mod 16PB
In future, please do some trimming work before requesting debugging help.
You should be looking to test code which has all the lines stripped out which are not relevant to the error. When this has been done then post this stripped down version (that still exhibits the problem in the same way) in your question. This will help you find the problem and also save time for our experts going through irrelevant lines of code.
MODERATOR.
As an illustration (It's not enormously shorter, but does save some time going through) - Private Sub cmbFindLastEssentialData_Click()
-
-
strSQL = "SELECT DailyPrice.Symbol, DailyPrice.LocateDate, DailyPrice.MarketPrice " & _
-
"FROM DailyPrice, TempSymbol " & _
-
"WHERE (((DailyPrice.Symbol) = [TempSymbol]![Symbol])) " & _
-
"ORDER BY DailyPrice.Symbol, DailyPrice.LocateDate DESC;"
-
-
strSQLTEMP = "SELECT TempSymbol.Symbol" & _
-
" FROM TempSymbol" & _
-
" WHERE (((TempSymbol.Symbol) Is Not Null))" & _
-
" ORDER BY TempSymbol.Symbol;"
-
Set db = CurrentDb
-
Set rs1 = db.OpenRecordset(strSQL)
-
Set rs2 = db.OpenRecordset(strSQLTEMP)
-
-
rs2.MoveFirst
-
-
rs1.MoveFirst
-
Do Until rs1.EOF
-
Do While rs1!Symbol = rs2!Symbol
-
If rs1!MarketPrice <> -(mLSGC) Or -5.25 Then
-
strINSERT = "INSERT INTO LastGoodData (LocateDate, Symbol, MarketPrice) VALUES (#" & rs1!LocateDate & "#, '" & rs1!Symbol & "', " & rs1!MarketPrice & ");"
-
db.Execute strINSERT, dbFailOnError
-
Exit Do
-
Else 'only for -5.25 data
-
rs1.MoveNext
-
If rs1!Symbol <> rs2!Symbol Then
-
strINSERT = "INSERT INTO LastGoodData (LocateDate, Symbol, MarketPrice) VALUES (#" & rs1!LocateDate & "#, '" & rs1!Symbol & "', " & rs1!MarketPrice & ");"
-
db.Execute strINSERT, dbFailOnError
-
Exit Do
-
ElseIf rs1.EOF Then
-
strINSERT = "INSERT INTO LastGoodData (LocateDate, Symbol, MarketPrice) VALUES (#" & rs1!LocateDate & "#, '" & rs1!Symbol & "', " & rs1!MarketPrice & ");"
-
db.Execute strINSERT, dbFailOnError
-
Exit Do
-
End If
-
End If
-
Loop
-
rs2.MoveNext
-
Loop
-
End Sub
NeoPa 32,556
Expert Mod 16PB
Another problem with your code - related or unrelated to your current issue I don't know - is : - If rs1!MarketPrice <> -(mLSGC) Or -5.25 Then
This will always be TRUE as the value -5.25 is non-zero. It is not being compared to anything so returns the value directly.
Sign in to post your reply or Sign up for a free account.
Similar topics
by: Tony |
last post by:
I have a form in access that has 5 fields for a certain record: 1)Date
mailed, 2)Date received, 3)Date completed 4)Foreign Content amount and
5)record number. If the foreign content (FC)is >15% of...
|
by: Tony |
last post by:
Hello,
I am having difficulty in getting the current record of the current
form to show after pressing a command button that takes me to another
form. The command button takes me to another...
|
by: DBQueen |
last post by:
I have a subform which is in Continuous Forms view.
I have added a button to the bottom of the page to move to the next record
using the button wizard (result: DoCmd.GoToRecord , , acNext).
I...
|
by: Maria |
last post by:
Is there another way to delete the current record in a subform from the main
form, another subform or a sub-subform other than setting focus on a field
in the subform and using run command...
|
by: Zlatko Matić |
last post by:
There is a form (single form) and a combobox. I want that current record of
the form is adjusted according to selected value in the combobox. Cuurrent
record should be the same as the value in the...
|
by: 4004 |
last post by:
I would like to open a columnar form (so I can see all the details)
from a datasheet form (so I can see what is there) but keep the same
recordset and current record.
I can do the recordset set...
|
by: christianlott1 |
last post by:
I admit my form is pretty complex and may need a total overhaul -
I have two subforms synchronized on a form through an unbound text box.
When I enter a new record in the second subform it used...
|
by: roveagh1 |
last post by:
Hi
I've been using the 2 year old link below to repeat values from previous record field into current corresponding field. It's worked fine for text but the last piece of advice was to use the same...
|
by: eighthman11 |
last post by:
Hi everyone:
This is probably a pretty simple problem but it is driving me nuts.
Using Access 2000. I have a continuous form which list several
thousand inventory items. The user can enter a...
|
by: MLH |
last post by:
Cut this little FN out-a-Access 97 HELP. It makes mention
of the new record being made the current record. Is it the
Update method responsible for that - or the Bookmark method?
Which one?
...
|
by: emmanuelkatto |
last post by:
Hi All, I am Emmanuel katto from Uganda. I want to ask what challenges you've faced while migrating a website to cloud.
Please let me know.
Thanks!
Emmanuel
|
by: BarryA |
last post by:
What are the essential steps and strategies outlined in the Data Structures and Algorithms (DSA) roadmap for aspiring data scientists? How can individuals effectively utilize this roadmap to progress...
|
by: Hystou |
last post by:
There are some requirements for setting up RAID:
1. The motherboard and BIOS support RAID configuration.
2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However,...
|
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can...
|
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers,...
|
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven...
|
by: agi2029 |
last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing,...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome a new...
| |