Results 1 to 6 of 6
  1. #1
    newbieX is offline Competent Performer
    Windows 7 64bit Access 2007
    Join Date
    Jul 2013
    Posts
    111

    Appending data to lookup table if not in list at the click of a button

    I have a subform that has an unbound combo box (cbo_MyList) pulling data from a column in my MySources lookup table. The "Not in List" property for cbo_MyList has the following code. The code appends the data to the end of the MySources table as I want, but not when I want. I want the code to append to the table when the user clicks the "Add Data" button (just in case the user clears their entries halfway through). Suggestions as to how I can alter my code to do it when a button is clicked rather than when I click outside cbo_MyList.



    Code:
    Private Sub cbo_MyList_NotInList(NewData As String, Response As Integer)
    
        Dim ctrl As Control
        Dim strSQL As String, strMessage As String
        Set ctrl = Me.ActiveControl
       
        strSQL = "INSERT INTO MySources(Source) VALUES(""" & _
                NewData & """)"
    
            CurrentDb.Execute strSQL, dbFailOnError
            Response = acDataErrAdded
    End Sub
    Thank you in advance.

  2. #2
    ItsMe's Avatar
    ItsMe is offline Sometimes Helpful
    Windows 7 64bit Access 2010 32bit
    Join Date
    Aug 2013
    Posts
    7,862
    Have you tried placing an action query like INSERT INTO behind a command button? Maybe the click event could fire some VBA that would run a query.

  3. #3
    newbieX is offline Competent Performer
    Windows 7 64bit Access 2007
    Join Date
    Jul 2013
    Posts
    111

    Appending data to lookup table followup response

    Quote Originally Posted by ItsMe View Post
    Have you tried placing an action query like INSERT INTO behind a command button? Maybe the click event could fire some VBA that would run a query.
    There is some kind of behind the scenes code going on within the NotInList property box that is figuring out if the value entered into the combo box is in the lookup table, and if not, executing the insert into command.

    I think what needs to happen is that I need to mimic this behind the scenes code and apply it to the Add Data button. I do not know how to do this.

  4. #4
    hapm is offline Competent Performer
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2014
    Posts
    197
    Allow the user to input data that is not in the list (can be changed in control properties) and when the Click event of your button is called, check if the current value is in your list, and if not, add it with your INSERT sql and do a requery on the combobox.

  5. #5
    ItsMe's Avatar
    ItsMe is offline Sometimes Helpful
    Windows 7 64bit Access 2010 32bit
    Join Date
    Aug 2013
    Posts
    7,862
    I have not done it but I am sure it is possible. You could call a separate sub routine or function within the form's module or simply include some code in the NotInList procedure.

    I will guess that the NotInList is testing for a condition and when the condition is true the event fires. So it should be a matter of getting the value from the cbo that the user was typing. Maybe a .Text property or something.

    A quick search and I came up with this example from here.
    http://msdn.microsoft.com/en-us/libr...ffice.15).aspx

    MVP Contributor Bill Jelen, MrExcel.com
    Code:
    Private Sub cboDept_NotInList(NewData As String, Response As Integer)
        Dim oRS As DAO.Recordset, i As Integer, sMsg As String
        Dim oRSClone As DAO.Recordset
    
        Response = acDataErrContinue
    
        If MsgBox("Add dept?", vbYesNo) = vbYes Then
            Set oRS = CurrentDb.OpenRecordset("tblDepartments", dbOpenDynaset)
            oRS.AddNew
            oRS.Fields(1) = NewData
            For i = 2 To oRS.Fields.Count - 1
                sMsg = "What do you want for " & oRS(i).Name
                oRS(i).Value = InputBox(sMsg, , oRS(i).DefaultValue)
            Next i
            oRS.Update
            cboDept = Null
            cboDept.Requery
            DoCmd.OpenTable "tblDepartments", acViewNormal, acReadOnly
            DoCmd.GoToRecord acDataTable, "tblDepartments", acLast
        End If
    End Sub
    Just below that example is another one that uses SQL rather than DAO. The event passes the string the user tried to use via NewData. It looks like you will need to collect the info from the user via an input box if your Lookup table has more than An Autonumber and one other column that would accept NewData. So three or more columns would require the looping code in the example above.

  6. #6
    ipisors is offline Access Developer
    Windows XP Access 2007
    Join Date
    Sep 2013
    Posts
    119
    Just my personal advice/experience. I shun Not In List techniques - they seem like gimmicks that overcomplicate things. Really. Rather, I ALWAYS set the Combobox properties to AllowValueListEdits-No, LimitToList-Yes, ShowOnlyRowSourceValues-Yes. In fact I wish MS defaulted these things - why in the world would you want to allow users to pick something not in your carefully contrived and designed list, then trap that problem??

    Next to every combobox that is limited to a specific list, I usually have a little "Add" link, bringing them to the Form where I've then carefully designed some process to add a record to the table which [if you have a normalized database, SHOULD be populating that cmb!] in question. It's phenomenally easier than trying to create a large amount of nested IF/ENDIF code to do the whole thing in one singular block of code.

    I have yet to have any user complain, not find that entirely intuitive and convenient, or ask me to allow them to free-enter things not in the list. Users understand that either the item is in the list, and if it's not, they click a link to Add it in a proper, controlled manner.

    You could take that approach if you want and skip this headache.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 6
    Last Post: 01-11-2013, 11:33 AM
  2. Replies: 5
    Last Post: 12-09-2012, 02:29 PM
  3. Replies: 1
    Last Post: 03-29-2012, 06:19 PM
  4. Export Excel data to access on a button click
    By Grooz13 in forum Import/Export Data
    Replies: 0
    Last Post: 03-15-2011, 10:02 AM
  5. Invisible LookUp Data List
    By aaghd in forum Access
    Replies: 3
    Last Post: 07-28-2009, 02:35 PM

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums