HELP! This just doesnt make any sense!!!

[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

 

[MarkSweetland]

You'll have to check to ensure the recordset is not empty before you attempt to use the contents of a column:


If Not (rs.EOF and rs.BOF) Then
If rs("STAccess&quot Then
optStat.Visible = True
lblNoSTAccess.Visible = False
Else
optStat.Visible = False
lblNoSTAccess.Visible = True
End If
Else
MsgBox "Not a valid StationID"
End If



Mark The law, in its majestic equality, forbids the rich, as well as the poor, to sleep under the bridges, to beg in the streets, and to steal bread.

Anatole France

 

[nicklad]

i ran this code thru debugging mode, and it seems to skip everything from If Not (rs.EOF And rs.BOF) Then to
Else
MsgBox "Not a valid StationID", does this mean that my recordset is empty? and if it is, how can i fix this?

 

[MarkSweetland]

Your recordset is empty if both BOF and EOF are true. The StationID you're looking for in the table is the value read in from the login.riw file. A lot of times there are additional spaces on the same line in the text file that are also read in to a variable, or in your case the caption property of the lable.

Unlike the Input # statement, the Input function returns all of the characters it reads, including commas, carriage returns, linefeeds, quotation marks, and leading spaces(Especially since your using the LOF() function).

If you're sure the file contains a valid ID, try the Trim() function, although I'm not sure if Trim() will remove carriage returns or not:

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

If there are carriage returns that still affect the text try the Input# method:

Dim strStatID as String
Open App.Path & "\login.riw" For Input As #1
Do While Not EOF(1)
Input #1, strStatID
lblStatID.Caption = Trim(strStatID)
Loop


Mark
The law, in its majestic equality, forbids the rich, as well as the poor, to sleep under the bridges, to beg in the streets, and to steal bread.

Anatole France