Item cannot be found in the collection 1

[nickjar2]

full error msg:
Item cannot be found in the collection corresponding to the requested name or ordinal

Here is the code I have. I am using VBa as front end, with an Oracle db. I am using ADO and OLEDB:

Dim sFaults As String

On Error GoTo selectERR

sFaults = "SELECT Visit_ID, LoggerNo, MPRN, FaultDetails, AF_Meter_Reading, "
sFaults = sFaults & "AF_Logger_Reading, AL_Meter_Reading, AL_Logger_Reading, "
sFaults = sFaults & "fault_Maint, Maint_Date, Done_By FROM meterFAULTS where "
sFaults = sFaults & " MPRN = " & MPRN & " ORDER BY "
sFaults = sFaults & "Maint_Date DESC"


rstFaults.Open sFaults, conn, adOpenKeyset

Set Forms!frmLoggerMaint!frmLogMaintSUBF.Form.Recordset = rstFaults

I have subsituted the recordset for a DAO recset, using a local table and it worked fine. I also have an identical piece of code in another form, which works fine.
Anyone got any suggestions??? Why douesn't it like this ADO recordset??

Cheers! Nick (Everton Rool OK!)

 

[Paddyo]

Check your field names in the table which is not working. This error usually refers to mismatched field names. If it is working in the local table it suggests that the other table has an incorrect field name.

Hope this helps

 

[nickjar2]

Cheers for responding. I have checked the field names and all seems to be OK. Nick (Everton Rool OK!)

 

[nickjar2]

Anyone any more ideas?? Please? This is something that I just can't workout.

Cheers! Nick (Everton Rool OK!)

 

[TrojanRabbit]

I find, sometimes, that Access prefers one method of referring to a form over another. Sometimes, it like the reference method you're using for the form. Sometimes, it prefers another method:
Form_frmLoggerMaint.frmLogMaintSUBF.Form.Recordset
Something you can try.

 

[cmmrfrds]

At what point are you getting the error?

rstFaults.Open sFaults, conn, adOpenKeyset

Do you know at this point that a recordset was returned? Did you check for rstFaults.EOF?

Set Forms!frmLoggerMaint!frmLogMaintSUBF.Form.Recordset = rstFaults

 

[Steve101]

Nick,

(a) Following cmmrflds comments, it would help to know exactly where the error is occuring, and how you are declaring the various variables.

(b) Have you tried debugging the code and using breakpoints to identify exactly which item in the collection cannot be found?

(c) Totally unrelated to your problem, but a suggestion on defining your SQL SELECT string. Instead of using the multi statement string concatenation approach, I do it this way:

sFaults= "SELECT Visit_ID, LoggerNo, MPRN, " & _
" FaultDetails, AF_Meter_Reading, " & _
" AF_Logger_Reading, AL_Meter_Reading, " & _
" AL_Logger_Reading, " & _
" fault_Maint, Maint_Date, Done_By " & _
"FROM meterFAULTS " & _
"WHERE MPRN = " & MPRN & _
"ORDER BY Maint_Date DESC"

Use of the line continuation character (_) means that you can lay out your select string to be more legible.

(d) And another point comes to mind as I write this. Oracle field names MAY be case sensitive. Check this out; what my be happening is that you're referring to a field in your Select statement for example in uppercase, but its defined in lower case in Oracle, or visa versa;

Hope this helps,
Cheers,
Steve

 

[nickjar2]

Thanks to everyone for responding. I eventually found the problem. I believe that when using VB/Oracle you need to use a client side cursor. I left that blank, so i assume that it defaults to server side, and that is what the problem was.
All day yesterday i struggled with this, and then i come in first thing and it clicks. We live and learn.
Cheers again! Nick (Everton Rool OK!)