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?
24 2207
I am not sure, but don't you need to wrap double quotes around it.
Like: - rst("ProjectDescription") = NewData
Let us know,
-AJ
ADezii 8,834
Recognized Expert Expert
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.
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 234
Recognized Expert New Member
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.
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
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 234
Recognized Expert New Member
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 8,834
Recognized Expert Expert
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
- Does tbl_Submission have other, Required Fields, besides [ProjectDescript ion]?
- Are you positive the Bound, Displayed Column in your Combo is [ProjectDescript ion]?
-
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.
Sign in to post your reply or Sign up for a free account.
Similar topics |
by: Ken Fine |
last post by:
I have a menu system that has nodes that can be opened or closed. In an
effort to make my code more manageable, I programmed a little widget tonight
that keeps track of the open/active item and automatically builds
querystrings for my redirect URLS. The code for this follows. It defines an
ASP Dictionary object, and key/value pairs for each, and builds appropriate
querystrings based on comparison with a status variable.
The way it works...
|
by: Nils Petter Vaskinn |
last post by:
I'm using an enum that's declared within a function (since I only need it
within that function.)
I can't find anything about this in "The C++ Programming Language" by
Stroustroup and I don't have the standard.
Is this legal?
// test.cpp
#include <iostream>
|
by: Ryan Ternier |
last post by:
I have a section of my project that is Driving me nuts.
No one has been able to help that much on it, so i thought of posting it
here in hopes someone could help.
I need to print out an Ordered list.
Ex.
1. Something
|
by: Simon Jefferies |
last post by:
Hello,
I am trying to add an item to a checked list box, like:
clbList.Items.add("Hello",true)
I get an error back:
Run-time exception thrown: System.ArgumentOutOfRangeException - Specified
argument was out of the range of valid values. Parameter name: '-1' is not a
|
by: Kevin Blount |
last post by:
I need to create a page that lists a users Content, and allow them to
add subscribers to or remove subscribers from that Content. The list may
contain just one Content item, but it could just as easily contain 10
Content items.
Each Content item would look something like this:
---------------------------------------------
<content title 1>
<content description 1>
| |
by: hanseymoon |
last post by:
Dear newsgroup:
I've got this long function, which works good overall to spell check
words from a dictionary and I am not in a position to replace it. Can
someone please see where or how it might be chopping up words like:
don't. It brings them back as: 't, chopping off the "don" before the
apostrophe. I've looked over the whole situation and ran many $string
tests....and it appears to be narrowed it down to this. I may be wrong
and...
|
by: Tony Lownds |
last post by:
(Note: PEPs in the 3xxx number range are intended for Python 3000)
PEP: 3107
Title: Function Annotations
Version: $Revision: 53169 $
Last-Modified: $Date: 2006-12-27 20:59:16 -0800 (Wed, 27 Dec 2006) $
Author: Collin Winter <collinw@gmail.com>,
Tony Lownds <tony@lownds.com>
Status: Draft
Type: Standards Track
|
by: Adam |
last post by:
Hi,
I'd like to return an (arbitrary length) string array from a function so
that after calling the array I've got a list of strings I can access.
After much perusing of the internet I found a related answer here (by
Eric Sosman) which involved creating an array of pointers and using
that, so it looks something like:
|
by: =?Utf-8?B?TWFobW91ZCBTaGFiYW4=?= |
last post by:
i have a problem in adding new listbox items
i don't need to allow adding multible items with the same textvalue
ex: if current items are:
green
red
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look !
Part I. Meaning of...
|
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it.
First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
| |
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth.
The Art of Business Website Design
Your website is...
|
by: Hystou |
last post by:
Overview:
Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
|
by: agi2029 |
last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own....
Now, this would greatly impact the work of software developers. The idea...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules.
He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms.
Adolph will...
|
by: conductexam |
last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one.
At the time of converting from word file to html my equations which are in the word document file was convert into image.
Globals.ThisAddIn.Application.ActiveDocument.Select();...
|
by: TSSRALBI |
last post by:
Hello
I'm a network technician in training and I need your help.
I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs.
The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols.
I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
| |
by: muto222 |
last post by:
How can i add a mobile payment intergratation into php mysql website.
| |