apped2table function for adding an item not already in the list.

[AccessBeetle]

I am using this "append2tab le" function from Allen Browne to add an item to the combobox that is not already in the list.
Here is the function, with my fields and table names

Function Append2Table(cbo As ComboBox, NewData As Variant) As Integer
On Error GoTo Err_Append2Table
' Purpose:   Append NotInList value to combo's recordset.
' Assumes:   ControlSource of combo has the same name as the foreign key field.
' 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 ProjectID As Variant      ' Name of the field to append to.
 
    Append2Table = acDataErrContinue
    ProjectID = cbo.ControlSource
    If Not (IsNull(ProjectID) Or IsNull(NewData)) Then
        sMsg = "Do you wish to add the entry " & NewData & " for " & cbo.Name & "?"
        If MsgBox(sMsg, vbOKCancel + vbQuestion, "Add new value?") = vbOK Then
            Set rst = CurrentDb.OpenRecordset("qry_SubmissionActive", dbOpenDynaset, dbSeeChanges)
            rst.AddNew
                          rst(ProjectDescription) = 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
 

ProjectID is an Autonumber and ProjectDescript ion is a string.
I want to add an item to the field ProjectDescript ion.
Error: Item not found in this collection.
Any ideas?

[ajalwaysus]

I am not sure, but don't you need to wrap double quotes around it.

Like:

rst("ProjectDescription") = NewData

Let us know,
-AJ

[ADezii]

Personally, if you don't care whether or not this code is reusable, you can reduce the code and make it more readable. Assuming a Table Name of tblProject, and a Combo Box named cboProjects, which consists of a Single Bound Column whose Control Source is [ProjectDescript ion], then the code would be:

Private Sub cboProjects_NotInList(NewData As String, Response As Integer)
Dim rst As DAO.Recordset
Dim sMsg As String
 
Response = acDataErrContinue
 
If Not IsNull(NewData) Then
  sMsg = "Do you wish to add the entry " & NewData & " for " & Me![cboProjects].Name & "?"
    If MsgBox(sMsg, vbOKCancel + vbQuestion, "Add new value?") = vbOK Then
      Set rst = CurrentDb.OpenRecordset("tblProject", dbOpenDynaset)
        rst.AddNew
          rst!ProjectDescription = NewData
        rst.Update
          rst.Close: Set rst = Nothing
          Response = acDataErrAdded
    End If
End If
End Sub

NOTE: The Limit To List Property of cboProjects must be set to Yes.

[AccessBeetle]

ajalwaysus,

I get an error saying "Field cannot be updated" if I make change you suggested.

Adezil,

Tried the limited code, once mre. the error is now "ODBC call failed"
Note:comboboxna me: cboProjectID, The records are to be inserted into "qry_Submission Active" .

still looking for the answer.

[topher23]

I'd check qry_SubmissionA ctive to make sure it's actually updatable. Open it in datasheet view, and if you can't add anything there, add your new data to the underlying table rather than the query.

[ajalwaysus]

The error you are getting using my code is most likely because your query is not "updateable ". An easy way to test that is to go into your query and try to manually add a record. Let me know f this is the issue,and if it is, then you need a better way of getting your data.

EDIT: @topher23
Ditto =)

-AJ

[AccessBeetle]

Completely missed out that part. You both are correct. The query is not Updateable. So I replace that query with one of underlying table (tbl_Submission ) which has the field ProjectDescript ion. And now I am getting "ODBC call failed" error.
P.S: I am using linked tables in access. DB actually is sitting under SQL server 2005. May be this is the problem.
I run the
"Step into" and the error occurs at this step:
rst.update

What should be the solution??

[topher23]

Well, Beetle, I think you've found your problem. This code is using DAO to create and update the recordset, whereas you need to use ADO to connect properly to SQL server.

If you're going to be using a back-end other than Jet (.mdb) or 2007's Access Engine (.accdb) you're going to need to learn about ADO. Check out w3schools.com, they have a free online course on it. http://www.w3schools.com/ado/default.asp

I'd love to give you a quick and simple answer, but I know very little about ADO, as I have no access to a server for my applications (blasted corporate IT boneheads!).

[ADezii]

Equivalent ADO Code would be (substitute your Combo Box Name for cboProjects):

Private Sub cboProjects_NotInList(NewData As String, Response As Integer)
Dim rst As ADODB.Recordset
Dim sMsg As String
 
Set rst = New ADODB.Recordset
 
Response = acDataErrContinue
 
If Not IsNull(NewData) Then
  sMsg = "Do you wish to add the entry " & NewData & " for " & Me![cboProjects].Name & "?"
    If MsgBox(sMsg, vbOKCancel + vbQuestion, "Add new value?") = vbOK Then
      With rst
        .Source = "tbl_Submission"
        .ActiveConnection = CurrentProject.Connection
        .CursorType = adOpenKeyset
        .LockType = adLockOptimistic
          .Open
 
        .AddNew
          !ProjectDescription = NewData
        .Update
          rst.Close: Set rst = Nothing
          Response = acDataErrAdded
      End With
    End If
End If
End Sub

1. Does tbl_Submission have other, Required Fields, besides [ProjectDescript ion]?
2. Are you positive the Bound, Displayed Column in your Combo is [ProjectDescript ion]?

[AccessBeetle]

Private Sub cboProjectID_NotInList(NewData As String, Response As Integer)
Dim conn As New ADODB.Connection
Dim rst As ADODB.Recordset
Dim sMsg As String
 
Set conn = CurrentProject.Connection
 
Response = acDataErrContinue
 
If Not IsNull(NewData) Then
  sMsg = "Do you wish to add the entry " & NewData & " for " & Me![cboProjectID].Name & "?"
    If MsgBox(sMsg, vbOKCancel + vbQuestion, "Add new value?") = vbOK Then
    rst.Open "qry_SubmissionActive", conn, adOpenDynamic, adLockOptimistic
 
 Do While Not rst.EOF
 
        rst.Fields("ProjectDescription") = NewData
        rst.Update
        Response = acDataErrAdded
 
 Loop
        rst.Close
End If
End If
End Sub
 

Ok I am trying to write code for Adodb. but getting runtime error 91: Object Variable not set.
any ideas?

adezil: may be we chriscrossed in replying let me try your code and I will let you know.
thanks for the help.