Welcome to SPN

Register and Join the most happening forum of Sikh community & intellectuals from around the world.

Sign Up Now!

Item not found in this collection error

Discussion in 'Information Technology' started by Brandon, Nov 10, 2005.

  1. Brandon

    Brandon
    Expand Collapse
    Guest

    Hello I am using this code below to take a total savings number, break it out
    by percentages and populate the subform based on these values. However I get
    an error for "Item not found in the collection" when it is running through
    the .AddNew Function. Why does this happen?
    Thanks for any assistance!
    Brandon

    Private Sub cmdSGACalc_Click()
    On Error GoTo Err_cmdSGACalc_Click

    Dim curSavingsByMonth As Currency
    Dim intMonth As Integer
    Dim x As Integer
    Dim intDivisionID As Integer
    Dim intLocationID As Integer
    Dim curHistoricVolume As Currency
    Dim curLowBidAmt As Currency
    Dim curImplementedBidAmt As Currency
    Dim intProjectID As Integer
    Dim intProjectType As Integer
    Dim intCurrencyID As Integer
    Dim intLotNumber As Integer
    Dim curDivisionPercent(1 To 4) As Currency

    curDivisionPercent(1) = 0.254
    'BI
    curDivisionPercent(2) = 0.176
    'PM
    curDivisionPercent(3) = 0.35
    'EPG
    curDivisionPercent(4) = 0.22
    'RSG

    intDivisionID = 1
    x = 1
    intProjectID = Me!ProjectID.Value
    intLotNumber = Me!LotNumber.Value
    intLocationID = 52 'North America
    curHistoricVolume = Me!SGAHistoricVolume.Value
    curLowBidAmt = Me!SGALowBidAmt.Value
    curImplementedBidAmt = Me!SGAImplementedBidAmt.Value
    intProjectType = Me!SGAProjectTypeID.ItemData(1)
    intCurrencyID = Me!SGACurrencyID.ItemData(1)
    Me!sfrmLotBreakout.SetFocus
    Me.sfrmLotBreakout.CostAvoidance.SetFocus

    With Me!sfrmLotBreakout.Form.RecordsetClone
    For x = 1 To 4
    'calculate the monthly savings and add records
    'to the subform Brandon Cheal 7/14/04
    .AddNew
    !ProjectTypeID = intProjectType
    !CurrencyID = intCurrencyID
    !LotNumber = intLotNumber
    !DivisionID = intDivisionID
    !LocationID = intLocationID
    !HistoricVolume = curHistoricVolume *
    curDivisionPercent(intDivisionID)
    !LowBidAmt = curLowBidAmt *
    curDivisionPercent(intDivisionID)
    !ImplementedBidAmt = curImplementedBidAmt *
    curDivisionPercent(intDivisionID)
    !ProjectID = intProjectID
    .Update
    intDivisionID = intDivisionID + 1
    Next x
    End With

    Exit_cmdSGACalc_Click:
    Exit Sub

    Err_cmdSGACalc_Click:
    MsgBox Err.Description
    Resume Exit_cmdSGACalc_Click


    End Sub
     
  2. Loading...


  3. Ofer

    Ofer
    Expand Collapse
    Guest

    You get this error when you try to assign a value or get a value from a
    field that doesnt exist

    When you get the error, press Ctrl+Break, the code will stop in your error
    trap
    Err_cmdSGACalc_Click:
    MsgBox Err.Description
    Resume
    Resume Exit_cmdSGACalc_Click

    After the msgbox line, write resume, and step the code by pressing F8 key,
    you will be return to the line with the error, check the field name, and
    check if it exist

    --
    The next line is only relevant to Microsoft''s web-based interface users.
    If I answered your question, please mark it as an answer. It''s useful to
    know that my answer was helpful
    HTH, good luck


    "Brandon" wrote:

    > Hello I am using this code below to take a total savings number, break it out
    > by percentages and populate the subform based on these values. However I get
    > an error for "Item not found in the collection" when it is running through
    > the .AddNew Function. Why does this happen?
    > Thanks for any assistance!
    > Brandon
    >
    > Private Sub cmdSGACalc_Click()
    > On Error GoTo Err_cmdSGACalc_Click
    >
    > Dim curSavingsByMonth As Currency
    > Dim intMonth As Integer
    > Dim x As Integer
    > Dim intDivisionID As Integer
    > Dim intLocationID As Integer
    > Dim curHistoricVolume As Currency
    > Dim curLowBidAmt As Currency
    > Dim curImplementedBidAmt As Currency
    > Dim intProjectID As Integer
    > Dim intProjectType As Integer
    > Dim intCurrencyID As Integer
    > Dim intLotNumber As Integer
    > Dim curDivisionPercent(1 To 4) As Currency
    >
    > curDivisionPercent(1) = 0.254
    > 'BI
    > curDivisionPercent(2) = 0.176
    > 'PM
    > curDivisionPercent(3) = 0.35
    > 'EPG
    > curDivisionPercent(4) = 0.22
    > 'RSG
    >
    > intDivisionID = 1
    > x = 1
    > intProjectID = Me!ProjectID.Value
    > intLotNumber = Me!LotNumber.Value
    > intLocationID = 52 'North America
    > curHistoricVolume = Me!SGAHistoricVolume.Value
    > curLowBidAmt = Me!SGALowBidAmt.Value
    > curImplementedBidAmt = Me!SGAImplementedBidAmt.Value
    > intProjectType = Me!SGAProjectTypeID.ItemData(1)
    > intCurrencyID = Me!SGACurrencyID.ItemData(1)
    > Me!sfrmLotBreakout.SetFocus
    > Me.sfrmLotBreakout.CostAvoidance.SetFocus
    >
    > With Me!sfrmLotBreakout.Form.RecordsetClone
    > For x = 1 To 4
    > 'calculate the monthly savings and add records
    > 'to the subform Brandon Cheal 7/14/04
    > .AddNew
    > !ProjectTypeID = intProjectType
    > !CurrencyID = intCurrencyID
    > !LotNumber = intLotNumber
    > !DivisionID = intDivisionID
    > !LocationID = intLocationID
    > !HistoricVolume = curHistoricVolume *
    > curDivisionPercent(intDivisionID)
    > !LowBidAmt = curLowBidAmt *
    > curDivisionPercent(intDivisionID)
    > !ImplementedBidAmt = curImplementedBidAmt *
    > curDivisionPercent(intDivisionID)
    > !ProjectID = intProjectID
    > .Update
    > intDivisionID = intDivisionID + 1
    > Next x
    > End With
    >
    > Exit_cmdSGACalc_Click:
    > Exit Sub
    >
    > Err_cmdSGACalc_Click:
    > MsgBox Err.Description
    > Resume Exit_cmdSGACalc_Click
    >
    >
    > End Sub
     
  4. Brandon

    Brandon
    Expand Collapse
    Guest

    But I know the field name exists because it's a field on the subform! So how
    could the field not exist in code?


    "Ofer" wrote:

    > You get this error when you try to assign a value or get a value from a
    > field that doesnt exist
    >
    > When you get the error, press Ctrl+Break, the code will stop in your error
    > trap
    > Err_cmdSGACalc_Click:
    > MsgBox Err.Description
    > Resume
    > Resume Exit_cmdSGACalc_Click
    >
    > After the msgbox line, write resume, and step the code by pressing F8 key,
    > you will be return to the line with the error, check the field name, and
    > check if it exist
    >
    > --
    > The next line is only relevant to Microsoft''s web-based interface users.
    > If I answered your question, please mark it as an answer. It''s useful to
    > know that my answer was helpful
    > HTH, good luck
    >
    >
    > "Brandon" wrote:
    >
    > > Hello I am using this code below to take a total savings number, break it out
    > > by percentages and populate the subform based on these values. However I get
    > > an error for "Item not found in the collection" when it is running through
    > > the .AddNew Function. Why does this happen?
    > > Thanks for any assistance!
    > > Brandon
    > >
    > > Private Sub cmdSGACalc_Click()
    > > On Error GoTo Err_cmdSGACalc_Click
    > >
    > > Dim curSavingsByMonth As Currency
    > > Dim intMonth As Integer
    > > Dim x As Integer
    > > Dim intDivisionID As Integer
    > > Dim intLocationID As Integer
    > > Dim curHistoricVolume As Currency
    > > Dim curLowBidAmt As Currency
    > > Dim curImplementedBidAmt As Currency
    > > Dim intProjectID As Integer
    > > Dim intProjectType As Integer
    > > Dim intCurrencyID As Integer
    > > Dim intLotNumber As Integer
    > > Dim curDivisionPercent(1 To 4) As Currency
    > >
    > > curDivisionPercent(1) = 0.254
    > > 'BI
    > > curDivisionPercent(2) = 0.176
    > > 'PM
    > > curDivisionPercent(3) = 0.35
    > > 'EPG
    > > curDivisionPercent(4) = 0.22
    > > 'RSG
    > >
    > > intDivisionID = 1
    > > x = 1
    > > intProjectID = Me!ProjectID.Value
    > > intLotNumber = Me!LotNumber.Value
    > > intLocationID = 52 'North America
    > > curHistoricVolume = Me!SGAHistoricVolume.Value
    > > curLowBidAmt = Me!SGALowBidAmt.Value
    > > curImplementedBidAmt = Me!SGAImplementedBidAmt.Value
    > > intProjectType = Me!SGAProjectTypeID.ItemData(1)
    > > intCurrencyID = Me!SGACurrencyID.ItemData(1)
    > > Me!sfrmLotBreakout.SetFocus
    > > Me.sfrmLotBreakout.CostAvoidance.SetFocus
    > >
    > > With Me!sfrmLotBreakout.Form.RecordsetClone
    > > For x = 1 To 4
    > > 'calculate the monthly savings and add records
    > > 'to the subform Brandon Cheal 7/14/04
    > > .AddNew
    > > !ProjectTypeID = intProjectType
    > > !CurrencyID = intCurrencyID
    > > !LotNumber = intLotNumber
    > > !DivisionID = intDivisionID
    > > !LocationID = intLocationID
    > > !HistoricVolume = curHistoricVolume *
    > > curDivisionPercent(intDivisionID)
    > > !LowBidAmt = curLowBidAmt *
    > > curDivisionPercent(intDivisionID)
    > > !ImplementedBidAmt = curImplementedBidAmt *
    > > curDivisionPercent(intDivisionID)
    > > !ProjectID = intProjectID
    > > .Update
    > > intDivisionID = intDivisionID + 1
    > > Next x
    > > End With
    > >
    > > Exit_cmdSGACalc_Click:
    > > Exit Sub
    > >
    > > Err_cmdSGACalc_Click:
    > > MsgBox Err.Description
    > > Resume Exit_cmdSGACalc_Click
    > >
    > >
    > > End Sub
     
  5. Brandon

    Brandon
    Expand Collapse
    Guest

    How do I check if the field exists in the immediate window?

    "Ofer" wrote:

    > You get this error when you try to assign a value or get a value from a
    > field that doesnt exist
    >
    > When you get the error, press Ctrl+Break, the code will stop in your error
    > trap
    > Err_cmdSGACalc_Click:
    > MsgBox Err.Description
    > Resume
    > Resume Exit_cmdSGACalc_Click
    >
    > After the msgbox line, write resume, and step the code by pressing F8 key,
    > you will be return to the line with the error, check the field name, and
    > check if it exist
    >
    > --
    > The next line is only relevant to Microsoft''s web-based interface users.
    > If I answered your question, please mark it as an answer. It''s useful to
    > know that my answer was helpful
    > HTH, good luck
    >
    >
    > "Brandon" wrote:
    >
    > > Hello I am using this code below to take a total savings number, break it out
    > > by percentages and populate the subform based on these values. However I get
    > > an error for "Item not found in the collection" when it is running through
    > > the .AddNew Function. Why does this happen?
    > > Thanks for any assistance!
    > > Brandon
    > >
    > > Private Sub cmdSGACalc_Click()
    > > On Error GoTo Err_cmdSGACalc_Click
    > >
    > > Dim curSavingsByMonth As Currency
    > > Dim intMonth As Integer
    > > Dim x As Integer
    > > Dim intDivisionID As Integer
    > > Dim intLocationID As Integer
    > > Dim curHistoricVolume As Currency
    > > Dim curLowBidAmt As Currency
    > > Dim curImplementedBidAmt As Currency
    > > Dim intProjectID As Integer
    > > Dim intProjectType As Integer
    > > Dim intCurrencyID As Integer
    > > Dim intLotNumber As Integer
    > > Dim curDivisionPercent(1 To 4) As Currency
    > >
    > > curDivisionPercent(1) = 0.254
    > > 'BI
    > > curDivisionPercent(2) = 0.176
    > > 'PM
    > > curDivisionPercent(3) = 0.35
    > > 'EPG
    > > curDivisionPercent(4) = 0.22
    > > 'RSG
    > >
    > > intDivisionID = 1
    > > x = 1
    > > intProjectID = Me!ProjectID.Value
    > > intLotNumber = Me!LotNumber.Value
    > > intLocationID = 52 'North America
    > > curHistoricVolume = Me!SGAHistoricVolume.Value
    > > curLowBidAmt = Me!SGALowBidAmt.Value
    > > curImplementedBidAmt = Me!SGAImplementedBidAmt.Value
    > > intProjectType = Me!SGAProjectTypeID.ItemData(1)
    > > intCurrencyID = Me!SGACurrencyID.ItemData(1)
    > > Me!sfrmLotBreakout.SetFocus
    > > Me.sfrmLotBreakout.CostAvoidance.SetFocus
    > >
    > > With Me!sfrmLotBreakout.Form.RecordsetClone
    > > For x = 1 To 4
    > > 'calculate the monthly savings and add records
    > > 'to the subform Brandon Cheal 7/14/04
    > > .AddNew
    > > !ProjectTypeID = intProjectType
    > > !CurrencyID = intCurrencyID
    > > !LotNumber = intLotNumber
    > > !DivisionID = intDivisionID
    > > !LocationID = intLocationID
    > > !HistoricVolume = curHistoricVolume *
    > > curDivisionPercent(intDivisionID)
    > > !LowBidAmt = curLowBidAmt *
    > > curDivisionPercent(intDivisionID)
    > > !ImplementedBidAmt = curImplementedBidAmt *
    > > curDivisionPercent(intDivisionID)
    > > !ProjectID = intProjectID
    > > .Update
    > > intDivisionID = intDivisionID + 1
    > > Next x
    > > End With
    > >
    > > Exit_cmdSGACalc_Click:
    > > Exit Sub
    > >
    > > Err_cmdSGACalc_Click:
    > > MsgBox Err.Description
    > > Resume Exit_cmdSGACalc_Click
    > >
    > >
    > > End Sub
     
  6. Ofer

    Ofer
    Expand Collapse
    Guest

    Type

    For sub form
    ?Forms![FormName]![SubFormControlName].form![FieldName]
    For form
    ?Forms![FormName]![FieldName]

    It could be that the field name doesn't exist in the table

    --
    The next line is only relevant to Microsoft''s web-based interface users.
    If I answered your question, please mark it as an answer. It''s useful to
    know that my answer was helpful
    HTH, good luck


    "Brandon" wrote:

    > How do I check if the field exists in the immediate window?
    >
    > "Ofer" wrote:
    >
    > > You get this error when you try to assign a value or get a value from a
    > > field that doesnt exist
    > >
    > > When you get the error, press Ctrl+Break, the code will stop in your error
    > > trap
    > > Err_cmdSGACalc_Click:
    > > MsgBox Err.Description
    > > Resume
    > > Resume Exit_cmdSGACalc_Click
    > >
    > > After the msgbox line, write resume, and step the code by pressing F8 key,
    > > you will be return to the line with the error, check the field name, and
    > > check if it exist
    > >
    > > --
    > > The next line is only relevant to Microsoft''s web-based interface users.
    > > If I answered your question, please mark it as an answer. It''s useful to
    > > know that my answer was helpful
    > > HTH, good luck
    > >
    > >
    > > "Brandon" wrote:
    > >
    > > > Hello I am using this code below to take a total savings number, break it out
    > > > by percentages and populate the subform based on these values. However I get
    > > > an error for "Item not found in the collection" when it is running through
    > > > the .AddNew Function. Why does this happen?
    > > > Thanks for any assistance!
    > > > Brandon
    > > >
    > > > Private Sub cmdSGACalc_Click()
    > > > On Error GoTo Err_cmdSGACalc_Click
    > > >
    > > > Dim curSavingsByMonth As Currency
    > > > Dim intMonth As Integer
    > > > Dim x As Integer
    > > > Dim intDivisionID As Integer
    > > > Dim intLocationID As Integer
    > > > Dim curHistoricVolume As Currency
    > > > Dim curLowBidAmt As Currency
    > > > Dim curImplementedBidAmt As Currency
    > > > Dim intProjectID As Integer
    > > > Dim intProjectType As Integer
    > > > Dim intCurrencyID As Integer
    > > > Dim intLotNumber As Integer
    > > > Dim curDivisionPercent(1 To 4) As Currency
    > > >
    > > > curDivisionPercent(1) = 0.254
    > > > 'BI
    > > > curDivisionPercent(2) = 0.176
    > > > 'PM
    > > > curDivisionPercent(3) = 0.35
    > > > 'EPG
    > > > curDivisionPercent(4) = 0.22
    > > > 'RSG
    > > >
    > > > intDivisionID = 1
    > > > x = 1
    > > > intProjectID = Me!ProjectID.Value
    > > > intLotNumber = Me!LotNumber.Value
    > > > intLocationID = 52 'North America
    > > > curHistoricVolume = Me!SGAHistoricVolume.Value
    > > > curLowBidAmt = Me!SGALowBidAmt.Value
    > > > curImplementedBidAmt = Me!SGAImplementedBidAmt.Value
    > > > intProjectType = Me!SGAProjectTypeID.ItemData(1)
    > > > intCurrencyID = Me!SGACurrencyID.ItemData(1)
    > > > Me!sfrmLotBreakout.SetFocus
    > > > Me.sfrmLotBreakout.CostAvoidance.SetFocus
    > > >
    > > > With Me!sfrmLotBreakout.Form.RecordsetClone
    > > > For x = 1 To 4
    > > > 'calculate the monthly savings and add records
    > > > 'to the subform Brandon Cheal 7/14/04
    > > > .AddNew
    > > > !ProjectTypeID = intProjectType
    > > > !CurrencyID = intCurrencyID
    > > > !LotNumber = intLotNumber
    > > > !DivisionID = intDivisionID
    > > > !LocationID = intLocationID
    > > > !HistoricVolume = curHistoricVolume *
    > > > curDivisionPercent(intDivisionID)
    > > > !LowBidAmt = curLowBidAmt *
    > > > curDivisionPercent(intDivisionID)
    > > > !ImplementedBidAmt = curImplementedBidAmt *
    > > > curDivisionPercent(intDivisionID)
    > > > !ProjectID = intProjectID
    > > > .Update
    > > > intDivisionID = intDivisionID + 1
    > > > Next x
    > > > End With
    > > >
    > > > Exit_cmdSGACalc_Click:
    > > > Exit Sub
    > > >
    > > > Err_cmdSGACalc_Click:
    > > > MsgBox Err.Description
    > > > Resume Exit_cmdSGACalc_Click
    > > >
    > > >
    > > > End Sub
     
  7. Brandon

    Brandon
    Expand Collapse
    Guest

    The fields are all both on the table and on the subform. the controls have
    the same name as the fieldnames, so I don't understand why I receive this
    error.

    "Ofer" wrote:

    > Type
    >
    > For sub form
    > ?Forms![FormName]![SubFormControlName].form![FieldName]
    > For form
    > ?Forms![FormName]![FieldName]
    >
    > It could be that the field name doesn't exist in the table
    >
    > --
    > The next line is only relevant to Microsoft''s web-based interface users.
    > If I answered your question, please mark it as an answer. It''s useful to
    > know that my answer was helpful
    > HTH, good luck
    >
    >
    > "Brandon" wrote:
    >
    > > How do I check if the field exists in the immediate window?
    > >
    > > "Ofer" wrote:
    > >
    > > > You get this error when you try to assign a value or get a value from a
    > > > field that doesnt exist
    > > >
    > > > When you get the error, press Ctrl+Break, the code will stop in your error
    > > > trap
    > > > Err_cmdSGACalc_Click:
    > > > MsgBox Err.Description
    > > > Resume
    > > > Resume Exit_cmdSGACalc_Click
    > > >
    > > > After the msgbox line, write resume, and step the code by pressing F8 key,
    > > > you will be return to the line with the error, check the field name, and
    > > > check if it exist
    > > >
    > > > --
    > > > The next line is only relevant to Microsoft''s web-based interface users.
    > > > If I answered your question, please mark it as an answer. It''s useful to
    > > > know that my answer was helpful
    > > > HTH, good luck
    > > >
    > > >
    > > > "Brandon" wrote:
    > > >
    > > > > Hello I am using this code below to take a total savings number, break it out
    > > > > by percentages and populate the subform based on these values. However I get
    > > > > an error for "Item not found in the collection" when it is running through
    > > > > the .AddNew Function. Why does this happen?
    > > > > Thanks for any assistance!
    > > > > Brandon
    > > > >
    > > > > Private Sub cmdSGACalc_Click()
    > > > > On Error GoTo Err_cmdSGACalc_Click
    > > > >
    > > > > Dim curSavingsByMonth As Currency
    > > > > Dim intMonth As Integer
    > > > > Dim x As Integer
    > > > > Dim intDivisionID As Integer
    > > > > Dim intLocationID As Integer
    > > > > Dim curHistoricVolume As Currency
    > > > > Dim curLowBidAmt As Currency
    > > > > Dim curImplementedBidAmt As Currency
    > > > > Dim intProjectID As Integer
    > > > > Dim intProjectType As Integer
    > > > > Dim intCurrencyID As Integer
    > > > > Dim intLotNumber As Integer
    > > > > Dim curDivisionPercent(1 To 4) As Currency
    > > > >
    > > > > curDivisionPercent(1) = 0.254
    > > > > 'BI
    > > > > curDivisionPercent(2) = 0.176
    > > > > 'PM
    > > > > curDivisionPercent(3) = 0.35
    > > > > 'EPG
    > > > > curDivisionPercent(4) = 0.22
    > > > > 'RSG
    > > > >
    > > > > intDivisionID = 1
    > > > > x = 1
    > > > > intProjectID = Me!ProjectID.Value
    > > > > intLotNumber = Me!LotNumber.Value
    > > > > intLocationID = 52 'North America
    > > > > curHistoricVolume = Me!SGAHistoricVolume.Value
    > > > > curLowBidAmt = Me!SGALowBidAmt.Value
    > > > > curImplementedBidAmt = Me!SGAImplementedBidAmt.Value
    > > > > intProjectType = Me!SGAProjectTypeID.ItemData(1)
    > > > > intCurrencyID = Me!SGACurrencyID.ItemData(1)
    > > > > Me!sfrmLotBreakout.SetFocus
    > > > > Me.sfrmLotBreakout.CostAvoidance.SetFocus
    > > > >
    > > > > With Me!sfrmLotBreakout.Form.RecordsetClone
    > > > > For x = 1 To 4
    > > > > 'calculate the monthly savings and add records
    > > > > 'to the subform Brandon Cheal 7/14/04
    > > > > .AddNew
    > > > > !ProjectTypeID = intProjectType
    > > > > !CurrencyID = intCurrencyID
    > > > > !LotNumber = intLotNumber
    > > > > !DivisionID = intDivisionID
    > > > > !LocationID = intLocationID
    > > > > !HistoricVolume = curHistoricVolume *
    > > > > curDivisionPercent(intDivisionID)
    > > > > !LowBidAmt = curLowBidAmt *
    > > > > curDivisionPercent(intDivisionID)
    > > > > !ImplementedBidAmt = curImplementedBidAmt *
    > > > > curDivisionPercent(intDivisionID)
    > > > > !ProjectID = intProjectID
    > > > > .Update
    > > > > intDivisionID = intDivisionID + 1
    > > > > Next x
    > > > > End With
    > > > >
    > > > > Exit_cmdSGACalc_Click:
    > > > > Exit Sub
    > > > >
    > > > > Err_cmdSGACalc_Click:
    > > > > MsgBox Err.Description
    > > > > Resume Exit_cmdSGACalc_Click
    > > > >
    > > > >
    > > > > End Sub
     
  8. Brandon

    Brandon
    Expand Collapse
    Guest

    Thanks, I got it working! It was a field name change that got me! Thanks
    for your help!

    "Ofer" wrote:

    > Type
    >
    > For sub form
    > ?Forms![FormName]![SubFormControlName].form![FieldName]
    > For form
    > ?Forms![FormName]![FieldName]
    >
    > It could be that the field name doesn't exist in the table
    >
    > --
    > The next line is only relevant to Microsoft''s web-based interface users.
    > If I answered your question, please mark it as an answer. It''s useful to
    > know that my answer was helpful
    > HTH, good luck
    >
    >
    > "Brandon" wrote:
    >
    > > How do I check if the field exists in the immediate window?
    > >
    > > "Ofer" wrote:
    > >
    > > > You get this error when you try to assign a value or get a value from a
    > > > field that doesnt exist
    > > >
    > > > When you get the error, press Ctrl+Break, the code will stop in your error
    > > > trap
    > > > Err_cmdSGACalc_Click:
    > > > MsgBox Err.Description
    > > > Resume
    > > > Resume Exit_cmdSGACalc_Click
    > > >
    > > > After the msgbox line, write resume, and step the code by pressing F8 key,
    > > > you will be return to the line with the error, check the field name, and
    > > > check if it exist
    > > >
    > > > --
    > > > The next line is only relevant to Microsoft''s web-based interface users.
    > > > If I answered your question, please mark it as an answer. It''s useful to
    > > > know that my answer was helpful
    > > > HTH, good luck
    > > >
    > > >
    > > > "Brandon" wrote:
    > > >
    > > > > Hello I am using this code below to take a total savings number, break it out
    > > > > by percentages and populate the subform based on these values. However I get
    > > > > an error for "Item not found in the collection" when it is running through
    > > > > the .AddNew Function. Why does this happen?
    > > > > Thanks for any assistance!
    > > > > Brandon
    > > > >
    > > > > Private Sub cmdSGACalc_Click()
    > > > > On Error GoTo Err_cmdSGACalc_Click
    > > > >
    > > > > Dim curSavingsByMonth As Currency
    > > > > Dim intMonth As Integer
    > > > > Dim x As Integer
    > > > > Dim intDivisionID As Integer
    > > > > Dim intLocationID As Integer
    > > > > Dim curHistoricVolume As Currency
    > > > > Dim curLowBidAmt As Currency
    > > > > Dim curImplementedBidAmt As Currency
    > > > > Dim intProjectID As Integer
    > > > > Dim intProjectType As Integer
    > > > > Dim intCurrencyID As Integer
    > > > > Dim intLotNumber As Integer
    > > > > Dim curDivisionPercent(1 To 4) As Currency
    > > > >
    > > > > curDivisionPercent(1) = 0.254
    > > > > 'BI
    > > > > curDivisionPercent(2) = 0.176
    > > > > 'PM
    > > > > curDivisionPercent(3) = 0.35
    > > > > 'EPG
    > > > > curDivisionPercent(4) = 0.22
    > > > > 'RSG
    > > > >
    > > > > intDivisionID = 1
    > > > > x = 1
    > > > > intProjectID = Me!ProjectID.Value
    > > > > intLotNumber = Me!LotNumber.Value
    > > > > intLocationID = 52 'North America
    > > > > curHistoricVolume = Me!SGAHistoricVolume.Value
    > > > > curLowBidAmt = Me!SGALowBidAmt.Value
    > > > > curImplementedBidAmt = Me!SGAImplementedBidAmt.Value
    > > > > intProjectType = Me!SGAProjectTypeID.ItemData(1)
    > > > > intCurrencyID = Me!SGACurrencyID.ItemData(1)
    > > > > Me!sfrmLotBreakout.SetFocus
    > > > > Me.sfrmLotBreakout.CostAvoidance.SetFocus
    > > > >
    > > > > With Me!sfrmLotBreakout.Form.RecordsetClone
    > > > > For x = 1 To 4
    > > > > 'calculate the monthly savings and add records
    > > > > 'to the subform Brandon Cheal 7/14/04
    > > > > .AddNew
    > > > > !ProjectTypeID = intProjectType
    > > > > !CurrencyID = intCurrencyID
    > > > > !LotNumber = intLotNumber
    > > > > !DivisionID = intDivisionID
    > > > > !LocationID = intLocationID
    > > > > !HistoricVolume = curHistoricVolume *
    > > > > curDivisionPercent(intDivisionID)
    > > > > !LowBidAmt = curLowBidAmt *
    > > > > curDivisionPercent(intDivisionID)
    > > > > !ImplementedBidAmt = curImplementedBidAmt *
    > > > > curDivisionPercent(intDivisionID)
    > > > > !ProjectID = intProjectID
    > > > > .Update
    > > > > intDivisionID = intDivisionID + 1
    > > > > Next x
    > > > > End With
    > > > >
    > > > > Exit_cmdSGACalc_Click:
    > > > > Exit Sub
    > > > >
    > > > > Err_cmdSGACalc_Click:
    > > > > MsgBox Err.Description
    > > > > Resume Exit_cmdSGACalc_Click
    > > > >
    > > > >
    > > > > End Sub
     

Share This Page