Puzzling error!

[krill]

Hi chaps!

Got a problem that can't seem to put my finger on!

VB app running on numerous OS. Within the app I have a child form thatcomes up and populates a series of list boxes, using a ADO recordset from a access97 database. On my development machines I have NO grief (As usual!!)but on "some" machine sI get this error when the form loads (I believe that there is a problem with poulating the recorsets).

"item cannot be found in the collection corresponding to the requested name or ordinal".

I have optain a copy of there database and can run it successfully on my development machine. What I'm I missing Any ideas chaps!!


Ta

Gerard

 

[minoad]

How are you connecting? Can you show the string and the sql command text?

 

[krill]

Here is the whole procedure..basically it is very much the same right though:


/////////////////////////////The Procedure//////////////////

Private Sub popcombo()
' Comments :
' Parameters: -
' Modified :
'
' --------------------------------------------------

Dim AdOrg As ADODB.Recordset
Dim AdWard As ADODB.Recordset
Dim AdUnit As ADODB.Recordset
Dim AdDev As ADODB.Recordset
Dim AdSur As ADODB.Recordset
Dim AdSurg As ADODB.Recordset
Dim AdConsult As ADODB.Recordset
Dim AdClass As ADODB.Recordset
Dim AdSpec As ADODB.Recordset
Dim AdAcq As ADODB.Recordset
Dim AdMRO As ADODB.Recordset
Dim AdSSI As ADODB.Recordset
Dim AdOther As ADODB.Recordset
Dim AdJob As ADODB.Recordset
Dim AdLoc As ADODB.Recordset
Dim AdOT As ADODB.Recordset
Dim Adpath As ADODB.Recordset
Dim AdSharp As ADODB.Recordset

Dim strHosp As String
Dim strWard As String
Dim strUnit As String
Dim strDev As String
Dim strSur As String
Dim strSurg As String
Dim strConsult As String
Dim strClass As String
Dim strSpec As String
Dim strAcq As String
Dim strOther As String
Dim strJob As String
Dim strLoc As String
Dim strOT As String
Dim strPath As String

Dim strSharp As String

' Comments :
' Parameters: -
' Modified :
'
' --------------------------------------------------
'----Hospital----
'eICATerror ErrorEnablerStart
On Error GoTo PROC_ERR
'eICATerror ErrorEnablerEnd

listHosp(0).Clear
listHosp(1).Clear
strHosp = "SELECT * FROM [HospCodes] Order by Header1"

With MainMenu.AdHosp
.ConnectionString = cndata
.CommandType = adCmdText
.EOFAction = adDoMoveLast
.RecordSource = strHosp
.Refresh
End With



listHosp(0).AddItem "All"
Do While Not MainMenu.AdHosp.Recordset.EOF
listHosp(0).AddItem MainMenu.AdHosp.Recordset.Fields("Header1&quot
listHosp(0).ItemData(listHosp(0).NewIndex) = MainMenu.AdHosp.Recordset.Fields("ID&quot
MainMenu.AdHosp.Recordset.MoveNext
Loop

'===Job List====
listJob(0).Clear
listJob(1).Clear
Set AdJob = cndata.Execute("SELECT * FROM ListStaffCategory WHERE Module = 7 order by StaffCat&quot

listJob(0).AddItem "All"
Do While Not AdJob.EOF
listJob(0).AddItem AdJob.Fields("StaffCat&quot
listJob(0).ItemData(listJob(0).NewIndex) = AdJob.Fields("StaffCatNo&quot
AdJob.MoveNext
Loop
'===Sharp List====
ListSharp(0).Clear
ListSharp(1).Clear
Set AdSharp = cndata.Execute("SELECT * FROM ListPercDevice order by Device&quot

ListSharp(0).AddItem "All"
Do While Not AdSharp.EOF
ListSharp(0).AddItem AdSharp.Fields("Device&quot
ListSharp(0).ItemData(ListSharp(0).NewIndex) = AdSharp.Fields("Code&quot
AdSharp.MoveNext
Loop

'===OT List====
ListOT(0).Clear
ListOT(1).Clear
Set AdOT = cndata.Execute("SELECT * FROM ListExpLocationOT order by OTLocation&quot

ListOT(0).AddItem "All"
Do While Not AdOT.EOF
ListOT(0).AddItem AdOT.Fields("OTLocation&quot
ListOT(0).ItemData(ListOT(0).NewIndex) = AdOT.Fields("Code&quot
AdOT.MoveNext
Loop
'===Path List====
ListPath(0).Clear
ListPath(1).Clear
Set Adpath = cndata.Execute("SELECT * FROM ListExpLocationPath order by PathLocation&quot

ListPath(0).AddItem "All"
Do While Not Adpath.EOF
ListPath(0).AddItem Adpath.Fields("PathLocation&quot
ListPath(0).ItemData(ListPath(0).NewIndex) = Adpath.Fields("Code&quot
Adpath.MoveNext
Loop
'===Location List====
ListLoc(0).Clear
ListLoc(1).Clear
Set AdLoc = cndata.Execute("SELECT * FROM ListExpLocation order by Location&quot

ListLoc(0).AddItem "All"
Do While Not AdLoc.EOF
ListLoc(0).AddItem AdLoc.Fields("Location&quot
ListLoc(0).ItemData(ListLoc(0).NewIndex) = AdLoc.Fields("Code&quot
AdLoc.MoveNext
Loop

ListAcq(0).Clear
ListAcq(1).Clear
Set AdAcq = cndata.Execute("SELECT * FROM ListAcquisition WHERE mid([Mod],1,4) = 'MRO1' order by Acquisition&quot

ListAcq(0).AddItem "All"
Do While Not AdAcq.EOF
ListAcq(0).AddItem AdAcq.Fields("Acquisition&quot
ListAcq(0).ItemData(ListAcq(0).NewIndex) = AdAcq.Fields("AcquisitionCode&quot
AdAcq.MoveNext
Loop


'----Ward--------
listWard(0).Clear
listWard(1).Clear
Set AdWard = cndata.Execute("SELECT * FROM [ListWard] Order by Ward&quot

listWard(0).AddItem "All"
Do While Not AdWard.EOF
listWard(0).AddItem AdWard.Fields("Ward&quot
listWard(0).ItemData(listWard(0).NewIndex) = AdWard.Fields("Wardcode&quot
AdWard.MoveNext
Loop



'----Unit--------
ListUnit(0).Clear
ListUnit(1).Clear
Set AdUnit = cndata.Execute("SELECT * FROM [ListUnit] Order by Unit&quot

ListUnit(0).AddItem "All"
Do While Not AdUnit.EOF
ListUnit(0).AddItem AdUnit.Fields("Unit&quot
ListUnit(0).ItemData(ListUnit(0).NewIndex) = AdUnit.Fields("Unitcode&quot
AdUnit.MoveNext
Loop

'----Device--------
ListDev(0).Clear
ListDev(1).Clear
Set AdDev = cndata.Execute("SELECT * FROM [ListDevice] Order by DeviceDes&quot


ListDev(0).AddItem "All"
Do While Not AdDev.EOF
ListDev(0).AddItem AdDev.Fields("DeviceDes&quot
ListDev(0).ItemData(ListDev(0).NewIndex) = AdDev.Fields("Code&quot
AdDev.MoveNext
Loop


'----Surveillance--------
ListSur(0).Clear
ListSur(1).Clear
Set AdSur = cndata.Execute("SELECT * FROM [ListSurveillanceType] Order by Surveillance&quot


ListSur(0).AddItem "All"
Do While Not AdSur.EOF
ListSur(0).AddItem AdSur.Fields("Surveillance&quot
ListSur(0).ItemData(ListSur(0).NewIndex) = AdSur.Fields("SurveillanceCode&quot
AdSur.MoveNext
Loop

'----Infection Type--------
ListSSI(0).Clear
ListSSI(1).Clear
Set AdSSI = cndata.Execute("SELECT * FROM [ListSSI] Order by SSItype&quot


ListSSI(0).AddItem "All"
Do While Not AdSSI.EOF
ListSSI(0).AddItem AdSSI.Fields("SSItype&quot
ListSSI(0).ItemData(ListSSI(0).NewIndex) = AdSSI.Fields("SSItypeCode&quot
AdSSI.MoveNext
Loop
'----Surgeon--------
listSurg(0).Clear
listSurg(1).Clear
Set AdSurg = cndata.Execute("SELECT Distinct StaffName, id FROM [ListStaff] order by StaffName&quot


listSurg(0).AddItem "All"
Do While Not AdSurg.EOF
listSurg(0).AddItem AdSurg.Fields("StaffName&quot
listSurg(0).ItemData(listSurg(0).NewIndex) = AdSurg.Fields("id&quot
AdSurg.MoveNext
Loop

'----Consultant--------
ListConsult(0).Clear
ListConsult(1).Clear
AdSurg.MoveFirst
If Not AdSurg.EOF Then

'AdSurg.MoveFirst
End If
ListConsult(0).AddItem "All"
Do While Not AdSurg.EOF
ListConsult(0).AddItem AdSurg.Fields("StaffName&quot
ListConsult(0).ItemData(ListConsult(0).NewIndex) = AdSurg.Fields("id&quot
AdSurg.MoveNext
Loop
'----Class--------
ListClass(0).Clear
ListClass(1).Clear
Set AdClass = cndata.Execute("SELECT * FROM [ListClass] order by ClassDescription&quot

ListClass(0).AddItem "All"
Do While Not AdClass.EOF
ListClass(0).AddItem AdClass.Fields("ClassDescription&quot
ListClass(0).ItemData(ListClass(0).NewIndex) = AdClass.Fields("Class&quot
AdClass.MoveNext
Loop


'----Speciality--------
ListSpec(0).Clear
ListSpec(1).Clear
Set AdSpec = cndata.Execute("SELECT * FROM [ListSpecialty tbl] order by Specialty&quot

ListSpec(0).AddItem "All"
Do While Not AdSpec.EOF
ListSpec(0).AddItem AdSpec.Fields("Specialty&quot
ListSpec(0).ItemData(ListSpec(0).NewIndex) = AdSpec.Fields("SpecCode&quot
AdSpec.MoveNext
Loop

'---Organism-----

Set AdOrg = cndata.Execute("Select Organism, OrganismCode from ListOrganism order by Organism&quot

ListOrg(0).AddItem "All"
Do While Not AdOrg.EOF
ListOrg(0).AddItem AdOrg.Fields("Organism&quot
ListOrg(0).ItemData(ListOrg(0).NewIndex) = AdOrg.Fields("OrganismCode&quot
AdOrg.MoveNext
Loop

'---MRO Organsim----
Set AdMRO = cndata.Execute("Select * from MROCodes order by MRO&quot

ListMRO(0).AddItem "All"
Do While Not AdMRO.EOF
ListMRO(0).AddItem AdMRO.Fields("MRO&quot
ListMRO(0).ItemData(ListMRO(0).NewIndex) = AdMRO.Fields("MROCode&quot
AdMRO.MoveNext
Loop

'---MRO Organsim Other----
Set AdOther = cndata.Execute("SELECT * FROM [ListOrganism] where SO = true Order by Organism&quot

ListOther(0).AddItem "All"
Do While Not AdOther.EOF
ListOther(0).AddItem AdOther.Fields("Organism&quot
ListOther(0).ItemData(ListOther(0).NewIndex) = AdOther.Fields("OrganismCode&quot
AdOther.MoveNext
Loop


'Destory objects
AdAcq.Close
Set AdAcq = Nothing

AdJob.Close
Set AdJob = Nothing

Adpath.Close
Set Adpath = Nothing

AdOT.Close
Set AdOT = Nothing

AdLoc.Close
Set AdLoc = Nothing

AdSharp.Close
Set AdSharp = Nothing

AdMRO.Close
Set AdMRO = Nothing

AdOrg.Close
Set AdOrg = Nothing

AdWard.Close
Set AdWard = Nothing

AdUnit.Close
Set AdUnit = Nothing

AdDev.Close
Set AdDev = Nothing

AdClass.Close
Set AdClass = Nothing

AdSpec.Close
Set AdSpec = Nothing

AdSur.Close
Set AdSur = Nothing

AdSurg.Close
Set AdSurg = Nothing


MainMenu.AdHosp.Recordset.Close


'eICATerror ErrorHandlerStart
PROC_EXIT:
Exit Sub
PROC_ERR:
If Err.Number = 94 Then
Beep
MsgBox "There is a missing value in your database, please contact the administrator.", _
vbExclamation + vbOKOnly, _
"Data Error"
ElseIf Err.Number = -2147217904 Then

Resume Next
ElseIf Err.Number = 91 Then
Exit Sub
Else
MsgBox Err.Description
End If
Resume PROC_EXIT
'eICATerror ErrorHandlerEnd

End Sub

 

[MichaelRed]

Is this for REAL?

You could (apparently) just use a single recordset, re-assigning the SQL string for each of the pairs of controls!

I would consider placing the Sql statements and the control names in a table / recordset and just reading the querry and the controls from the recordset.

That could collapse the ~ 300 lines of declarations & code to mabe 40. That would be -IMHO- a good place to start!

Deleting the 20 (or so) Declared strings which are NOT used would at least shorten the verbage, as would the declaration of the (20 or so?) seperate ADODB recordsets. After all, each is only used to populate the associated control and they are discarded afterwards -WHY do they need new seperate distinct names? ESPECIALLY since they ALL remain until the procedure is done?

I could EASILY see this as a problem on some systems - simply due to memory limitations!

Overall "UGH". Clean it UP! If you still have problems, ask again!

MichaelRed
m.red@att.net

There is never time to do it right but there is always time to do it over