Letting users add a new record to a combo box

Often your users will need to add new items to a combo box. How
you do that will depend on several factors.

• If the items in the combo are made up of a value list, you
  will need to add new items to that list.
• If the combo is based on a query or table, you will need to
  add a new record to that underlying table. That can be done
  behind the scenes, or by popping up a form for the users to fill
  out; the second option is best if the new record requires
  information in several fields (such as a new client).

Adding new items to a value list

In most cases you will use a table as the RowSource for a combo
box. That simplifies filtering and sorting the RowSource, for
example when you create cascading (dependent) combo boxes. However,
you don’t always know what data needs to be displayed. For example,
scheduling systems will often require several dates from the current
date (the next 4 Mondays or the next 14 days).

Using a list-filling callback function

To create a list of the next n Mondays, Access provides
a callback function called ListMondays. You can find it in the Help,
but the documentation isn’t all that good. The function is shown
below: it needs to be pasted into a standard module.

Function ListMondays(fld As Control, id As Variant, _
  row As Variant, col As Variant, code As Variant) _
  As Variant

  Dim intOffset As Integer
  Select Case code
    Case acLBInitialize ' Initialize.
      ListMondays = True
    Case acLBOpen ' Open.
      ListMondays = Timer ' Unique ID.
    Case acLBGetRowCount ' Get rows.
      ListMondays = 12
    Case acLBGetColumnCount ' Get columns.
      ListMondays = 1
    Case acLBGetColumnWidth ' Get column width.
      ListMondays = -1 ' Use default width.
    Case acLBGetValue ' Get the data.
      intOffset = Abs((9 - Weekday(Now)) Mod 7) - 28

    ListMondays = Format(Now() + _
      intOffset + 7 * (row - 1), "dd-mmm-yyyy")
  End Select
End Function

To adjust this function for your needs, there are only 3 rows
that you need to alter. They are:

Change the number of rows to display (in this case, 12)

    Case acLBGetRowCount ' Get rows.
      ListMondays = 12

Change the day to use, and the offset value that determines
the starting date

    Case acLBGetValue ' Get the data.
      intOffset = Abs((9 - Weekday(Now)) Mod 7) - 28

The expression in parentheses sets the day of the week. For
example, the 9 in the above expression ensures that you will get a
Monday as the first date in the sequence. The logic goes like this:

• Weekdays go from Sunday (1) through Saturday (7). By adding
  7 to the weekday you make sure that subtracting Weekday(Now)
  from your target weekday, you will always have a positive
  number. In case the result is >=7, dividing by 7 and taking the
  remainder (the Mod 7 part) will give a value between 0 and 7.
• Offsetting by -28 moves you back 4 weeks. You will need to
  adjust this value to get the correct first date.
• For example, assume that today is Friday 2 November. The
  intOffset calculation is Abs((9 – 6) Mod 7) – 28, or -25. If I
  pick Monday 5 November instead (3 days later), the calculation
  becomes Abs((9 – 2) Mod 7) -28, or -28. The starting point is
  the same day for any day from Tuesday to the following Monday.

Build the list of dates

    ListMondays = Format(Now() + _
      intOffset + 7 * (row - 1), "dd-mmm-yyyy")

It’s not obvious from the construction of this function but this
is the loop that builds the list. Change the date format to suit
your requirements.

To use this function, the Row Source Type of the combo box needs
to be changed so that it reads ListMondays — the name of the
callback function. Leave the Row Source blank.

Using the AddItem method

In Access XP and later, you can use AddItem to build a value
list. Typically, you would create the list when the form loads, as
in the sample code below which populates a combo box with the months
of the year.

Private Sub Form_Load()
  Dim intMonth As Integer

  'set the rowsource type
  Me.cmbMonth.RowSourceType = "Value List"

  'clear the current list and set the column count to 1
  Me.cmbMonth.RowSource = vbNullString
  Me.cmbMonth.ColumnCount = 1

  'populate the list
  For intMonth = 1 To 12
    Me.cmbMonth.AddItem Format(DateSerial(Year(Now()), intMonth, 1), "mmmm")
  Next intMonth
End Sub

Building a string for the RowSource

If you are using an older version than Access XP, you can’t use
the AddItem method. In that case you need to build the list by
adding the delimiting semi-colons, and then setting the RowSource to
the new list. The equivalent code to the last example is shown
below:

Private Sub Form_Load()
  Dim intMonth As Integer
  Dim strItems As String

  'set the rowsource type
  Me.cmbMonth.RowSourceType = "Value List"

  'clear the current list and set the column count to 1
  Me.cmbMonth.RowSource = vbNullString
  Me.cmbMonth.ColumnCount = 1
  strItems = vbNullString

  'populate the list
  For intMonth = 1 To 12
    strItems = strItems & ";" & Format(DateSerial(Year(Now()), intMonth, 1), "mmmm")
  Next intMonth

  'remove the first semi-colon from the list
  strItems = Mid(strItems, 2)

  'reset the RowSource to the newly created string
  Me.cmbMonth.RowSource = strItems
End Sub

Some things to note about value lists:

• The first line of code ensures that the correct rowsource
  type is used. Without this the code will fail, if for any reason
  the rowsource type has been changed to Table / Query or a custom
  list-filling function.
• The second and third lines of code are housekeeping. They
  ensure that the list is empty before we start adding items, and
  that only 1 column is displayed.
• There is a 2048 character limit in value lists for Access XP
  and earlier. This restricts the number of items that you can
  display; if you need more than this, you will need to use a
  table or query as your rowsource.
• If you want to start the month list somewhere other than
  January (eg, for a financial year), you can easily modify the
  code by adding an offset value to intMonth:

    Me.cmbMonth.AddItem Format(DateSerial(Year(Now()), intMonth + 6, 1), "mmmm")

In the example above, the first month in the list is July and the
month names wrap around to June: suitable for the Australian
financial year.

Adding a record to a table

If you are only populating a single field, this routine will
create a new record in the table and refresh the combo box to
display the new entry. It has been written to be generic: you will
need to change the names of four items in the code to make it work
for your situation.

Note: The following settings are required for the code to work:

• The Limit To List property of the combo box must be set to
  Yes
• You need to set a
  reference to the Microsoft DAO 3.6 Object Library

Private Sub YourCombo_NotInList(NewData As String, Response As Integer)
  'LimitToList property must be set to Yes.
  'Requires a reference to the Microsoft DAO 3.6 Object Lirary

  On Error GoTo ErrorHandler

  'strings used for the MsgBox
  Dim strTitle As String
  Dim strMsg1 As String
  Dim strMsg2 As String
  Dim strMsg As String
  'buttons to display on the MsgBox
  Dim intMsgDialog As Integer
  'result returned from the MsgBox
  Dim intResult As Integer
  'object variables
  Dim cbx As Access.ComboBox
  Dim dbs As DAO.Database
  Dim rst As DAO.Recordset
  'field and table names
  Dim strTable As String
  Dim strEntry As String
  Dim strFieldName As String

  'The name of the lookup table -- edit to suit
  strTable = "YourTable"

  'The type of item to add to the table -- edit to suit
  strEntry = "Descriptive text"

  'The field in the lookup table in which the new entry is stored -- edit to suit
  strFieldName = "YourField"

  'The combo box that you are updating -- edit to suit
  Set cbx = Me![YourComboBox]

  'Display a message box asking whether the user wants to add a new entry.
  strTitle = strEntry & " is not in the list"
  intMsgDialog = vbYesNo + vbExclamation + vbDefaultButton1
  strMsg1 = "Do you want to add "
  strMsg2 = " as a new " & strEntry & " entry?"
  strMsg = strMsg1 + NewData + strMsg2
  intResult = MsgBox(strMsg, intMsgDialog, strTitle)

  If intResult = vbNo Then
    'Cancel adding the new entry to the lookup table.
    Response = acDataErrContinue
    cbx.Undo
    Exit Sub
  ElseIf intResult = vbYes Then
    'Add a new record to the lookup table.
    Set dbs = CurrentDb
    Set rst = dbs.OpenRecordset(strTable)
    rst.AddNew
    rst(strFieldName) = NewData
    rst.Update
    rst.Close

    'Continue without displaying default error message.
    Response = acDataErrAdded
  End If

ErrorHandlerExit:
  Exit Sub

ErrorHandler:
  MsgBox "Error No: " & Err.Number & "; Description: " & _
  Err.Description
  Resume ErrorHandlerExit

End Sub

 
Home | About |
Links |

Contact | Excel |
Access