Behind the combo box, in the 'On Not in List' Event, create area behind the combo box, create a subroutine cboVendor_NotInList(NewData As String, Response As Integer), and include this code: (NOTE: this is code from Access 2000 Developer's Handbook, Volume I and I use it and it's great!)
The table and field that I use is tblVendors.VendorName. You will have to modify this code to include your table name and the field name. Try it. Hope this helps!
Private Sub cboVendor_NotInList(NewData As String, Response As Integer)
' From Access 2000 Developer's Handbook, Volume I
' by Getz, Litwin, and Gilbert (Sybex)
' Copyright 1999. All rights reserved.
' Amended on 03/06/2008
' EOP Budget and Fiscal database - VISA Statement
' Add new items to the table/field: tblVendors.VendorName
Dim strMsg As String
#If USEDAO Then
Dim rst As DAO.Recordset
Dim db As DAO.Database
#Else
Dim rst As ADODB.Recordset
#End If
strMsg = "'" & NewData & "' is not in the list. "
strMsg = strMsg & "Would you like to add it?"
If vbNo = MsgBox(strMsg, vbYesNo + vbQuestion, _
"New Vendor") Then
Response = acDataErrDisplay
Else
#If USEDAO Then
Set db = CurrentDb()
Set rst = db.OpenRecordset("tblVendors")
#Else
Set rst = New ADODB.Recordset
rst.Open _
Source:="tblVendors", _
ActiveConnection:=CurrentProject.Connection, _
CursorType:=adOpenKeyset, _
LockType:=adLockOptimistic, _
Options:=adCmdTableDirect
#End If
Patrick, THANKS!!
It's great to have one listing of all these resources. I've visited most of these sites, and Allen Browne's is great, as is The Access Web. Although I've found sometimes it's difficult to zero in on specific development feature th…
Wow thankyou Patrick. I now have my new MS Office 2007 so can get started on my first project on Accces 2007, hopefully dipping into a lot of the resources posted so far.
I have also found on ebay the full Library of Bilbles for Microsoft Office fo…
Here are some of my best links that will help you learn more about Access--fabulous sites -- and they are free (mostly).
A Free Ebook Tutorial
http://thatlldoit.com/accessbasics.aspx
Allen Browne's site -- One of the very best. I can't do without…
Hello Stephanie,
I apologize, I replied to your question a few weeks ago about Richard Rost's online courses. But I don't see my reply posted here. Richard has short free targeted demos, and he also has more extensive targeted courses that you can…
I am new to this group but I can see that there is enough expertise here that we who are able can take turns writing tips - articles, etc. sharing our knowledge. If we could assess the knowledge we have among ourselves then someone could organize a…
Access Developers
Simon Wong's Comments
Comment Wall (2 comments)
You need to be a member of Access Developers to add comments!
Join this Ning Network
Welcome to Access Developers. All the best with developing your skills.
The table and field that I use is tblVendors.VendorName. You will have to modify this code to include your table name and the field name. Try it. Hope this helps!
Private Sub cboVendor_NotInList(NewData As String, Response As Integer)
' From Access 2000 Developer's Handbook, Volume I
' by Getz, Litwin, and Gilbert (Sybex)
' Copyright 1999. All rights reserved.
' Amended on 03/06/2008
' EOP Budget and Fiscal database - VISA Statement
' Add new items to the table/field: tblVendors.VendorName
Dim strMsg As String
#If USEDAO Then
Dim rst As DAO.Recordset
Dim db As DAO.Database
#Else
Dim rst As ADODB.Recordset
#End If
strMsg = "'" & NewData & "' is not in the list. "
strMsg = strMsg & "Would you like to add it?"
If vbNo = MsgBox(strMsg, vbYesNo + vbQuestion, _
"New Vendor") Then
Response = acDataErrDisplay
Else
#If USEDAO Then
Set db = CurrentDb()
Set rst = db.OpenRecordset("tblVendors")
#Else
Set rst = New ADODB.Recordset
rst.Open _
Source:="tblVendors", _
ActiveConnection:=CurrentProject.Connection, _
CursorType:=adOpenKeyset, _
LockType:=adLockOptimistic, _
Options:=adCmdTableDirect
#End If
rst.AddNew
rst("VendorName") = NewData
rst.Update
Response = acDataErrAdded
rst.Close
End If
End Sub
Welcome to
Access Developers
Sign Up
or Sign In
About
Latest Activity
Badge
Get Badge
© 2009 Created by Access Guy on Ning. Create a Ning Network!
Badges | Report an Issue | Privacy | Terms of Service