[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 458
  • Last Modified:

Adding new items in a Combo Box

I ahve a combo box that I enabled the Not in List Property on the form, yet when I try to add a new item it prompts me to confirm if I want to add a new one, but when I click yes it returns with "Item not on list"..  
here is the code


Private Sub Combo684_NotInList(NewData As String, Response As Integer)
'ALLOW USER TO SAVE NON-LIST ITEMS.
 
  Dim strSql As String
  Dim BYTUPDATE As Byte

  On Error GoTo ErrHandler
 

  BYTUPDATE = MsgBox("DO YOU WANT TO ADD " & Combo684.Value & " TO THE LIST?", vbYesNo, "NON-LIST ITEM!")

  If BYTUPDATE = vbYes Then

    strSql = "INSERT INTO tbldoctor(DoctorOffice) " & "VALUES ('" & NewData & "')"
   
    Debug.Print strSql

    CurrentDb.Execute strSql

    Response = acDataErrAdded

  ElseIf BYTUPDATE = vbNo Then

    Response = acDataErrContinue

    Me!Combo684.Undo

  End If

  Exit Sub

ErrHandler:

  MsgBox Err.Number & ": " & Err.Description, vbOKOnly, "ERROR"
End Sub
0
seamus9909
Asked:
seamus9909
  • 2
1 Solution
 
Jeffrey CoachmanCommented:
IIRC, you need to requery the combobox:

Private Sub Combo684_NotInList(NewData As String, Response As Integer)
'ALLOW USER TO SAVE NON-LIST ITEMS.
 
  Dim strSql As String
  Dim BYTUPDATE As Byte

  On Error GoTo ErrHandler
 

  BYTUPDATE = MsgBox("DO YOU WANT TO ADD " & Combo684.Value & " TO THE LIST?", vbYesNo, "NON-LIST ITEM!")

  If BYTUPDATE = vbYes Then

    strSql = "INSERT INTO tbldoctor(DoctorOffice) " & "VALUES ('" & NewData & "')"
   
    Debug.Print strSql

    CurrentDb.Execute strSql

    Response = acDataErrAdded
    me.Combo684.requery

  ElseIf BYTUPDATE = vbNo Then

    Response = acDataErrContinue

    Me!Combo684.Undo

  End If

  Exit Sub

ErrHandler:

  MsgBox Err.Number & ": " & Err.Description, vbOKOnly, "ERROR"
End Sub
0
 
seamus9909Author Commented:
Thanks when i add the requery, it now says "you must save the current field before running the requery action"
0
 
Jeffrey CoachmanCommented:
Not sure, as I would have to see a sample of your database.
I am not sure where you got that code from
(Did you test it before adding it to your app?)

There are dozens of techniques for doing this.
You can search here or on the web.

Here is a link to a sample I created a while back:
http://filedb.experts-exchange.com/incoming/2012/01_w01/537909/Access--Basic--Simple-ComboBoxNo.mdb
http://filedb.experts-exchange.com/incoming/2009/03_w12/118972/Access--Basic--Simple-ComboBoxNo.mdb

Perhaps another Expert can tackle your code as-is...


JeffCoachman
0

Featured Post

Prep for the ITIL® Foundation Certification Exam

December’s Course of the Month is now available! Enroll to learn ITIL® Foundation best practices for delivering IT services effectively and efficiently.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now