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

Expand|Select|Wrap|Line Numbers

 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:

Expand|Select|Wrap|Line Numbers

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:

Expand|Select|Wrap|Line Numbers

 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:

Quote:

Originally Posted by 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.

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):

Expand|Select|Wrap|Line Numbers

 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

Does tbl_Submission have other, Required Fields, besides [ProjectDescription]?
Are you positive the Bound, Displayed Column in your Combo is [ProjectDescription]?

AccessBeetle

Expand|Select|Wrap|Line Numbers

 
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 =

Expand|Select|Wrap|Line Numbers

 SELECT qry_SubmissionActive.ProjectID, qry_SubmissionActive.PropertyName & "  " & qry_SubmissionActive.ProjectDescription AS Property

FROM qry_SubmissionActive

ORDER BY qry_SubmissionActive.PropertyName, qry_SubmissionActive.ProjectDescription;

ADezii

Quote:

Originally Posted by 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 =

in your Combo Box:

What is your Bound Column?
What is the Column Count?
What are the Column Widths?
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

Expand|Select|Wrap|Line Numbers

Do While Not rst.EOF

which doesn't really make sense.

NeoPa

Quote:

Originally Posted by 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.

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

Expand|Select|Wrap|Line Numbers

 .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

Expand|Select|Wrap|Line Numbers

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?

Expand|Select|Wrap|Line Numbers

 
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

Quote:

Originally Posted by topher23

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.

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.

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Similar Microsoft Access / VBA bytes

/bytes/development

/bytes/it

/bytes/about