Welcome to SPN

Register and Join the most happening forum of Sikh community & intellectuals from around the world.

Sign Up Now!

prompt to add data to lookup list?

Discussion in 'Information Technology' started by triplespiral, Jul 28, 2006.

  1. triplespiral

    triplespiral
    Expand Collapse
    Guest

    I imagine this is something simple (hopefully) so I am asking the all-wise
    ones of this community for any insight!

    Currently i have a table that contains team names so the person entering the
    data can easily select from the list as well as to keep consistency with
    spelling etc. Currently, when a new record is entered, if the name is not in
    the list it gives an error stating so... is there a way to have it say "this
    isn't in this, but would you like me to add it?" and automatically update the
    names table with the new entry?

    Ta!
    Laurene
     
  2. Loading...


  3. Ofer Cohen

    Ofer Cohen
    Expand Collapse
    Guest

    Can be done in a form, check this link on "How to use the Microsoft Access
    Not In List event of a combo box"

    http://www.databasedev.co.uk/not_in_list.html

    --
    Good Luck
    BS"D


    "triplespiral" wrote:

    > I imagine this is something simple (hopefully) so I am asking the all-wise
    > ones of this community for any insight!
    >
    > Currently i have a table that contains team names so the person entering the
    > data can easily select from the list as well as to keep consistency with
    > spelling etc. Currently, when a new record is entered, if the name is not in
    > the list it gives an error stating so... is there a way to have it say "this
    > isn't in this, but would you like me to add it?" and automatically update the
    > names table with the new entry?
    >
    > Ta!
    > Laurene
     
  4. fredg

    fredg
    Expand Collapse
    Guest

    On Thu, 22 Jun 2006 11:52:02 -0700, triplespiral wrote:

    > I imagine this is something simple (hopefully) so I am asking the all-wise
    > ones of this community for any insight!
    >
    > Currently i have a table that contains team names so the person entering the
    > data can easily select from the list as well as to keep consistency with
    > spelling etc. Currently, when a new record is entered, if the name is not in
    > the list it gives an error stating so... is there a way to have it say "this
    > isn't in this, but would you like me to add it?" and automatically update the
    > names table with the new entry?
    >
    > Ta!
    > Laurene


    You just want to add the name to the table? No other field?

    Code the Combo box NotInList event:

    ' Prompt user to verify they wish to add new value.
    If MsgBox("Name is not in list. Add it?", vbOKCancel) = vbOK Then
    ' Set Response argument to indicate that data is being added.
    Dim strSQL As String
    strSQL = " INSERT INTO TableName(ComboName) SELECT " & Chr(34) &
    NewData & Chr(34) & ";"
    CurrentDb.Execute strSQL, dbFailOnError
    Response = acDataErrAdded
    Else
    ' If user chooses Cancel, suppress error message
    Response = acDataErrContinue
    ' clear the entry from the combo box
    Me.ComboName = Null
    End If
    End Sub

    --
    Fred
    Please respond only to this newsgroup.
    I do not reply to personal e-mail
     
  5. Larry Daugherty

    Larry Daugherty
    Expand Collapse
    Guest

    from a personal app of mine that's an example of "do as I say, not as
    I do". It was hurried and there is no real error handling code. I
    didn't realize I had any code without it !! Also, the Sendkeys isn't
    a great idea. On www.mvps.cor/access there is some code contributed
    by Ken Getz that has safer routines. fSendkeys, I believe.

    Your mileage may vary...

    Private Sub GetShopName_NotInList(NewData As String, Response As
    Integer)

    On Error GoTo GetShopName_NotInList_error

    Dim strMsg As String
    Dim mydb As Database
    Dim rst As Recordset
    Dim CapStr As String

    Beep

    strMsg = "'" & NewData & "' is not listed. "
    strMsg = strMsg & "Would you like to add it?"
    If MsgBox(strMsg, MB_YESNO, "New Job Shop") = IDNO Then
    SendKeys "{ESC}"
    Response = DATA_ERRCONTINUE
    Else
    Response = DATA_ERRADDED

    Set mydb = CurrentDb()
    Set rst = mydb.OpenRecordset("tblItem")
    rst.AddNew
    rst!Item = NewData
    rst.Update
    rst.Close
    mydb.Close

    End If

    Exit Sub

    GetShopName_NotInList_error:
    Resume Next

    End Sub


    HTH
    --
    -Larry-
    --

    "triplespiral" <triplespiral@discussions.microsoft.com> wrote in
    message news:5B61C621-4EDD-4188-BA55-CB11A12CD99A@microsoft.com...
    > I imagine this is something simple (hopefully) so I am asking the

    all-wise
    > ones of this community for any insight!
    >
    > Currently i have a table that contains team names so the person

    entering the
    > data can easily select from the list as well as to keep consistency

    with
    > spelling etc. Currently, when a new record is entered, if the name

    is not in
    > the list it gives an error stating so... is there a way to have it

    say "this
    > isn't in this, but would you like me to add it?" and automatically

    update the
    > names table with the new entry?
    >
    > Ta!
    > Laurene
     

Share This Page