Ask Your Question

Combo box auto-update when a new entry is typed in? [closed]

asked 2017-05-05 03:23:34 +0200

EasyTrieve gravatar image

updated 2020-07-21 01:35:25 +0200

Alex Kemp gravatar image

Can anyone share with me a base macro that does this:

In this base Table Control, if rather than select blue or green, you type "red" for a car color into the combo box (i.e. a brand new item in the list), then

1) update the cars table with red (the form already does this), and then via an event:
2) append "red" to the colors table, and
3) update the combo box to now display all three options: blue, green and red, for future record edits.

In other words, when a new, previously unknown, item is entered into a combo box in a base Table Control, auto-update the combo box's underlying table, and update the combo box with the new value.

I previously had this working in many of my Access forms but having to start over w/ base. Also I put a confirmation prompt on the screen before doing the insert to help keep errors out of the underlying table.

This is a very handy tool for data entry to update the underlying lookup table.

edit retag flag offensive reopen merge delete

Closed for the following reason question is not relevant or outdated by Alex Kemp
close date 2020-07-21 01:35:42.267976

1 Answer

Sort by » oldest newest most voted

answered 2017-05-06 20:12:53 +0200

EasyTrieve gravatar image

updated 2017-05-07 19:32:02 +0200

[7 May update: Fixes lost focus that MsgBox creates. Also improves and fixes error messages.]

This is currently designed to work w/ a MariaDB connected database (a MySQL binary replacement) so will need to be modified a bit to work with HSQLDB which uses different SQL quoting.

For the combo box data it assumes you're using SQL 'Type of list contents...' (not Table, Query, etc). It grabs the information it needs from various places including the underlying SQL to make it easier to hookup to multiple columns with just one Sub.

You might need some coding experience to get this to work for you. I've left in some code commented out to help remind me of related things that aren't needed in this code.

Option Explicit 'BASIC  ###### ComboBox lookup auto-update ######

Private Sub BeforeUpdatingComboBox(oEvent As Object) As Boolean     'Fires when cr is hit: when something is selected or entered
    On Error GoTo Error_Handler

    '--- Constants
    Dim   sbCR As String : sbCR = chr(10)

    'buttons displayed
    Const sbOkOnly      =  0
    Const sbYesNo       =  4

    'icons displayed
    Const sbQuestion    = 32
    Const sbExclamation = 48

    'answers returned
    Const sbYes         =  6
    Const sbNo          =  7

    '---Get context 
    Dim oComboBox   As Object   : oComboBox = oEvent.Source

    'Check that setup is correct
    if oComboBox.ListSourceType <> Then MsgBox(_
        "This version of the 'BeforeUpdatingComboBox' event handler requires that combo box 'Type of list contents...' be set to SQL (rather than Table, Query, etc.).",_
        "Fatal configuration error - Missing SQL text"): stop

    'See if what was selected is in the underlying data
    Dim sInput      As String   : sInput    = oComboBox.getCurrentValue     'String that was entered before CR
'   Dim sInput      As String   : sInput    = oComboBox.Text                'String that was entered before CR

    'If input is not null, then look to see if it's in the table aready, and if not add it
    If sInput<>"" Then

        Dim sName       As String   : sName     = oComboBox.DataField       'My field's Name (not combo box name)
'       Dim sName       As String   : sName     = oComboBox.BoundField.Name 'My field's Name (not combo box name)   (same as above)

''      Dim iItems      As Integer  : iItems    = oComboBox.ItemCount       '# of items in combo box lookup         (not used)
''      Dim sItems()    As String   : sItems    = oComboBox.StringItemList  'array of all combo box items           (not used)

        Dim sSQL        As String   : sSQL      = oComboBox.ListSource      'SELECT `To` FROM `accounting`.`tos` AS `tos` ORDER BY `To` ASC 

        'Take SQL apart into phrases.  Add WHERE for ID=
        Const cOB=" ORDER BY ":Dim sOrderBy As String   :sOrderBy= sRightParam(sSQL, cOB, iStart(sSQL, cOB)) :sSQL  = sRemainder(sSQL ,iStart(sSQL , cOB))  'get & remove any ORDER BY phrase
        Const cWH=" WHERE "   :Dim sWhere   As String   :sWhere  = sRightParam(sSQL, cWH, iStart(sSQL, cWH)) :sSQL  = sRemainder(sSQL ,iStart(sSQL , cWH))  'get & remove any WHERE    phrase
        Const cFR=" FROM "    :Dim sFROM    As String   :sFROM   = sRightParam(sSQL, cFR, iStart(sSQL, cFR))                                                'get              FROM     phrase
        Const cAS=" As "      :                                                                               sFrom = sRemainder(sFrom,iStart(sFROM, cAS))  '      remove any As       phrase

        'sSQL = SELECT ... FROM ...
        sSQL = sSQL & cWH & "("     & iif(sWhere<>"", sWhere & ") AND ( " ,"")  & ...
edit flag offensive delete link more

Question Tools

1 follower


Asked: 2017-05-05 03:23:34 +0200

Seen: 879 times

Last updated: May 07 '17