Help needed with an item not in a collection..........ACC97 
Author Message
 Help needed with an item not in a collection..........ACC97

Hi All,
Could someone assist with a recordset problem I'm having in Access97.
The following code produces an "item not found in this collection
error" This has been driving me nuts for the last week. Can anyone
help please??

Dim OriginalDataExist As QueryDef
Dim OriginalData As Recordset
Dim NewDataExist As QueryDef
Dim NewData As Recordset

'This one loops through the subtable TblInvoiceQuotesLabourDetails and
copies the records into the subtable TblInvoiceLabourDetails

NewDataExist = DB.CreateQueryDef("", "SELECT
[FldInvoiceNumber],[FldLabour],[FldLabourHours],[FldLabourCost] FROM
[TblInvoiceLabourDetails] WHERE
[FldInvoiceNumber]=[forms]![FrmInvoicesQuoteMain]![FldInvoiceNumber];")

Set NewData = QryDefinitionExist.OpenRecordset(dbOpenDynaset)

Set OriginalDataExist = DB.CreateQueryDef("", "SELECT
[FldInvoiceNumber],[FldLabour],[FldlabourHours],[FldLabourCost] FROM
[TblInvoiceQuotesLabourDetails] WHERE
[FldInvoiceNumber]=[forms]![FrmInvoicesQuoteMain]![FldInvoiceNumber];")

Set OriginalData = QryDefinitionExist.OpenRecordset(dbOpenDynaset)

'There are no rows in this dataset so adds a new row.......
NewData.AddNew

''Moves to the first record of the recordset
OriginalData.MoveFirst

'The idea now is that values are copied from a field in OriginalData
'and put into the same field in NewData.
Do Until OriginalData.EOF

The next line works fine..........
NewData.[FldInvoiceNumber].Value = OriginalData![FldInvoiceNumber]

'The following is the line generating the error.............
'I have checked and rechecked spelling.............
'Same syntax as the previous line.........
'Any other ideas????
NewData.[FldLabour].Value = OriginalData![FldLabour]

'This is the rest of the code which I can't get to right now
'due to the error.
NewData.[FldlabourHours].Value = OriginalData![FldlabourHours]
NewData.[FldLabourCost].Value = OriginalData![FldLabourCost]
NewData.Update
NewData.AddNew
OriginalData.MoveNext
Loop
NewData.Close
OriginalData.Close

What little hair I have left is rapidly disappearing. I have the Acc97
Developers handbook and this code has basically ben adapted from
there. The strange thing is the first line in the loop works but not
the second even though they have the smae syntax.

Any help will be greatly appreciated.

John Murray
Whyalla, South Australia



Wed, 27 Mar 2002 03:00:00 GMT  
 Help needed with an item not in a collection..........ACC97
John,

Try using a 'bang' instead of a 'dot', i.e.:
  NewData![FldLabour].Value = OriginalData![FldLabour]

Simon Lewis

Quote:

>Hi All,
>Could someone assist with a recordset problem I'm having in Access97.
>The following code produces an "item not found in this collection
>error" This has been driving me nuts for the last week. Can anyone
>help please??

>Dim OriginalDataExist As QueryDef
>Dim OriginalData As Recordset
>Dim NewDataExist As QueryDef
>Dim NewData As Recordset

>'This one loops through the subtable TblInvoiceQuotesLabourDetails and
>copies the records into the subtable TblInvoiceLabourDetails

>NewDataExist = DB.CreateQueryDef("", "SELECT
>[FldInvoiceNumber],[FldLabour],[FldLabourHours],[FldLabourCost] FROM
>[TblInvoiceLabourDetails] WHERE
>[FldInvoiceNumber]=[forms]![FrmInvoicesQuoteMain]![FldInvoiceNumber];")

>Set NewData = QryDefinitionExist.OpenRecordset(dbOpenDynaset)

>Set OriginalDataExist = DB.CreateQueryDef("", "SELECT
>[FldInvoiceNumber],[FldLabour],[FldlabourHours],[FldLabourCost] FROM
>[TblInvoiceQuotesLabourDetails] WHERE
>[FldInvoiceNumber]=[forms]![FrmInvoicesQuoteMain]![FldInvoiceNumber];")

>Set OriginalData = QryDefinitionExist.OpenRecordset(dbOpenDynaset)

>'There are no rows in this dataset so adds a new row.......
>NewData.AddNew

>''Moves to the first record of the recordset
>OriginalData.MoveFirst

>'The idea now is that values are copied from a field in OriginalData
>'and put into the same field in NewData.
>Do Until OriginalData.EOF

>The next line works fine..........
>NewData.[FldInvoiceNumber].Value = OriginalData![FldInvoiceNumber]

>'The following is the line generating the error.............
>'I have checked and rechecked spelling.............
>'Same syntax as the previous line.........
>'Any other ideas????
>NewData.[FldLabour].Value = OriginalData![FldLabour]

>'This is the rest of the code which I can't get to right now
>'due to the error.
>NewData.[FldlabourHours].Value = OriginalData![FldlabourHours]
>NewData.[FldLabourCost].Value = OriginalData![FldLabourCost]
>NewData.Update
>NewData.AddNew
>OriginalData.MoveNext
>Loop
>NewData.Close
>OriginalData.Close

>What little hair I have left is rapidly disappearing. I have the Acc97
>Developers handbook and this code has basically ben adapted from
>there. The strange thing is the first line in the loop works but not
>the second even though they have the smae syntax.

>Any help will be greatly appreciated.

>John Murray
>Whyalla, South Australia



Wed, 27 Mar 2002 03:00:00 GMT  
 Help needed with an item not in a collection..........ACC97
Hi All,
Could someone assist with a recordset problem I'm having in Access97.
The following code produces an "item not found in this collection
error" This has been driving me nuts for the last week. Can anyone
help please??

Dim OriginalDataExist As QueryDef
Dim OriginalData As Recordset
Dim NewDataExist As QueryDef
Dim NewData As Recordset

'This one loops through the subtable TblInvoiceQuotesLabourDetails and
copies the records into the subtable TblInvoiceLabourDetails

NewDataExist = DB.CreateQueryDef("", "SELECT
[FldInvoiceNumber],[FldLabour],[FldLabourHours],[FldLabourCost] FROM
[TblInvoiceLabourDetails] WHERE
[FldInvoiceNumber]=[forms]![FrmInvoicesQuoteMain]![FldInvoiceNumber];")

Set NewData = QryDefinitionExist.OpenRecordset(dbOpenDynaset)

Set OriginalDataExist = DB.CreateQueryDef("", "SELECT
[FldInvoiceNumber],[FldLabour],[FldlabourHours],[FldLabourCost] FROM
[TblInvoiceQuotesLabourDetails] WHERE
[FldInvoiceNumber]=[forms]![FrmInvoicesQuoteMain]![FldInvoiceNumber];")

Set OriginalData = QryDefinitionExist.OpenRecordset(dbOpenDynaset)

'There are no rows in this dataset so adds a new row.......
NewData.AddNew

''Moves to the first record of the recordset
OriginalData.MoveFirst

'The idea now is that values are copied from a field in OriginalData
'and put into the same field in NewData.
Do Until OriginalData.EOF

The next line works fine..........
NewData.[FldInvoiceNumber].Value = OriginalData![FldInvoiceNumber]

'The following is the line generating the error.............
'I have checked and rechecked spelling.............
'Same syntax as the previous line.........
'Any other ideas????
NewData.[FldLabour].Value = OriginalData![FldLabour]

'This is the rest of the code which I can't get to right now
'due to the error.
NewData.[FldlabourHours].Value = OriginalData![FldlabourHours]
NewData.[FldLabourCost].Value = OriginalData![FldLabourCost]
NewData.Update
NewData.AddNew
OriginalData.MoveNext
Loop
NewData.Close
OriginalData.Close

What little hair I have left is rapidly disappearing. I have the Acc97
Developers handbook and this code has basically ben adapted from
there. The strange thing is the first line in the loop works but not
the second even though they have the smae syntax.

Any help will be greatly appreciated.

John Murray
Whyalla, South Australia



Thu, 28 Mar 2002 03:00:00 GMT  
 
 [ 3 post ] 

 Relevant Pages 

1. VB Collections - Accessing the collection, not the item.

2. HELP - Item not found in this collection...

3. Help: Item not found in this collection.

4. HELP - Item not found in this collection...

5. item not found in collection error in qrydef

6. Item not found in Collection Error?

7. Item not in this collection

8. Variable field Names/Item not in Collection

9. Item not found in collection error

10. Item not found in this collection

11. Item Not Found in Collection (Error 3036)

12. Item not found in this collection error

 

 
Powered by phpBB® Forum Software