How to fix code with Collection error

[DOYLE60]

I have code that duplicates a record on a table along with detail records of
another table (Header and Detail) after the user fills in a new Purchase Order
number (key field). The code has sevrved me for 5 years or so. When I add a
field to one of the tables, I simply add that field to the list of fields in
the code and the code works, copying all. However, last week I added several
fields and after I added the fields, the code did not work. It gives me the
message:

"Run--time error '3265': Item not found in theis collection."

Well, the item *is* in that collection."

I can't figure out why these fields are not in the collection. They are in the
collection and they are spelled correctly. What is going on here? What is the
collection the error message is referring to? Though the forms and everything
works perfectly, as far as I know, the code acts as if these fields don't
exist, or as if the field is spelled wrong. Yes, I tested it, and wrong field
names give the same error.
Below is the code in question, if that helps.

Thanks,

Matt

__________________________________________________________

Function TempToRegPO()
'Make the New PO entered in text box on Transfer form a string.
'Make the Temp PO in the combo box on Transfer form a string.
Dim strNewPO As String, strTempPO As String

'Create RecordSets to hold data during code.
Dim rs As Recordset, rs2 As Recordset, db As Database, rs3 As Recordset
Dim rs4 As Recordset, rs5 As Recordset
Dim rs6 As Recordset, rs7 As Recordset

'Set the database etc.
Set db = dbEngine(0)(0)

strTempPO = Forms![TransferTempPOtoRegPOfrm]![TempPO] 'Define the TempPO on
transfer form.
strNewPO = Forms![TransferTempPOtoRegPOfrm]![txtPOinput] 'Define the NewPO on
transfer form.

' If the user did not enter a NewPO number, message will appear.
If IsNull(strNewPO) Then
MsgBox ("Please enter new PO number")
End If

'Check if the new PO already exists. If it does, rs will have one record in it
where PO is the same as the
'PO entered in the PO text box on the Transfer form.
Set rs = db.OpenRecordset("SELECT * FROM [orderheaderqry] WHERE
[orderheaderqry].[PO] = '" & strNewPO & "';")
If rs.RecordCount = 1 Then
MsgBox ("PO number already exists, please try another.")
'If the New PO is indeed new, the following will run.
Else
'Make rs2 the record (only one) to be copied. It is the record in the
TempOrderHeaderqry where the PO equals
'the TempPO on the Transfer form.
'Make rs3 the record we will be adding to the OrderHeaderqry.
Set rs2 = db.OpenRecordset("SELECT * FROM [TempOrderHeaderqry] WHERE
[TempOrderHeaderqry].[PO] = '" & strTempPO & "';")
Set rs3 = db.OpenRecordset("OrderHeaderqry")

rs2.MoveFirst ' Go to first field of the record to be copied.
rs3.AddNew 'add a new row (or go the * row) of the OrderHeader (where the
fields will be copied to).
rs3![PO] = strNewPO 'Set the PO to the NewPO.
rs3![CategoryID] = rs2![CategoryID] 'Copy from one record to the other.
rs3![GroupID] = rs2![GroupID]

'snipping 20 or so fields

rs3![Memo3] = rs2![Memo3]
rs3![SizeNumber] = rs2![SizeNumber]
rs3![Size1] = rs2![Size1]
'Causes an error.
rs3![Size2] = rs2![Size2]
'Causes an error.
rs3![Size3] = rs2![Size3]
'Causes an error.
rs3![Size4] = rs2![Size4]
'Causes an error.
rs3![Size5] = rs2![Size5]
'Causes an error.
rs3![Size6] = rs2![Size6]
'Causes an error.
rs3![Size7] = rs2![Size7]
'Causes an error.
rs3![Size8] = rs2![Size8]
'Causes an error.
rs3![Size9] = rs2![Size9]
'Causes an error.
rs3![Integrated] = rs2![Integrated]
'Causes an error.
rs3![Misc3Title] = rs2![Misc3Title]
'Causes an error.
rs3![Misc3Data] = rs2![Misc3Data]
'Causes an error.
rs3![SubFactory] = rs2![SubFactory]
'Causes an error.
rs3![WHInstructions] = rs2![WHInstructions] 'Causes
an error.
rs3![CreatedBy] = rs2![TempCreatedby] 'Causes
an error.
rs3.Update


'Make rs4 the record (perhaps many) to be copied. They are the records in
the TempOrderDetailsqry where the PO equals
'the TempPO on the Transfer form.
'Make rs3 the record we will be adding to the OrderDetails.
Set rs4 = db.OpenRecordset("SELECT * FROM [TempOrderDetailsqry] WHERE
[TempOrderDetailsqry].[PO] = '" & strTempPO & "';")
Set rs5 = db.OpenRecordset("OrderDetails")

rs4.MoveFirst
Do Until rs4.EOF
rs5.AddNew
rs5![PO] = strNewPO
rs5![Counter] = rs4![Counter]
rs5![Style] = rs4![Style]
rs5![XL] = rs4![XL]
rs5![2XL] = rs4![2XL]
'rs5!Size7 = rs4![Size7x] 'Causes an
error.
'rs5!Size8 = rs4![Size8x] 'Causes an
error.
'rs5!Size9 = rs4![Size9x] 'Causes an
error.
rs5![Packing] = rs4![Packing]
rs5![FOB] = rs4![FOB]
rs5![S P] = rs4![S P]
rs5![QuotaCat] = rs4![QuotaCat] 'Causes an
error.
rs5![PackingBreak] = rs4![PackingBreak] 'Causes an error.
rs5.Update
rs4.MoveNext
Loop

'Do the same as above loop with StorePO's.
Set rs6 = db.OpenRecordset("SELECT * FROM [TempStorePOtbl] WHERE
[TempStorePOtbl].[PO] = '" & strTempPO & "';")
Set rs7 = db.OpenRecordset("StorePOtbl")

rs4.MoveFirst
Do Until rs6.EOF
rs7.AddNew
rs7![PO] = strNewPO
rs7![StorePO] = rs6![StorePO]
rs7![Notes] = rs6![Notes]
rs7.Update
rs6.MoveNext
Loop

MsgBox ("PO has been made official")


'Refresh EntryForm2 without moving it around.
If IsOpen("EntryForm2") Then
Forms![ENTRYFORM2].Requery
Forms![ENTRYFORM2]![Combo366].Requery
Forms![ENTRYFORM2]![StorePOEntrysub].Requery
With Forms![ENTRYFORM2].RecordsetClone
.FindFirst "PO = """ & strNewPO & """"
If Not .NoMatch Then
Forms![ENTRYFORM2].Bookmark = .Bookmark
End If
End With
End If
End If
'The following code facilitates transfering another PO.
'Sends TempEntryForm2 to the next PO.
If IsOpen("TempEntryForm2") Then
DoCmd.GoToRecord acDataForm, "TempEntryForm2", acNext, 1
End If

'Closes the Transfer PopUp form (temporarily).
DoCmd.Close acForm, "TransferTempPOtoRegPOfrm"
'Opens the Transfer PopUp form (to reset it to the TempEntryForm2
settings).
Dim stDocName As String
Dim stLinkCriteria As String
Dim rsFind As Recordset
stDocName = "TransferTempPOtoRegPOfrm"
stLinkCriteria = "[DivisionNumber]=" &
Forms!TempEntryForm2![DivisionNumber]
DoCmd.OpenForm stDocName
Set rsFind = Forms(stDocName).RecordsetClone
rsFind.FindFirst stLinkCriteria
Forms(stDocName).Bookmark = rsFind.Bookmark
End Function

 

[Dev Ashish]

(e-mail address removed) (DOYLE60) wrote in

However, last week I added several
fields and after I added the fields, the code did not work. It gives
me the message:

"Run--time error '3265': Item not found in theis collection."

Well, the item *is* in that collection."

I can't figure out why these fields are not in the collection.

Enumerate the fields in those recordsets and compare them to see the
difference.

Dim f as Field
For each f in SomeRecordset.Fields
debug.print f.Name
Next

Also, to add a record, you don't have to use DAO. You can also use SQL
"Insert into sometable select field1, field2, field3, etc from
someothertable"

-- Dev

 

[DOYLE60]

I'm not quite sure what you are saying here. I'm not very good with code at
all. The code I showed was written by a friend and this list with minor
tweaking by me. I used the "Help" to try and understand what "Enumerate" means
here and am still lost. Are your comments here to try and understand the
problem, or are they an alternative method for duplicating records? I have no
idea what your last comment is about.

I can't quite understand why when I added fields a year ago to a table, and
then added those fields to this code, it worked. Now, after adding further
fields, it doesn't work. It's as if the fields don't exist in those tables.
They do. And they are spelled correctly. I tried compacting. The database is
split and is used by 20 people or more.

Do you have the time to explain it more step by step here? If not, I
understand and thanks anyway.

Enumerate the fields in those recordsets and compare them to see the
difference.
Enumerate?

Dim f as Field
For each f in SomeRecordset.Fields
debug.print f.Name
Next

Is this something to put in a debug window? I don't know how to use it.

Also, to add a record, you don't have to use DAO. You can also use SQL
"Insert into sometable select field1, field2, field3, etc from
someothertable"

Is this an alternative method for the code? I haven't a clue what to do with
this.

Matt

 

[Dev Ashish]

(e-mail address removed) (DOYLE60) wrote in

I'm not quite sure what you are saying here.

The problems you are experiencing have to do with the field name not being
present in one, or more, tables.

Are your comments here
to try and understand the problem,

Yup, you'll have to compare to find out which fields are missing, and from
which recordset.

Enumerate?

Just a code-term to list out all items from a given collection, in this
case, the Fields collection.

For example, you have several recordsets in your code, all using "select
*" which returns all fields from the table.

Set rs = db.OpenRecordset("SELECT * FROM [orderheaderqry] WHERE
[orderheaderqry].[PO] = '" & strNewPO & "';")

Set rs2 = db.OpenRecordset("SELECT * FROM [TempOrderHeaderqry] WHERE
[TempOrderHeaderqry].[PO] = '" & strTempPO & "';")

Set rs3 = db.OpenRecordset("OrderHeaderqry")

You can temporarily comment out all code that updates the columns and put
in the debug code to see which columns come back. You should only need one
step-through to see the output. So, a modified code listing (you can put in
this code right after the "set rs" line in your code).

Dim f as Field

Set rs = db.OpenRecordset("SELECT * FROM [orderheaderqry] WHERE
[orderheaderqry].[PO] = '" & strNewPO & "';")

For each f in rs.Fields
debug.print "RS [orderheaderqry] Field: ", f.Name
Next

Set rs2 = db.OpenRecordset("SELECT * FROM [TempOrderHeaderqry] WHERE
[TempOrderHeaderqry].[PO] = '" & strTempPO & "';")

For each f in rs2.Fields
debug.print "RS2 [TempOrderHeaderqry] Field: ", f.Name
Next


Set rs3 = db.OpenRecordset("OrderHeaderqry")
For each f in rs3.Fields
debug.print "rs3 [OrderHeaderqry] Field: ", f.Name
Next

and so on... Hopefully this will help trace why the first line in your
listing

rs3![WHInstructions] = rs2![WHInstructions]

fails (WHInstructions could be missing from rs2 or rs3; the effect will be
the same).

Is this something to put in a debug window? I don't know how to use
it.

See above.

Is this an alternative method for the code?

Yes, it's an alternate way, and usually preferred, to insert or update
records into a table. If you are more comfortable with the individual
updates, then stick with it. The SQL way is typically more readable in code
and terse compared to field-by-field update. For example, for rs2 and rs
interaction in your code, you are copying one record into the
OrderHeaderqry table/query from the TempOrderHeaderqry table/query. You
could recode it as

Dim sql as string
sql = "insert into OrderHeaderqry (PO, CategoryID, GroupID, Memo3, " & _
" Size1, Size2, Size3, ....) " & _
"select PO, CategoryID, GroupID, Memo3, Size1, " & _
"Size2, Size3, Size4, ...., from TempOrderHeaderqry " & _
" where TempOrderHeaderqry.PO = '" & strTmpPO & "'"
CurrentDB.Execute sql, dbFailOnError

Where '...' represent the remaining columns which are hardcoded in your
code. This will work for a single record just as well as for multiple
records.

-- Dev

 

[DOYLE60]

Thanks Dev. By trying to understand your code, I was able to see my own
mistake. You see, I used something called "orderheaderqry." Well, that is a
query and not a table, of course. How dumb of me. I don't know why I built
this one on a query and not a table years ago. But anyhow. The problem is
solved.

Thanks again,

Matt