Page 1 of 2 12 LastLast
Results 1 to 15 of 18
  1. #1
    Star Lounger
    Join Date
    Feb 2002
    Posts
    56
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Not in list (A2000)

    The following code is creating an error.

    Function Append2Table(cbo As ComboBox, NewData As Variant) As Integer
    On Error GoTo Err_Append2Table
    ' Purpose: Append NotInList value to combo's recordset.
    ' Return: acDataErrAdded if added, else acDataErrContinue
    ' Usage: Add this line to the combo's NotInList event procedure:
    ' Response = Append2Table(Me.MyCombo, NewData)
    Dim rst As Recordset
    Dim sMsg As String
    Dim vField As Variant ' Name of the field to append to.

    Append2Table = acDataErrContinue
    vField = cbo.ControlSource
    If Not (IsNull(vField) Or IsNull(NewData)) Then
    sMsg = "UnRecognised Entry, Do you wish to add " & NewData & " As A New " & cbo.Name & "?"
    If MsgBox(sMsg, vbOKCancel + vbQuestion, "Add new value?") = vbOK Then
    Set rst = CurrentDb.OpenRecordset(cbo.RowSource)
    rst.AddNew
    rst(vField) = NewData
    rst.Update
    rst.Close
    Append2Table = acDataErrAdded
    End If
    End If

    Exit_Append2Table:
    Set rst = Nothing
    Exit Function

    Err_Append2Table:
    MsgBox "Error " & Err.Number & ": " & Err.DESCRIPTION, vbInformation, "Append2Table()"
    Resume Exit_Append2Table
    End Function

    The combo, has this in the not in list event.

    Response = Append2Table(Me![Item], NewData)

    It isn't working for some reason, run time error 13, type mismatch.
    Does any one know a remedy for this.
    This works in A97 and I,ve checked the references.
    Just an after thought, The combo in question looks for the data from a lookup table, tblEstimateItems with the following SQL:

    SELECT [tblEstimateItems].[EstimateItems], [tblEstimateItems].[code] FROM tblEstimateItems;

    Dave

  2. #2
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Sint Niklaas, Belgium
    Posts
    2,778
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Not in list (A2000)

    This will only work if the control source of the combo is a field with the same name and type as the field displayed in the combobox. If you are working with an ID field, this will not work.
    Francois

  3. #3
    Silver Lounger
    Join Date
    Jun 2001
    Location
    Niagara Falls, New York, USA
    Posts
    1,878
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Not in list (A2000)

    Hi Dave

    I like your reusable code to handle new combo entries.

    Let me know when you get it working with a2k and what the problem was.

    Wondering. What strategy do you use to allow an end user to fix a typo they previously added to the combo list?

    Thanks, John

  4. #4
    Star Lounger
    Join Date
    Dec 2001
    Location
    Fredensborg, Denmark
    Posts
    86
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Not in list (A2000)

    You are very close to have a working solution.

    In the Append2Table function perform the following changes:

    Replace line

  5. #5
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Sint Niklaas, Belgium
    Posts
    2,778
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Not in list (A2000)

    Claus,

    I don't think this will work if the combo is working with an invisible ID field.
    rst.fields(cbo.BoundColumn) will give the Field containing the ID.
    NewData contains the displayed data.
    Francois

  6. #6
    Star Lounger
    Join Date
    Dec 2001
    Location
    Fredensborg, Denmark
    Posts
    86
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Not in list (A2000)

    Francois,

    Your statement is false. The field numbered 1 is the second field in the recordset.

    But I admit being a bit clumsy.

    I should have noted that I assume that we are dealing with 2-field lookup tables only, where the ID is the 1st field and the displayed data are the 2nd field.

    Then it does indeed work, but there is no reason to use the BoundColumn property.

    One should use rst.fields(1)=NewData instead of using the BoundColumn, since the displayed field is numbered 1 in the recordset.

  7. #7
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Sint Niklaas, Belgium
    Posts
    2,778
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Not in list (A2000)

    Your right, I forgot the first field in a recordset is fields(0).
    You have also to assume the ID field is an Autonumber.
    Francois

  8. #8
    Star Lounger
    Join Date
    Feb 2002
    Posts
    56
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Not in list (A2000)

    You're all confusing me now, here's all the code.
    Thanks all for the help, I feel I am so close but can't fathom out how to reach the end.

    This code works in A97 but not in A2000

    Private Sub cmbCode_NotInList(NewData As String, Response As Integer)
    Response = Append2Table(Me![Item], NewData)
    End Sub

    and this is the module.

    Function Append2Table(cbo As ComboBox, NewData As Variant) As Integer
    On Error GoTo Err_Append2Table
    ' Purpose: Append NotInList value to combo's recordset.
    ' Return: acDataErrAdded if added, else acDataErrContinue
    ' Usage: Add this line to the combo's NotInList event procedure:
    ' Response = Append2Table(Me.MyCombo, NewData)
    Dim rst As Recordset
    Dim sMsg As String
    Dim vField As Variant ' Name of the field to append to.

    Append2Table = acDataErrContinue
    vField = cbo.ControlSource
    If Not (IsNull(vField) Or IsNull(NewData)) Then
    sMsg = "UnRecognised Entry, Do you wish to add " & NewData & " As A New " & cbo.NAME & "?"
    If MsgBox(sMsg, vbOKCancel + vbQuestion, "Add new value?") = vbOK Then
    Set rst = CurrentDb.OpenRecordset(cbo.RowSource)
    rst.AddNew
    rst(vField) = NewData
    rst.Update
    rst.Close
    Append2Table = acDataErrAdded
    End If
    End If

    Exit_Append2Table:
    Set rst = Nothing
    Exit Function

    Err_Append2Table:
    MsgBox "Error " & Err.Number & ": " & Err.DESCRIPTION, vbInformation, "Append2Table()"
    Resume Exit_Append2Table
    End Function

    The form frmEstimateDetails/sbfEstimateDetails/tblEstimateDetails has a combo.
    The combo uses tblEstimateItems for the look up.
    When I run this code from not in list I get type mismatch 13 error.

    It's never let me down in A97 and I understand the code needs converting to A2000.
    I'm stuck !!!

    Dave

  9. #9
    Star Lounger
    Join Date
    Dec 2001
    Location
    Fredensborg, Denmark
    Posts
    86
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Not in list (A2000)

    Dave,

    Our discussion on a more general solution may have confused you

  10. #10
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Not in list (A2000)

    Continuing on the ideas of Claus and Francois: I think it will be very hard to write a function that handles the NotInList event in *all* occasions:
    1. <LI>Often, it will not be enough to add a value in just one field of the row source of the combo box. In those cases, you must either set the other fields in code, or display a custom form in which users can enter all required fields.
      <LI>Depending on the way you build the combo box, the displayed value may correspond to the first, second, third, ... field in the row source.
      <LI>The name of the control source of the combo box is not necessarily the same as the name of the corresponding field in the row source.
    In the relatively simple situation where you have a lookup table in which just the one field needs to be added, you could use your code with some modifications based on the suggestions of Claus. I see no reason why it shouldn't work in Access 2000.

    In the code below, I have removed vfield because of the third problem mentioned above.

    I have added an optional parameter Col that takes care of the second problem. It indicates the column number of the displayed value in the combo box (zero-based, so first column = 0, second column = 1 etc.) It would be possible to compute Col by parsing the ColumnWidths property of the combo box to find out the first column with non-zero width. But I have no time for that now, so I will leave that as an exercise to the reader (ha, I have always wanted to write that!).

    In the "standard" situation where the first column of the combo box is hidden (an autonumber ID field) and the second contains the displayed data, you can omit the Col parameter, because the default value is 1.

    Function Append2Table(cbo As ComboBox, NewData As Variant, Optional Col As Integer = 1) As Integer
    ' Purpose: Append NotInList value to combo's recordset.
    ' Return: acDataErrAdded if added, else acDataErrContinue
    ' Usage: Add this line to the combo's NotInList event procedure:
    ' Response = Append2Table(Me.MyCombo, NewData, 2)
    ' or
    ' Response = Append2Table(Me.MyCombo, NewData)

    Dim rst As DAO.Recordset
    Dim sMsg As String

    On Error GoTo Err_Append2Table
    Append2Table = acDataErrContinue
    vField = cbo.ControlSource
    If Not IsNull(NewData) Then
    sMsg = "UnRecognised Entry, Do you wish to add " & NewData & " As A New " & cbo.Name & "?"
    If MsgBox(sMsg, vbOKCancel + vbQuestion, "Add new value?") = vbOK Then
    Set rst = CurrentDb.OpenRecordset(cbo.RowSource)
    rst.AddNew
    rst.Fields(Col) = NewData
    rst.Update
    rst.Close
    Append2Table = acDataErrAdded
    End If
    End If

    Exit_Append2Table:
    Set rst = Nothing
    Exit Function

    Err_Append2Table:
    MsgBox "Error " & Err.Number & ": " & Err.Description, vbInformation, "Append2Table()"
    Resume Exit_Append2Table
    End Function

  11. #11
    Star Lounger
    Join Date
    Feb 2002
    Posts
    56
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Not in list (A2000)

    Thanks all, I am so close now I can smell it.

    the following IS working to a degree.

    Function Append2Table(cbo As ComboBox, NewData As Variant) As Integer
    'On Error GoTo Err_Append2Table
    ' Purpose: Append NotInList value to combo's recordset.
    ' Return: acDataErrAdded if added, else acDataErrContinue
    ' Usage: Add this line to the combo's NotInList event procedure:
    ' Response = Append2Table(Me.MyCombo, NewData)
    Dim rst As DAO.Recordset
    Dim sMsg As String
    Dim vField As Variant ' Name of the field to append to.

    Append2Table = acDataErrContinue
    vField = cbo.ControlSource
    If Not (IsNull(vField) Or IsNull(NewData)) Then
    sMsg = "UnRecognised Entry, Do you wish to add " & NewData & " As A New " & cbo.Name & "?"
    If MsgBox(sMsg, vbOKCancel + vbQuestion, "Add new value?") = vbOK Then
    Set rst = CurrentDb.OpenRecordset(cbo.RowSource)
    rst.AddNew
    rst.Fields(0) = NewData
    rst.Update
    rst.Close
    Append2Table = acDataErrAdded
    End If
    End If

    Exit_Append2Table:
    Set rst = Nothing
    Exit Function

    Err_Append2Table:
    MsgBox "Error " & Err.Number & ": " & Err.Description, vbInformation, "Append2Table()"
    Resume Exit_Append2Table
    End Function

    I've changed the following line:

    rst.Fields(1) = NewData
    to
    rst.Fields(0) = NewData

    This is now saving the code to the correct table, I've assumed judging by seeing the code work, that this deals with a single column combo.
    Mine is 2 columned, [Code] & [description]
    Whats happening is I enter the code, the DB doesn't recognise it and fires the append code above.
    It fills one column(0) but not the second.

    Dave

  12. #12
    3 Star Lounger
    Join Date
    Feb 2001
    Location
    NYC,USA,Earth
    Posts
    273
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Not in list (A2000)

    Could you use an inputbox statement to prompt the user for a description value? Then set the results to rst.fields(1).

  13. #13
    Star Lounger
    Join Date
    Feb 2002
    Posts
    56
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Not in list (A2000)

    Good Idea, Paul.

    How would this be done.

    Sorry to be a pain.

    Dave

  14. #14
    3 Star Lounger
    Join Date
    Feb 2001
    Location
    NYC,USA,Earth
    Posts
    273
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Not in list (A2000)

    Something like this:

    Dim vPrompt as Variant

    vPrompt = InputBox("Please enter a description for this code", "Need a Description")
    rst.fields(1) = vPrompt

    This is simplistic but you will need to check the vPrompt value for an empty string in case the user hits the Cancel button or doesn't enter anything and hits the OK button. This will work for this situation but may not work for all of your combo's.

  15. #15
    Star Lounger
    Join Date
    Feb 2002
    Posts
    56
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Not in list (A2000)

    Absolutly Perfect.
    Thanks all,
    Much appreciated.

    have a good un
    Dave

Page 1 of 2 12 LastLast

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •