Run-time error '3265'

[santanudas]

Hi all,

I've a simple code to show the result based on a SQL quary:

Private Sub cmdRun_Click()
Dim dbs As DAO.Database
Dim SQLStmt As String, trs As DAO.Recordset

Set dbs = OpenDatabase("E:\My Documents\CSSC_booking\booking.mdb&quot
SQLStmt = "SELECT [status] FROM [tblBooking] WHERE [tblBooking].[room] = " & cmbRoom.Value & ";"
'MsgBox SQLStmt
Set trs = CurrentDb.OpenRecordset(SQLStmt)
' , dbOpenDynaset)

If Not (trs.EOF And trs.BOF) Then
trs.MoveLast
trs.MoveFirst

Do While Not trs.EOF
MsgBox trs.Fields(8)
trs.MoveNext
Loop

End If
End Sub

Here the fiels 'room' is numeric. It's giving a run-time error: Run-time error '3265'
Item not found in this collection

Can any one help me please.
Thanks in advance.

Santanu

 

[BillPower]

Hi santanudas,

Try changing this line:
SQLStmt = "SELECT [status] FROM [tblBooking] WHERE [tblBooking].[room] = " & cmbRoom.Value & ";"

To:
SQLStmt = "SELECT [status] FROM [tblBooking] WHERE room = " & Me!cmbRoom & ";"

In VBA you don't need the Value property when referring to a control directly or in an SQL Statement.

Bill

 

[santanudas]

Hi billpower,

Thank you very much for you help. But unfortunately still no joy; the same error. I tried using

SQLStmt = "SELECT [status] FROM [tblBooking] WHERE [tblBooking].[timing] = '" & Me.cmbTiming & "';"

in addition but no different result. Any idea?

Regards,
Santanu

 

[CajunCenturion]

On which specific statement is the error being generated?

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

 

[BillPower]

Sorry, I just looked at the SQL statement, not the code. Will try to be a bit more observant in future.

Change this:
Set trs = CurrentDb.OpenRecordset(SQLStmt)

To:
Set trs = dbs.OpenRecordset(SQLStmt)

=============================
Change this:
MsgBox trs.Fields(8)

To:
MsgBox trs.Fields("TheFieldName&quot

Bill

 

[CajunCenturion]

I suspect the error is coming from the MsgBox trs.Fields(8) statement.

The Fields collection of the recordset may only contain the same number of elements as number if items retrieved by the Select clause of the SQL statement. In this case, only one item is being selected (status), therefore the Fields collection should only contain one element. That is why the MsgBox trs.Fields(8) is generating the error, because there is no eighth element in the collection.

Several things you can do. Firstly, you can provide some degree of range checking by using the .Count property of the collection. (MsgBox trs.Fields.Count). Secondly, you might try referencing the individual field collection elements using its key rather than index. The key should be the column name that applies. (MsgBox trs.Fields(“status”)

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

 

[santanudas]

Thank you very much both of you. it’s working fine now. But another small problem. I’ve modified the ‘IF-END IF’ section a bit and now it’s like this:

If Not (trs.EOF And trs.BOF) Then
trs.MoveLast
trs.MoveFirst
If trs.Fields("status&quot = "N" Then
MsgBox "Sorry, already booked!!!"
Else
MsgBox "Good Luck!!!"
End If
End If

After showing the result, what ever may be “already booked!!!" or "Good Luck!!!" it just replacing the value of the first row of the table with the value it’s searching for.
I really appreciate if you guys please try a bit to fix this. Thank you both of you once again for making first query working.

Santanu