Click here to Skip to main content
15,850,753 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
I use similar code to this in other areas of my program, my Database seems to be working OK, the SQL line works OK in Management Studio, but every time I get to this point, the program exits the routine, it just skips to the End Sub as soon as it hits the .Open line ... If there is a problem with the Database, surely I should get an error when I check the State (which returns 0) ?!?

Sub LoopDatabaseLeague()

On Error GoTo LoopDatabaseLeagueError

SQLstr = "SELECT Name, LeagueType_ID From League Where League.[LeagueType_ID] = '2';"

myEmailFolder = ""

Set KA_RS_League = New ADODB.Recordset

If KA_RS_League.State = adStateOpen Then KA_RS_League.Close <<< State = 0

KA_RS_League.Open SQLstr, KA_DB, adOpenDynamic, adLockOptimistic <<< This line skips to the end

Do Until KA_RS_League.EOF
    KA_Which_Newsletter.ComboBox1.AddItem (KA_RS_League![Name])
    If FindString(mySubject, KA_RS_League![Name]) Then
        myEmailFolder = KA_RS_League![Name]
    End If


End Sub

What I have tried:

Different code, searched internet, looked for error codes
Updated 21-Mar-17 2:06am
CHill60 21-Mar-17 5:31am    
You are "swallowing" the error by having an empty error handler - this is very very bad practice. Comment out On Error GoTo LoopDatabaseLeagueError, run the code and tell us what the error actually is
Gary Heath 21-Mar-17 5:59am    
It makes very little difference, instead of the next statement being the End Sub, it is the line following the call to this module :-(

Is there not a variable somewhere that holds the result of the statement ? If so what / where can I find it ?!?
CHill60 21-Mar-17 10:15am    
It makes a huge difference. By getting rid of the on error goto, a VISIBLE exception would be raised which would help you find out what the problem is. You have "no idea why the program cannot just tell me that !!!" ... It doesn't tell you that because YOU told the program NOT to tell you anything about ANY errors.
Gary Heath 21-Mar-17 10:47am    
I have tried Goto 0 & Goto Next but it didn't tell me anything, what I meant by my comment was that if there is no Connection to the Database to where I am creating a Recordset, why does it not report that, under any of the Goto options ?!?
CHill60 21-Mar-17 10:15am    
Notice the Reply link next to comments? If you use that link then the poster is notified of your response. In other words, I could have given you further assistance 4 hours ago

1 solution

I was losing my Connection to the database somewhere along the line, no idea why & no idea why the program cannot just tell me that !!!

Resolved now anyway, thanks for your help ...
Share this answer
Richard MacCutchan 21-Mar-17 10:01am    
It would have told you if you had not chosen to ignore errors in your program.
CHill60 21-Mar-17 10:13am    
A virtual 5 for this comment :-)
Richard MacCutchan 21-Mar-17 10:38am    
I often wonder how many real released business apps are running around the world with this sort of nonsense left in.
Gary Heath 21-Mar-17 11:24am    
As I said to CHill60, Richard, I tried Goto 0 & Goto Next, it just dropped out of the routine and the Recordset was closed / didn't exist in my logic ... this was because the Connection to the Database was obviously being reset somewhere, but I don't know where. I have now closed it at the end of the previous Module and Connect again at the start of this Module & that works OK.

What I do not understand is why the program will process the lines ...

Set KA_RS_League = New ADODB.Recordset
If KA_RS_League.State = adStateOpen Then KA_RS_League.Close

... without a Connection, regardless of the Goto I was using, I was getting no error message or code, just an "Out of Context" message in the Watch.

Still all sorted now, cheers :-)

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900