Visual Basic :: Item Not Found In This Collection , Any Clue Please?

Item Not Found In Collection Or Named Ordinal
I think is an easy one for experts out there.
I'm tring to ad one assignment inside of Do While Not rsJobTranLabor.EOF Loop... Which tells me sme records are in th erecordset since it goes in th loop and also I see the others fields of the records go into the flexgrid when this one statement is not there.
I am trying to assign rsJobTranLabor!JobTran into a flex grid and I'm getting errors as if it's not finding the field. The select statment is below and the two statements I have tried are also below. I guess my symtax is wrong but I don't know wat to change it to.
Thanks for the help!

grdTime.TextMatrix(GridRow, 11) = rsJobTranLabor!JCTRAN_JobTranLabor!JobTran

grdTime.TextMatrix(GridRow, 11) = rsJobTranLabor!JobTran

strSQL = "Select TranYMD, WorkOrderNbr, DLHours, DLRate, ExtCost, JCTRAN_JobTranLabor.JobTran "
strSQL = strSQL & "As JCTran, DLRateType From JCTRAN_JobTranLabor "
strSQL = strSQL & "Inner Join JCTRAN_JobTranHdrs On JCTRAN_JobTranLabor.JobTran = JCTRAN_JobTranHdrs.JobTran "
strSQL = strSQL & "Where EmployeeID = '" & EmpID10 & "' "
strSQL = strSQL & "And TranYMD >= '" & gWeek(0).DateYMD & "' "
strSQL = strSQL & "And TranYMD <= '" & gWeek(6).DateYMD & "' "
strSQL = strSQL & "Order By TranYMD"
intTmp1 = DS0_OpenForwardOnly(dbJCTran, strSQL, rsJobTranLabor)

Item Not Found In The Collection Corresponding To Requested Name Or Ordinal
Hello code knights
I'm getting the above error with this database app. There is only a listbox and a textbox. I'm using Access 2k and MS Active X data obj 2.7.
Code:
Option Explicit
Private strMvFilePathName As String
Private basicConn As ADODB.Connection
Private rsMvRecordset As ADODB.Recordset
Dim mboolShow As Boolean
Dim mboolAdding As Boolean
'
Private Sub Form_Load()
' Set the location for the mdb
    strMvFilePathName = App.Path & "asic.mdb"

' Create a connection to the database
    Set basicConn = New ADODB.Connection
    basicConn.Open ("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & strMvFilePathName & ";Persist Security Info=False")

' to display from record set
    Set rsMvRecordset = New ADODB.Recordset
    With rsMvRecordset
' Attach recordset to connection
        .ActiveConnection = basicConn
' Make sure the data will be Updatable
        .CursorType = adOpenDynamic
        .CursorLocation = adUseClient
' Open recordset using SQL SELECT statement to select records
        .Open ("SELECT ID, Name FROM tblbasic")
    End With

    Call FormShow

End Sub

Private Sub FormShow()
   mboolShow = True
   With rsMvRecordset
      txtAge = Field2Str(!Age) ' error here
   End With
   mboolShow = False
End Sub

Public Function Field2Str(vntField As Variant) As String
   If IsNull(vntField) Then
      Field2Str = ""
   Else
      Field2Str = Trim$(CStr(vntField))
   End If
End Function

Public Function Field2Long(vntField As Variant) As Long
   If IsNull(vntField) Then
      Field2Long = -1
   Else
      Field2Long = CLng(vntField)
   End If
End Function

Private Sub ListLoad()
   Dim oRS As Recordset
   Dim strSQL As String

   strSQL = "SELECT ID, Name FROM tblbasic"

   Set oRS = New Recordset
   oRS.Open strSQL, basicConn, adOpenForwardOnly, adLockReadOnly

   lstNames.Clear
   Do Until oRS.EOF
      With lstNames
         .AddItem Trim$(oRS("Name"))
         .ItemData(.NewIndex) = oRS("ID")
      End With

      oRS.MoveNext
   Loop
   oRS.Close

   If lstNames.ListCount Then
      lstNames.ListIndex = 0
   End If
End Sub

..the Field2String and Field2long are in case an empty field is encountered. My Access has just two fields ID and Age. More to be added as the current crisis ceases.

Hal

"He knows so little it's hard to keep up with what he doesn't know". -- Hawkeye Pierce on Frank Burns

Item Cannot Be Found In The Collection Corresponding To The Requested Name Or Ordinal
Hello
This was working fine then this error pops up when I try to add a record "Item cannot be found in the collection corresponding to the requested name or ordinal"
During the save routine this pops up. Even earlier versions of this app that were working, are now doing this, just bizarre. I'm using VB 6 as a front end to Access '03. The main control is mschart.
Here is my save routine...
Code:
Private Sub cmdSave_Click()
  Dim mySQL As String
  On Error GoTo UpdateErr
    With rs
      .AddNew
      '!Weeks = Format(DTPicker1.Year) & "/" & DTPicker1.Month & "/" & DTPicker1.Day ' or here
      !Weeks = DTPicker1.Value ‘ error here
      !Analyzer = Analyzer
      !Humphrey = Humphrey
      !Accumulator = Accumulator
      !Pump = Pump
      !Regulator = Regulator
      !Card = Card
      !NDF = NDF
      !Solenoid = Solenoid
      .MovePrevious
    End With
  'insert into table
  mySQL = "INSERT INTO tbliface([Weeks], Analyzer, Humphrey, Accumulator, Pump, Regulator, Card, NDF,Solenoid) VALUES('" & DTPicker1.DataField & "');"
    Debug.Print mySQL
  cboStartLoad ' load combobox for start date range to display on chart
  cboEndLoad ' load combobox for end date range to display on chart
  Exit Sub
  'mbDataChanged = False
  FrameAdd.Visible = False
UpdateErr:
  MsgBox Err.Description
End Sub

I've tried every variation of the error line month, day year etc.
Any help is appreciated.

IM CONFUSED...ERROR 3265: ITEM CANNOT BE FOUND IN THE COLLECTION
Hello everyone...

i have this problem which bugging me yesterday for the whole day.

here it is...i have a database with a field name "contractor_id", but when i try to load the details or
even save a data into that field it displays an error message "3265: Item cannot be found in the collection
corresponding to the requested name or ordinal."

wherein, i have checked the existing table where that field resides and its there, so far only that field is
having a problem but the other fields along with it are working fine, i even checked by code if i have only
mispelled the field name but its correct.

need you help very badly...thanks a lot.

vhin

How To Search Listbox For Item If Item Not Found Add Item?
Hi, lol man I cant find nothing today when I search the forum.

This my latest problem, How do I search a listbox for an item lets say "Game Over" And if the item is found the program does nothing, but if the item is not found it will add "Game Over"

Thanks Again

Error In Collection Whenever Delete One Item From Collection!!!
Hii everybody,

I am trying to remove one of the item from the collection, any item not like first or last or in sequence. LIke i have collection of 4 items and i removed 2 second item. After that i am trying to add new with new Key value whcih for this example is 5(In sequence). Then it generates error that "subscript out of range." I know wehn i am removing item, collection counter is decresing. But i did not get why it is giving error?

is it possible to remove item randomly from collection and then add new items with new key during run time?

Please enybody can give me small help or reference to example?

Thanks!!!

Hdave

Any Clue How To Get GetElementById("Item").Enabled?
Attn: moderators - this is a VB6 question about the DHTML DOM do not move to the .NET section

All,

I am trying to determine if a button from my VB.NET web application has a disabled button or not, but it keeps erroring on this line:

VB Code:
Private Sub WebBrowser1_NavigateComplete2(ByVal pDisp As Object, URL As Variant) On Error GoTo Err Dim blnNextEnabled As Boolean ' blnNextEnabled = False blnNextEnabled = WebBrowser1.Document.getElementById("btnNext").Enabled ' Error here! ' RaiseEvent NavigateComplete(blnNextEnabled) ' Exit Sub 'Err: Resume NextEnd Sub

It just drops to my error handler if I try to access the .Enabled Property. Any clues?

Thanks!

Dave

Any Clue How To Get GetElementById("Item").Enabled?
All,

I am trying to determine if a button from my VB.NET web application has a disabled button or not, but it keeps erroring on this line:

VB Code:
Private Sub WebBrowser1_NavigateComplete2(ByVal pDisp As Object, URL As Variant) On Error GoTo Err Dim blnNextEnabled As Boolean ' blnNextEnabled = False [color=DarkOrange]blnNextEnabled = WebBrowser1.Document.getElementById("btnNext").Enabled[/color] ' RaiseEvent NavigateComplete(blnNextEnabled) ' Exit Sub 'Err: Resume NextEnd Sub

It just drops to my error handler if I try to access the .Enabled Property. Any clues?

Thanks!

Dave

3265 Items Cannot Be Found In Collection - Sometimes Works?
Stumped on this one. I keep getting the 3265 error "Item cannot be found in the collection".

What is weird is that all my other executions of the stored proc work except for this one.

cn.Open "Provider=sqloledb;Data Source=LAPTOP;Initial Catalog=Production;User Id=sa;Password=sa;"

' Get the next key
SQL = "spGetNextKey('grp')"
Trace (SQL)
Rs.CursorType = adOpenKeyset
Rs.LockType = adLockOptimistic
Rs.Open SQL, cn, , , adCmdStoredProc
grpId = Rs("keyId")
Rs.Close

it bombs on the grpdId = rs("keyId") line. 3265 item cannot be found in the collection

the weird thing is that if I execute this code:

' Load the group type combo
SQL = "spGetType(12)"
Set Rs = cn.Execute(SQL)
frmMain.lstgrpType.Clear
Do While Not Rs.EOF
frmMain.lstgrpType.AddItem Rs("typTypeDesc")
frmMain.lstgrpType.ItemData(frmMain.lstgrpType.NewIndex) = Rs("typTypeId")
Rs.MoveNext
Loop
Rs.Close

it works fine.

The only difference is that the second (the one that works) returns values right from the table itself.
But the first (the one that does not work) calcs a value then returns it.

I'm finding bits and pieces about CAT? on the net but not sure what it does or if it'll help.

----------------------

Update... I got it to work by first returning the table value, then updated my next count, blah, blah.

Anyways it has got something to do with this 'catalog' value in ADO.. what does it do? WIll I be able to create stored procs that do strict calculations (no table stuff) and get the value back?

Type Of Scan : If Object Is Found In A Collection Then Do Something
You see it all the time when you're running an antivirus, spyware, trojan scan. The program is scanning your harddrive for something. I believe it can be broken down into a logical sequence:

If an object (compared to a data collection) is found, then do something.

So, say for example, you have the word "Raistlin" in an array (or list box, which ever). Now when you click, say, "Scan" it will scan a 'collection' of data for the word "Raistlin" and then it will display, say, a message box saying "Raistlin was found, get a cleric" (hehe )

Now from my standpoint it's pretty easy to comprehend. I mean, it seems pretty easy enough to do.. However, I guess I'm thinking in more 2 dimensional, because when I try to write the code down in VB it doesn't seem to do exactly what I have planned out in my head.

For the most part, I've gotten away with 'instr' but it merely searches strings.. I want to compare a word (Raistlin) to a large collection of data (say maybe 200 arrays) and work rather quickly.

All I really need is an idea where to look for this information. I can figure it out the rest later.. However, I can't start what I want to do without really knowing where to look for it, Know what I mean?

Any help is appreciated! Thanks

Getting A Collection Item From One Collection To Another
Hi,

Okay, I've spent 2 days trying to figure this out. I'm obviously missing something because it seems it should be easy .

I have 2 Collections and want to get an item from one to the other. I have a function for adding an item and tried to write a function for getting an item and returning it to the add function. This didn't go over well. I've also tried to write a copy function but didn't have success with that either.

Anyone know how I should be trying to go about this.

Thanks in advance

kjf

How To Get The Key Of A Collection Item.
I have a collection of data that I access in different ways.
Assuming that the collection is called Items, and each item is called Item, and finally the key is called Key
1. Direct acces to an item using:

Code:
Item = Items(Key)
2. Loop through all Item in Items:

Code:
for each Item in Items
Next Item

Now I know that I could define my own collection of data elements holding the Key value as part of its data, however I was trying to see if I could somehow get the Key value out of an Item in the collection.

Any ideas?

How To Get The Key Of A Collection Item?
I'm using the code below to find an item in my collection. How can I get its key argument?

Code:
Dim pTmpLayer As ILayer
Dim i As Integer
For i = 1 To m_pLayers.Count
Set pTmpLayer = m_pLayers.Item(i)
If pTmpLayer Is pSearchedLayer Then
m_pLayers.Remove i

'I need to know the key of the item I've removed from the collection...
'Any Idea?

Exit For
End If
Next

Item Cannot Be Found
Hi,

I keep getting the following error when I get to the second line of the code beneath...
Item cannot be found in the collection corresponding to the requested name or ordinal.

sSQL = "EXEC sp_SaveCapex " params listed.
Set rsProposalNo = g_Conn.Execute(sSQL)
g_iProposalNo = rsProposalNo.Fields("Record")

CREATE PROCEDURE sp_SaveCapex
(
    Params
)
AS
INSERT INTO CAPEX
(
        Columns listed
)
VALUES
(
    Values listed
)

SELECT @@identity AS Record
GO

The insert works fine and if I run the stored proc in query analyzer, it returns the value I require. However, when I run it in VB I get the error.

Anyone know what's going on?

Thanks for any help,
Robin

Item Can't Be Found
Hi you all... I trust today finds you healthy and happy. I have an application that seems to be giving me a bit of trouble. I hope someone can help me out.

Code:
Private Sub cmdTask_Click()

    Dim adoCon As ADODB.Connection
    Dim rs As ADODB.Recordset
    Dim sSQL As String
    Dim strPath As String


    Set adoCon = New ADODB.Connection
    Set rs = New ADODB.Recordset

    strPath = "C:Program FilesCommon FilesLibrary"

    With adoCon
        .Provider = "Microsoft.jet.oledb.4.0"
        .ConnectionString = "Data source=" & strPath & "Library.mdb"
        .Open
    End With

    If cmdTask.Caption = "&Submit" Then
        sSQL = ""
        sSQL = sSQL & " INSERT INTO Library (Name_of_Authoror, Title_of_Book,"
        sSQL = sSQL & " Date_of_Publication, Name_of_Publisher)"
        sSQL = sSQL & " Values ('" & cmbAuth.Text & "', '" & cmbTitle.Text & "',"
        sSQL = sSQL & " '" & txtDate.Text & "', '" & txtPub.Text & "')"

        adoCon.Execute sSQL

        cmbAuth.Text = ""
        cmbTitle.Text = ""
        txtDate.Text = ""
        txtPub.Text = ""

    ElseIf cmdTask.Caption = "Searc&h" Then
        If optTitle.Value = True Then
            sSQL = ""
            sSQL = sSQL & " SELECT Title_of_Book, Date_of_Publication, "
            sSQL = sSQL & " Name_of_Publisher"
            sSQL = sSQL & " FROM Library"
            sSQL = sSQL & " WHERE Name_of_Author = '" & cmbAuth.Text & "' "
            sSQL = sSQL & " Order By Name_of_Author Desc"
        ElseIf optAuth.Value = True Then
            sSQL = ""
            sSQL = sSQL & " SELECT Name_of_Author, Date_of_Publication, "
            sSQL = sSQL & " Name_of_Publisher"
            sSQL = SsSQL & " FROM Library "
            sSQL = sSQL & " WHERE Title_of_Book = '" & cmbTitle.Text & "' "
            sSQL = sSQL & " ORDER BY Title_of_Book Desc"
        End If
    End If

    Debug.Print sSQL

    With rs
        .CursorType = adOpenStatic
        .CursorLocation = adUseClient
        .ActiveConnection = adoCon
        .Open sSQL
         Set .ActiveConnection = Nothing
    End With

    If rs.RecordCount = 0 Then
        If rs.EOF Then
            With frmBoblib
                .txtDate.Text = ""
                .txtPub.Text = ""
            End With
        End If

    ElseIf rs.RecordCount = 1 Then
        If Not rs.EOF Then
            With frmBoblib
+++++++++++++++++++++++++++++++++++++++++++++++
                .txtDate.Text = rs.Fields(Date_of_Publication).Value
+++++++++++++++++++++++++++++++++++++++++++++++
                .txtPub.Text = rs.Fields(Name_of_Publisher).Value
            End With
        End If

    ElseIf rs.RecordCount > 1 Then
        If Not rs.EOF Then
            With frmBoblib
                .txtDate.Text = ""
                .txtPub.Text = ""
            End With
        End If
    End If

    With rs
        .CursorType = adOpenStatic
        .CursorLocation = adUseClient
        .ActiveConnection = adoCon
         Set .ActiveConnection = Nothing
    End With

    End Sub

In the above code, when I run the search operation, I get the error, "Item cannot be found in the collection corresponding to the requested name or ordinal". The line erring seems to be the line I have marked with the "+" signs, but I cannot see anything wrong with that line. Can someone give me another look... another set of eyes never hurts.

Thank you so much for your help,

Happieman
a.k.a. Bill

Edited by - Happieman on 9/9/2005 11:43:12 AM

Checking For An Item In A Collection.
I used Thinkers Collection tutorial and made my own collection class.

Now, I'm adding "shifts" from work into the collection from a database. In the database, they have a unique shift number. But in the program they are displayed by date (for one employee at a time). It is possible to have two shifts on the same date, so when I'm adding the shifts to the collection, I cannot use the date as a key, because it's possible to have more then one with the same date. I do not want to use the unique shift number as the key, because I have no way to match the multiple dates that could exist, to the unique entries in the database.

So I figured I'd display any extra shifts in a day with a number after them:
May 4, 2006
May 4, 2006 (1)
etc...

I thought that when I assign the keys to the shifts, I could search the collection to see if that key already exists, and then create a new one if needed. Then when the user clicks on the details for the shift, the unique key will get the proper shift number, and retrieve the right details.

However, I cannot seem to find a way to see if the key already exists.

This is the code I already have....but it just overflows as the int gets too big. I figured if I set the key to something that didn't exist, then nothing would be returned, and I could catch that....but either that's not the case, or I'm checking wrong. Here is my code:

Code:
Dim shiftcount As Integer, keytoadd As String
'Check to see if this date already exists in the collection
keytoadd = ConvertYYYYMMDDtoLongDate(DbRecSet("date_in").value)
Set tempemployeeshift = employee_shifts.item(keytoadd)
shiftcount = 1
Do Until tempemployeeshift Is Nothing
Set tempemployeeshift = employee_shifts.item(ConvertYYYYMMDDtoLongDate(DbRecSet("date_in").value) & "(" & shiftcount & ")")
keytoadd = ConvertYYYYMMDDtoLongDate(DbRecSet("date_in").value) & "(" & shiftcount & ")"
shiftcount = shiftcount + 1
Loop

Here is the code of the item function of the tutorial:

Code:
Public Function Item(ByVal vKey As Variant) As EmployeeShift
' Variant allows for string key or long index
On Error GoTo ErrorHandler:
If Len(vKey) > 0 And Len(vKey) < 51 Then
Set Item = mcolEmployeeShifts.Item(vKey)
Else
Set Item = Nothing 'signal failure
Err.Raise vbObjectError + 10000, _
"EmployeeShiftCollection.EmployeeShifts", "Key value invalid"
End If
Exit Function
ErrorHandler:
If Err.Number = 5 Or Err.Number = 9 Then
Set Item = Nothing 'Item not found
Else
Err.Raise Err.Number, "EmployeeShiftsCollection.EmployeeShift", Err.Description
End If
End Function

Check For Item In Collection
Hi. I have a collection and all the items in it have keys specified. How can i check if there is an item using a key? Theres a possibility the app might try to add the same key twice. In this case i want to remove the old one first then add the new one.

Test Whether A Collection Contains An Item
I'd like to be able to test if a certain collection contains an item described by a string key, and if the item isn't there, to Add it.
How can I do this without causing an error?

Should We Clear Item In Collection
I have a question here.

Should we do this before terminate event fired?

VB Code:
Do While myCol.Count > 0Call myCol.Remove(1)LoopSet myCol = Nothing

or this is enough?
Set myCol = Nothing

And why so?

Added [RESOLVED] to thread title - Hack

? Collection Item Exists
Hi All,

I am trying to write a general function to test if a collection item exists. I have managed to do this for a specific collection, but i would like the function to be able to accept a passed collection.

for example,

itemExists(collection)

would return true or false.

Can you pass a collection ro a function, i was planning to return true or false from the function, but if the collection item i am trying to pass does not exist i get the error 'subscript out of range' before it is passed to the function.

i must be missing something simple.

please help

i want to be able to use the function in the following way

if (ItemExists(colABC(1))) then

or

if (ItemExists(colABC(1).colXYZ(2))) then

Simon

Item Collection Properties
HI, anybody knows how to make something like this???

collection.item(1).property1=10

I did the collection part, and it works, but I don't know the proerties part.

Thanks!!!

Searching For An Item In A Collection
Hi,

This is along the same lines as my search through a MultiDimensional Array that I've been trying to make work.

I've decided to try the collection route instead, so here is my thought

I have a series of existing items in two collections

VB Code:
Dim EmpColl As New Collection ' For total payroll hours for all 3 'weeksDim FNColl As New Collection ' For the ID number which is the 'unique key in the system

I need to be able to update the EmpColl as new weeks of the payroll are added, but I'm not exactly sure what I need to do to search the Collection for the specified item.

I'm trying this

VB Code:
'FileNum is the unique key. The Employee's payroll ID.Dim FileNum As SingleDim Found As BooleanDim Hours As Single 'Employees hours for one payroll week For Each Item in FNColl If FNColl.Item(Item) = FileNum Then Found = True EmpColl.Item(FNColl.Item) = EmpColl.Item(FNColl.Item) _ + Hours End IfNext If Found = False Then FNColl.Add FileNum, FileNum EmpColl.Add Hours, FileNumEnd If

Is the logic in this right? I'm not sure whether specifying the Item as the key in this For Each statement is the right thing to do.

Thanks one and all for your help,

Tojam

Removing Item From Collection
Dim b As Bookmark
If collbookmarks.Count <> 0 Then
MsgBox "Nothing to remove"
Else
Set Favs = New bkmarks
Favs.Remove Index ' whatever the index number you want removed

Passing Item Of A Collection By Val
Hi all,

How can i pass item of a collectiion by val.

For e.g.
Dim cola As Collection
Dim colb As Collection
Set cola = New Collection
Set colb = New Collection

Dim rstA As ADODB.Recordset
Set rstA = New ADODB.Recordset

rstA.Fields.Append "Nm", adChar, 10
rstA.Open
rstA.AddNew
rstA.Fields(0) = "ABC"
rstA.Update

cola.Add rstA
Set colb = cola
colb.Item(1).Fields(0) = "DEF"

Debug.Print cola.Item(1).Fields(0) 'it prints DEF

How can i change the item in colb while not affecting cola?

Thank You!

Change To Item Key In A Collection
I need to Change to Item key in a collection for all items remaining after I remove an item from it.

Is there a way to do Item.key = XXX ?

Changing The Value Of A Particular Item In A Collection?
Hi there folks! Now, suppose that I've a collection object like this :

Dim m_MyCol as New Collection

Private Sub Form_Load()
m_MyCol.Add "Item1"
End Sub[/ccode]

Private Sub Command1_Click()
'try to change the value of the item
'that we added in Form_Load()
m_MyCol.Item(1) = "ItemXeon!"
End Sub

But the thing is that the compiler gave me an error! I know it's this line that causes the error :

m_MyCol.Item(1) = "ItemXeon!"

In any case, how do I change the value of a particular item in a Collection?
Is it even possible? Thanks a lot! :--D

The lyric of the shattering roar of Xeon(Berserker Mode) :
BWOAR!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!

Getting The Index Of A Collection Item.
Is it possible to get the Index of a collection item if you know the key it was added under?

example:

dim Mycol as new collection
Mycol.Add "keyboard","bob"
Mycol.Add "monitor","peter"
Mycol.Add "desk","joe"

I want to know that "monitor" is the second item in the list by using the key "peter" can this be done?

Jean-Guy

How To Extract The Key Of An Item In A Collection
Could someone tell me how to extract the key of an item in a collection?

Dim mcol as new Collection
mcol.Add "item1", "key1"

Then I passed this collection to an ActiveX DLL.
The ActiveX DLL doesn't know the keys used for each item the collection.
I know I can use an index like : debug.print col(index). But is there a way to extract the keys used for each item in the collection instead of using an index?

Thank you

Query Item Cannot Be Found
For reference, this works great in the query statement. The problem is with the code referencing the query statement. I can even paste the debug print right back in to the sql in the query and get the desired results.

But when running this way, I get the following:
Runtime Error 3265
Item cannot be found in the collection corresponding to the requested name or ordinal.

Any ideas would be greatly appreciated.

' *********************************************************
' Update Rel By / Rel Date Information on BOM for new parts

Dim rsBOM As ADODB.Recordset
Set rsBOM = New ADODB.Recordset

Dim sqlBOM As String

sqlBOM = "SELECT DISTINCT tblECOList.PartNo, " & _
"tblBOM.PartNo, " & _
"tblBOM.JobNo, " & _
"tblECOList.ECORefNum, " & _
"tblECOList.NewPart, " & _
"tblBOM.ReleasedBy, " & _
"tblBOM.ReleasedDate, " & _
"tblPartsListing.Code " & _
"FROM tblPartsListing INNER JOIN (tblBOM INNER JOIN tblECOList ON tblBOM.PartNo = tblECOList.PartNo) ON " & _
"(tblPartsListing.PartNo = tblBOM.PartNo) AND (tblPartsListing.PartNo = tblECOList.PartNo) " & _
"WHERE (((tblBOM.JobNo)=99999) AND " & _
"((tblECOList.ECORefNum)=2276) AND " & _
"((tblECOList.NewPart)=True) AND " & _
"(isnull(tblBOM.ReleasedBy)) AND " & _
"(isnull(tblBOM.ReleasedDate)) AND " & _
"((tblPartsListing.Code)=" & Chr(34) & "Purchased" & Chr(34) & " Or " & _
"(tblPartsListing.Code)=" & Chr(34) & "Purchased-Elec Comp" & Chr(34) & " Or " & _
"(tblPartsListing.Code)=" & Chr(34) & "Purchased-Mech Comp" & Chr(34) & " Or " & _
"(tblPartsListing.Code)=" & Chr(34) & "Purchased-Major Comp" & Chr(34) & " Or " & _
"(tblPartsListing.Code)=" & Chr(34) & "Stock" & Chr(34) & "));"

Debug.Print sqlBOM

rsBOM.Open sqlBOM, CurrentProject.Connection, adOpenDynamic, adLockOptimistic
rsBOM.MoveFirst

Do While Not rsBOM.EOF
MsgBox rsBOM.Fields!PartNo
rsBOM.Fields!ReleasedBy = EmployeeINIT
rsBOM.Fields!ReleasedDate = Date
rsBOM.Update
rsBOM.MoveNext

Loop

rsBOM.Close
Set rsBOM = Nothing

Item Cannot Be Found Error
Why is it that Im getting this error? Item cannot be found in the collection
corresponding to the requested name or ordinal
Below are my codes.

On Form_load:

Code:
Private Sub Form_Load()
Call openConnection
Me.Left = LeftPos - 200
Me.Top = TopPos - 200

Set rsStockOut = New ADODB.Recordset
Set rsClient = New ADODB.Recordset
rsStockOut.CursorLocation = adUseClient

sSQL = "SELECT tblStockMasterFile.ProductID, " & _
"tblStockMasterFile.ProductName, " & _
"tblStockMasterFile.Unit, " & _
"tblStockMasterFile.ReOrderPoint, " & _
"tblStockTransFile.EntryDate, " & _
"tblStockTransFile.pSequence, " & _
"tblStockTransFile.PurchasePrice, " & _
"tblStockTransFile.Quantity, " & _
"tblStockTransFile.Reason, " & _
"tblClientMasterFile.ClientName, " & _
"tblClientMasterFile.ClientCode , " & _
"tblClientMasterFile.Budget " & _
"FROM tblClientMasterFile INNER JOIN " & _
"tblStockTransFile ON " & _
"tblClientMasterFile.ClientCode = tblStockTransFile.ClientCode INNER JOIN " &

_
"tblStockMasterFile ON " & _
"tblStockTransFile.ProductID = tblStockMasterFile.ProductID"

If rsStockOut.State = adStateOpen Then rsStockOut.Close
rsStockOut.Open sSQL, oConn, adOpenStatic, adLockOptimistic

Call FillcboClient
Call LockFunction(frmStockOut, "TextBox")
lstStockOut.Enabled = False
End Sub 'Form_Load

The Function FillcboClient:

Code:
Private Sub FillcboClient()
cboClientName.Clear
cboClientName.AddItem "- Type or Select -"

'TODO : Fill cboAuthorID
'-------------------------------------------------------------------------------------
sSQL = "SELECT ClientCode, ClientName FROM tblClientMasterFile"
'this what i mean for 1 trip..u open the rs just one time

If rsClient.State = adStateOpen Then rsClient.Close
rsClient.Open sSQL, oConn, adOpenStatic, adLockOptimistic

If Not rsClient.BOF And Not rsClient.EOF Then
rsClient.MoveFirst

While rsClient.EOF = False
cboClientName.AddItem rsClient("ClientName").Value
rsClient.MoveNext
Wend
cboClientName.ListIndex = 0
End If
'-------------------------------------------------------------------------------------
End Sub

Product ID Change event:

Code:
Private Sub txtProdID_Change()
txtProdQtyOut.Text = ""

'Use for Barcode reader
' If InStr(1, txtProdID.Text, "*") = Len(txtProdID.Text) Then
' txtProdQty.SetFocus
' End If

If rsStockOut.State = adStateOpen Then rsStockOut.Close

On Error Resume Next
If txtProdID.Text <> "" Then
rsStockOut.Open "SELECT * FROM tblStockMasterFile WHERE ProductID = '" & txtProdID.Text

& "'", oConn, adOpenStatic, adLockOptimistic

If rsStockOut.RecordCount <> 0 Then
txtSupply.Text = rsStockOut.Fields("Quantity")
txtProdName.Text = rsStockOut.Fields("ProductName")
Else
'txtSupply.Text = ""
txtProdName.Text = ""
End If
Else
txtSupply.Text = ""
txtProdName.Text = ""
End If
End Sub 'txtProdID_Change

The cboClientName Change event: The call ReplaceQuotation Function is not working here but in my other forms the same code is perfectly working.

Code:
Private Sub cboClientName_Change()
txtClientCode.Text = ""

'if user click this..then search for the appropriate record then _
display the Client Code in the textbox
sSQL = "SELECT ClientCode, ClientName FROM tblClientMasterFile " & _
"WHERE ClientName='" & cboClientName.Text & "'"

If rsClient.State = adStateOpen Then rsClient.Close
rsClient.Open sSQL, oConn, adOpenStatic, adLockOptimistic
' Call ReplaceQuotation(cboClientName)

'display the Client Code in the textbox
If rsClient.BOF = False And rsClient.EOF = False Then
txtClientCode = rsClient.Fields("ClientCode").Value
End If
End Sub 'cboClientName_Change

The Add Button: the highlighted parts is where the error points.

Code:
Private Sub cmdAddItem_Click()
Dim lst As ListItem

If txtProdID.Locked = True Then
txtProdID.Locked = False
End If 'txtProdID lock

If lstStockOut.Enabled = False Then
lstStockOut.Enabled = True
End If

If CheckNullValue = False Then Exit Sub

Set lst = lstStockOut.ListItems.Add(, , txtProdID.Text) 'DISPLY IN LISTVIEW
lst.SubItems(1) = txtProdName.Text
lst.SubItems(2) = txtProdQtyOut.Text
lst.SubItems(3) = Format$(rsStockOut.Fields("PurchasePrice"), "###,###,##0.00") 'Capital
lst.SubItems(4) = Format$(lst.SubItems(3) * 1.15, "###,###,##0.00") 'Selling Price
lst.SubItems(5) = Format(lst.SubItems(2) * lst.SubItems(4), "###,###,##0.00") 'Total

txtProdID.Text = ""
txtProdQtyOut.Text = ""
txtProdID.SetFocus

Set lst = Nothing 'destroy the list
End Sub 'cmdAddItem_Click

Can someone please tell me what am i missing here?
I have attached here my table relationships
Any help is much appreaciated and thank you very much in advance.

Item Not Found In This Connection
I need help with the following code. I just added pallet fields in the table. I have a form which pulls up information from prior like orders. I need to add a SQL that brings up the new pallet fields from the table which they are present. When the second SQL is ran I get a "Item not found in this connection" error. Any help would be greatly appreciated. Thanks,

strSQL = "SELECT Packaging,ESTHours,[Order#ID] " & _
             "FROM [Production Schedule] " & _
             "WHERE CustomerNo = '" & Trim(rs("CUSTOMER_NUMBER")) & "' " & _
             "AND Compound = '" & strCompound & "' " & _
             "AND Color = '" & strColor & "' " & _
             "AND Mapics = '" & strMapics & "' " & _
             "AND Packaging IS NOT NULL " & _
             "ORDER BY [Order#ID] DESC"

         Set dbs = CurrentDb
          Set rs = dbs.OpenRecordset(strSQL)


          If Not rs.EOF Then

                        Form.Packaging.Value = rs("Packaging")
                            Form.EstHours.Value = rs("ESTHours")
                                   End If
       strSQL = "SELECT PalletQuanity,PalletWidth,PalletLength,PalletHeight,Surface,[Order#ID] " & _
             "FROM [Production Schedule] " & _
             "WHERE CustomerNo = '" & Trim(rs("CUSTOMER_NUMBER")) & "' " & _
             "AND Compound = '" & strCompound & "' " & _
             "AND Color = '" & strColor & "' " & _
             "AND Mapics = '" & strMapics & "' " & _
             "AND PalletQuanity IS NOT NULL "


           Set dbs = CurrentDb
    Set rs = dbs.OpenRecordset(strSQL)

    If Not rs.EOF Then
                            Form.PalletQuanity.Value = rs("PalletQuanity")
                             Form.PalletWidth.Value = rs("PalletWidth")
                              Form.PalletLength.Value = rs("PalletLength")
                               Form.PalletHeight.Value = rs("PalletHeight")
                              Form.Surface.Value = rs("Surface")
                                        End If

Item Cannot Be Found...Error
I get this error
Item cannot not be found in the collection corresponding to the requested name or ordinal.

I know that it supposed to mean that the field doesn't exist in my table...but it does...I copy and pasted the name and I rechecked and it's the same name,
After that I checked if I had record in this field. I do...So How can I solve my problem? I just want to add two item in my combo box.
Thanks in advance.
This is my code

Set RsQuotTemp = MyConn.Execute("select distinct noquotation from Tblquotation where nocustomer = " & Me.MSFGCustomer.text)
    If Not RsQuotTemp.EOF Then
    MsgBox (RsQuotTemp("QuotationLabel"))

      While Not RsQuotTemp.EOF
        Me.CmbQuotations.AddItem (RsQuotTemp("NoQuotation") & " / " & RsQuotTemp("QuotationLabel")) 'Ajoute les Quotations creer par ce client dans le combo box
        RsQuotTemp.MoveNext
      Wend

Please Help: Can't Remove Item From List (collection)
Explanation:
When the case form loads, the ucboProbationOfficer displays all officers from a sproc that brings all officers, regardless of inactive or active for historical data. When you select cmdNew on this form, we want the users to see only the active officers, so now I want to check if each officer in user combo box (ucboProbationOfficer) is active. If it's not active, remove it from the combobox which is derived from Collection colOfficers and Class clsOfficers

Note:
First try, I did have the following, but when it got to record 25 out of 30, it kept going to Terminate class and got subscript out of range error (5 records of 30 are inactive). So hardcoded it to 25 for now.

1st time tried: For p_lngItem2 = 1 To p_counterholder

------------------------------------------------------------------
Private Sub AssignEdit2()

Dim p_colOfficer As COLOfficers
Dim p_lngItem2 As Long
Dim p_lngRecord2 As Long
Dim p_vntItem2 As Variant
Dim p_counterholder As Variant

Set p_colOfficer = New COLOfficers
p_colOfficer.Load

p_lngRecord2 = 0
'p_counterholder = p_colOfficer.Count
For p_lngItem2 = 1 To 25 'p_counterholder

If p_colOfficer.Item(p_lngItem2).IsActive = False Then
p_colOfficer.Remove (p_lngItem2)
End If

Next p_lngItem2

--------------------------------------------

Results: Get same data, all officers so did not remove officers. Is this correct sytax?

Find The Value Of An Array Item Which Is In A Collection
Hi,

I have a collection and each and every item is an array of values.

I would like to find the value of items in that array in that collection. I can extract the values if I use For Each ... However, I would like to access directly and find the value.

The current code I have is:

Code:
Sub TestCollection()
Dim oTests As New Collection
Dim var As Variant

oTests.Add Array("student 1", "Maths", "90")
oTests.Add Array("student 2", "history", "50")
oTests.Add Array("student 3", "science", "80")

For Each var In oTests
'var(0) 'Student
'var(1) 'subject
'var(2) 'marks
Debug.Print var(0)
Debug.Print var(1)
Debug.Print var(2)
Next var
End Sub

I would like to access and get the value "student 2" directly, not using for each.

Thank you

Remove Collection Item In Listbox
I have a listbox showing options for the selected customer. I can't get my code working to remove an item from the listbox. I am very new at using VB and am very alone on this project as we don't really have an IT dept, it's just a small office application I'm developing.

For some reason the ID of the customer is getting sent to the remove method of the collection class and not the ID of the Customer's option. For example:

ID Customer Option
22 ABCCo Tax1
33 XYZCo NoTax
37 ABCCo Tax2

I want to remove ID 37 but it is sending OID = 2 (the customer's OID field in Access table).

Here is my code:

VB Code:
Private Sub cmdRemove_Click() Dim lngOID As Long Set mobjCustOpt = New CustomerBus.CustOptions lngOID = lstCustOpt.ItemData(lstCustOpt.ListIndex) mobjCustOpt.Remove (lngOID) End Sub

I thought that ListIndex would give me the ID field of the CustomerOptions table but it gives me the ID field of the Customer table for the customer displayed on the form.

thanks.

Assigning Values To An Item In A Collection.
Can you assign values to an item in a collection?

I am getting an object required message on the following line

Code:
mcolOptions(tvwOptions.SelectedItem.Parent.Key & 1) = intCount + 1

The first part works OK because it will return a value

Code:
mcolOptions(tvwOptions.SelectedItem.Parent.Key & 1)
in the immediate window.

Its something to do with the assignment. Either you can't assign to a collection or I am passing the wrong type in.

Retreive Collection Item Using Key Gives Error
Hi
I am doing

MileageReport(i).collCabs.Item(CStr(uniqCabIds(j)))

where collcabs is a collection that has entries based on keyvalue, uniqCabIds and MileageReport are arrays.
The references to the two arrays are legal , but the search for an item in collCabs based on key should result in Nothing being returned sincve the element does not exist with that key. Unfortunately I get a Invalid procedure call or argument Error on this line.

Is there a way to detect if an entry exists for a given key in the collection?

As a note, I put in a error handler just before that line but that seems to be getting ignored.

Pls help quick!
Thanks

How Do I Detect Whether A Collection Item Exists?
Hi,

I was just wondering how to detect whether a particular item in a collection exists?

ie. How do I check whether EMS Tools already exists in the following CommandBars collection?

Application.CommandBars("EMS Tools")

Thanks

Chris

Strings / Collection Item Queries ?
Hai,

Suppose i am having two collections nbamely , strRow1 and strRow1.
I have two strings (of same length) namely, strResult1 and strResult2. Using the Instr() function, i ind the positions where "1" appears in each string and store the positions numbers to the collections respectively.

Now i compute the .Count of each collection. The minimum of it is taken and i want to compare whether all the numbers in the collection with minimum count has same elements in the other collection. If atleast one item is missing give a

Msgbox : "One or more elemnts do not match"

otherwise give a

Msgbox saying "All the items in the first collection are in the second collection also"

I have done almost all of it except the last part , please start a new VB project and paste the below code.

U can see that the count of the second collec is 4 and the count of the first collection is 5. Therfore pick the second collec and compare the items with the other collections items.

VB Code:
Dim strRow1 As New CollectionDim strRow2 As New CollectionPrivate Sub Form_Load() Dim i As Integer Dim j As Integer Dim intPos As Integer Dim intJPos As Integer Dim strResult1 As String Dim strResult2 As String strResult1 = "100100010100001" '1 4 8 10 15 For i = 1 To Len(strResult1) intPos = InStr(i, strResult1, "1") Debug.Print intPos strRow1.Add intPos, "Pos" & i i = intPos + 1 Next i strResult2 = "010001000010001" '2 6 11 15 For j = 1 To Len(strResult2) intJPos = InStr(j, strResult2, "1") Debug.Print intJPos strRow2.Add intJPos, "Pos" & j j = intJPos + 1 Next j MsgBox "The number of 1's in strRow1 and strRow2 is : " & vbCrLf _ & Chr(9) & Chr(9) & strRow1.Count & Chr(9) & strRow2.Count End Sub

any help/ ideas / codes highly appreciated...,

ashky.

Are Item Values In Collection Read-only?
Hi!

I have two problems. The first one is that when I try to assign a value to an item for a collection, an error is returned

dim col1 as collection
set col1 = new collection

col1.Add 1, "first"
col1.Add 2, "second"

col1("first") = 3 ' error here

So, do I have to always remove and append an item? Because you can set a value of a specific field in recordset as rs.Fields(0) = "MyValue", I think it's obvious that other collections should be read-write enabled, and not read-only.

Or is there some mistake I am making?

2. Quick question. I made a function which returns a boolean value whether a value is already in the array specified (InArray(MyValue, MyArray)). However, when I use this function in a loop and the array's first value has not yet been initialized, I get an error (Index out of bounds or something like this). The same happens if I try to check that UBound is more than 1 - same error. How do I avoid this problem? I tried IsArray, IsEmpty, to no avail.

Thanks a lot in advance,

Stas