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
Well maybe this is a bit late (I have only just joined the site) but maybe somebody needs to do a bottom line analysis - that is how the company is being hit in the hip pocket. Just simply timing someone entering all of the information for each appl…
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