db HELP! This just doesn't make any sense!!!! 1

[nicklad]

Hi
i have a problem with some database code:
Im using ADO and a Microsoft Access db, this specific code is meant to check if a User has the yes/no field "STAccess" ticked - if so, the option button "optStat" becomes visible, and if not, the label "lblNoSTAccess" becomes visible.
NOW HERE's THE PROBLEM:
I just dont know what i've done to my code! one minute it was working fine, and then a little while later i get "Run Time Error '3021': Either BOF or EOF is true, or the current record has been deleted. Requested operation requires a current record."
When i check the db, everything is in order, WHAT COULD HAVE HAPPENED?!!!?!?!
The code is below, and the line of code which causes the error is in red:

Private Sub Form_Load()

Open App.Path & "\login.riw" For Input As #1
lblStatID.Caption = Input(LOF(1), 1)
Close #1

Dim sql As String
Dim dbconnection As New ADODB.Connection
Dim rs As New ADODB.Recordset

'Open the database
dbconnection.Provider = "Microsoft.Jet.OLEDB.4.0"
dbconnection.Open App.Path & "\RadioiW.mdb"

'Prepare the recordset
rs.ActiveConnection = dbconnection
rs.CursorType = adUseStatic
rs.CursorLocation = adUseClient

sql = "SELECT * FROM UserAccess WHERE StationID='" & lblStatID.Caption & "'"

rs.Open sql

If rs("STAccess&quot Then

optStat.Visible = True
lblNoSTAccess.Visible = False
Else
optStat.Visible = False
lblNoSTAccess.Visible = True
End If

End Sub


Im actually stuck! Please help me!
thanks
~nick

 

[Guest_imported]

hey ~nick

I hope u should check the status of the Recordset. or Why dont u take a variable and push the value to the STAccess to it.

Bye

 

[nicklad]

how do i use a variable for the "STAccess"? it was working perfectly just a little while ago, so i rekon it muct be somethin basic
Check the status of the recordset? how is that done?
if u mean check if the "STAccess" yes/no field is there and working, then i have. nothing has changed in the .mdb file

 

[sunaj]

Anyway its not a very safe way handle the recordset.

Try something like
-----------------------------------------------------------
sql = "SELECT STAccess FROM UserAccess WHERE StationID='" & replace(lblStatID.Caption,"'","''&quot & "'"

rs.Open sql
if not rs.eof and not rs.bof then
rs.movefirst
If rs("STAccess&quot Then
optStat.Visible = True
lblNoSTAccess.Visible = False
Else
optStat.Visible = False
lblNoSTAccess.Visible = True
else
MsgBox "An error has occured. No records match " & sql
End If
rs.close
set rs = nothing
-----------------------------------------------------------

If you don't move backwards in the recordset ( and don't use .recordcount), you should use a 'forward only' cursortype (default) - it is much faster.


Sunaj
'The gap between theory and practice is not as wide in theory as it is in practice'

 

[nicklad]

this code that u, sunaj, wrote does take away the runtime error, but it doesn't seem to be making either optStat or lblNoSTAccess visible
what could have happened?!

 

[sunaj]

Good question, you don't get the msgbox?
Try and debug through the code and see what happens...
Sunaj
'The gap between theory and practice is not as wide in theory as it is in practice'

 

[nicklad]

when i debug the code, it runs perfectly up until the if not rs.eof and not rs.bof then line in which it skips all the lines until the end if at the end, hence missing out the bit which tells the program to make optStat and lblNoSTAccess visible/invisible

 

[mmilan]

Okay - put a breakpoint on

if not rs.bof and not rs.eof then

Now run the code - when it breaks, in the immediate window (Cntrl-G), type "? rs.source"

Copy the result of this to the clipboard, then go to the access data, create a query in SQL view, and paste the resulting SQL into the query. Now run the query. If no records are returned, the code works fine and your data is the problem.

Another thought I had would be to check the filter property of the recordset, but there's no reason why that should be set. Check it anyway though.

mmilan

 

[sunaj]

If both rs.eof and rs.bof are true is means that the recordset returns empty (e.i. it is your data/sql select that is the problem). By the way, I forgot an 'end if' in my code:

-----------------------------------------------------------
sql = "SELECT STAccess FROM UserAccess WHERE StationID='" & replace(lblStatID.Caption,"'","''&quot & "'"

rs.Open sql
if not rs.eof and not rs.bof then
rs.movefirst
If rs("STAccess&quot Then
optStat.Visible = True
lblNoSTAccess.Visible = False
Else
optStat.Visible = False
lblNoSTAccess.Visible = True
End if
else
MsgBox "An error has occured. No records match " & sql
End If
rs.close
set rs = nothing
-----------------------------------------------------------
Sunaj
'The gap between theory and practice is not as wide in theory as it is in practice'

 

[nicklad]

i ran the query and it returned the results i thought it would: users who had access to STAccess had the yes/no "STAccess" field ticked, and those who didnt have access did not have that field ticked
what does this mean?

 

[nicklad]

sanuj:
also, after i put that END IF in the proper place, it would just load up lblNoSTAccess for all users, no matter if they had access to STAccess or not

 

[sunaj]

There is something that I don't understand. What is you recordset supposed to return: a value that indicates if one specific user has access or not (1 row) or values that returns access information for all users (many rows).
The code that I posted assumes that we are dealing with one user only.

Try debugging your project and use watch to follow what happens. If you can't figure it out post the entire code and I'll have a look at it. Sunaj
'The gap between theory and practice is not as wide in theory as it is in practice'

 

[CajunCenturion]

What type of field is STAccess, and what are the values that you are expecting to find for that field in the RecordSet?
Good Luck
--------------
As a circle of light increases so does the circumference of darkness around it. - Albert Einstein

 

[nicklad]

my recordset is trying to look at ONE user's info. it is checking to see if the yes/no field "STAccess" is ticked or not.
If it is ticked:
optStat.Visible = True
lblNoSTAccess.Visible = False

if not:
optStat.Visible = False
lblNoSTAccess.Visible = True

 

[CajunCenturion]

if STAccess is a yes/no field, then sunaj's code should work. I would like you to try the following code and see what happens.

sql = "SELECT STAccess FROM UserAccess WHERE StationID='" & replace(lblStatID.Caption,"'","''&quot & "'"

rs.Open sql
if not rs.eof and not rs.bof then
rs.movefirst
optStat.Visible = (rs.Fields("STAccess&quot = True)
lblNoSTAccess.Visible = Not optstat.visible
else
MsgBox "An error has occured. No records match " & sql
optStat.Visible = false
lblNoSTAccess.Visible = false
End If
rs.close
set rs = nothing
Good Luck
--------------
As a circle of light increases so does the circumference of darkness around it. - Albert Einstein

 

[CajunCenturion]

sunaj,
I was able to find the code that would send up in PDF documents. The code has been rewritten in "C", no longer in Pascal. The last section (3-6) deals with methods for 2 or more dimensions, but there is some background and I think perhaps some routines referenced from the first sections.

http://www.library.cornell.edu/nr/bookcpdf/c3-1.pdf

http://www.library.cornell.edu/nr/bookcpdf/c3-2.pdf

http://www.library.cornell.edu/nr/bookcpdf/c3-3.pdf

http://www.library.cornell.edu/nr/bookcpdf/c3-4.pdf

http://www.library.cornell.edu/nr/bookcpdf/c3-5.pdf

http://www.library.cornell.edu/nr/bookcpdf/c3-6.pdf

Good Luck
--------------
As a circle of light increases so does the circumference of darkness around it. - Albert Einstein

 

[sunaj]

CajunCenturion,

Thank you for finding the material for me .
I'll have a look at it when I can find the time. Here's a star for the trouble (even though you recently overtook me in total number of stars ). Sunaj
'The gap between theory and practice is not as wide in theory as it is in practice'

 

[nicklad]

it think that its not any of my code that's the problem, but instead its my Access Database. Now this i have no idea what could be wrong! I've created new ones, but it did nothing. Is there some References or components that i might be missing?
OR is there another way i can approach this? besides the yes/no fields, could i use a number instead, like 1=yes and 2=no, or just the words "yes" and "no"
?
i'll have a go, but if this sounds like a plan, or u have any other thorts, please post them
cheers guys!

 

[nicklad]

i figured it out!!!
it had NOTHING to do with my SQL code, my database code or my Access DB, it had something to do with the way i ws loading 2 text files into 2 labels.

the code i had that was screwing with my database was:

Open StatID for input as #1
lblStatID.Caption = Input(LOF(1), 1)
Close #1

Open ProgID for input as #2
lblProgID.Caption = Input(LOF(2), 2)
Close #2


I changed this format to the following:


Open StatID For Input As #1
Line Input #1, Data
lblStatID.Caption = lblStatID.Caption & Data
Close #1

Open ProgID For Input As #2
Line Input #2, Data
lblProgID.Caption = lblProgID.Caption & Data
Close #2


and now it works!
i dont know why this is, but it works so im happy