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

[AccessBeetle]

I am using this "append2table" 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 ProjectDescription is a string.
I want to add an item to the field ProjectDescription.
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 [ProjectDescription], 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:comboboxname: cboProjectID, The records are to be inserted into "qry_SubmissionActive" .

still looking for the answer.

[topher23]

I'd check qry_SubmissionActive 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 ProjectDescription. 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 [ProjectDescription]?
2. Are you positive the Bound, Displayed Column in your Combo is [ProjectDescription]?

[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.

[AccessBeetle]

To Adezil,
I replaced it with your suggested code but the same error at [.Update] saying
ODBC call failed.
Yes, another required field in Project ID, but that is an autonumber

the combobox has rowsource =

SELECT qry_SubmissionActive.ProjectID, qry_SubmissionActive.PropertyName & "  " & qry_SubmissionActive.ProjectDescription AS Property
FROM qry_SubmissionActive
ORDER BY qry_SubmissionActive.PropertyName, qry_SubmissionActive.ProjectDescription;

[ADezii]

@AccessBeetle
in your Combo Box:

1. What is your Bound Column?
2. What is the Column Count?
3. What are the Column Widths?
4. If all else else fails,can you send me the Front End Database? I may be able to get a picture of what is going on.

[ChipR]

What is the & " " & doing in the rowsource? Should that be a comma?
Edit: Nevermind, I see what it's doing now.

[ChipR]

If you're still using the code in post number 10, you seem to have a

Do While Not rst.EOF

which doesn't really make sense.

[NeoPa]

@topher23
I don't mean to contradict, but this isn't technically true.

If a SQL Server table or cursor is linked to, then using DAO to the linked table should work fine.

If you wish to use a recordset connecting directly to the SQL server (or even a Pass-Thru query) then you certainly could ONLY use ADODB.

Thank you anyway for posting Topher. It's really good to see you getting involved :)

[NeoPa]

This thread does seem to be meandering somewhat & I'm not sure you've found your way yet.

It seems to me that once you have sorted out exactly what you want to add where, then adding it can be simply accomplished with some basic SQL, and ensuring it is included in the ComboBox can be done by calling ComboBox.Requery.

Does it need to be more complicated than this?

[AccessBeetle]

Sorry everyone for replying late. but the problem is still there and it is the same. the error I am getting is ODBC call failed. at

.AddNew
          !ProjectDescription = NewData
        .Update <-- Error here

Ok here is the answers for NeoPa's (** Edit: Read ADezii's **) questions.
1)bound colum is 1
2)the Column count is 2
3)Column width: 0";4.0625"
4) I might not be able to send the database. Sorry for that.

ChipR,
I tried

Do While Not rst.EOF 

this but nothing still same error.

Thanks everyone for help.

[ChipR]

My point was that it doesn't make sense that you have a Do While loop at all. What is the purpose of the loop? (not to mention that fact that it can never end)

[topher23]

NeoPa makes a good point. Could this be accomplished using code along these lines?

DOCmd.RunSQL "INSERT INTO tblProject (ProjectDescription) VALUES ('" & NewData & "')"
Me!cboProjects.Requery
 

Sorry for the misleading info about ADO. I was under the impression that whenever you used a DBMS server, ADO had to be used instead of DAO. My bad.

[AccessBeetle]

Oh ok. I thought you want me to try it. I am using code in post # 9. ya that does not make any sense though so I intitally did not have it.
Thanks

[NeoPa]

@topher23
No worries.

It's not far off. The one exception is when you use linked tables.

BTW. I doubt the OP is trying to be rude in ignoring so many posts. Many are simply too overwhelmed with the concepts that keeping focus on anything but the stuff that makes sense to them is difficult.

Most don't realise that a simple post explaining they're confused would bring a response of helpful explanations.

[AccessBeetle]

If anyone is looking at this thread I am still looking for an answers.
Adezil, Here is the answers for the questions you asked in post #12
1)bound colum is 1
2)the Column count is 2
3)Column width: 0";4.0625"
4) I might not be able to send the database. Sorry for that.
Error: ODBC call Failed
Thanks.

[NeoPa]

If you're still interested in a solution, then you should consider responding to all those who have already posted first.

Why would anyone go to the trouble of committing further time to your problem when you fail even to respond to their posts?

[AccessBeetle]

NeoPa,

Good Point.

I am not ignoring their answers. I am trying to put them into a try. But by doing that if something pops up in my mind then I also try to do that to fix it. Afterall I am after this continuously for more than week.

Anyways,

I already found the answer. The problem was one of the Concatenated (ProjectDescription+PropertyName)field which did not allowed to enter the data (Meaning other fields of that query allowed to enter or update). So I created one subform, which pops up when somebody wants to enter a data that is not in the list. The user has to enter Project Description and Property name separately and hit submit. Them Me.Requery does the refreshing work to the combobox and the newly entered value is appeared in the combobox. It was not easy but that is the only way I could figure it out.

Thanks again everyone for help.

[NeoPa]

I hear what you're saying, yet a simple post in response - even before you've fully tested it out - goes a long way to stop people feeling they're being ignored.

I'm pleased you found something in the end that works for you, but considering the similarity of your solution to what was suggested in post #16 (and repeated in post #19) it's clear that this could have been resolved much earlier with full interaction on your part. Your version has fleshed this out somewhat though, which is always good.