I have a combo box on a customer form to add the customer's town. This comes
from the Town Table. The Town Table has two fields, the town ID (primary field)
and the town field.
How do I add a new town to the Town Table through the combo box on the
customer form?
You can use the following method (it requires that you have, or set, a reference
to the DAO Object Library appropriate for your version of Access. See
http://support.microsoft.com/default.aspx?kbid=197110):
'*****EXAMPLE START
Private Sub cboColors_NotInList(NewData As String, _
Response As Integer)
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim strSQL As String
'Open recordset containing no existing rows by
'filtering to non-existent value (faster)
strSQL = "SELECT Color " & _
"FROM tblColorsLkp " & _
"WHERE ColorID=0"
'Prompt user to add or not
If MsgBox("""" & NewData & """ is not in the list. " _
& "Would you like to add it?" _
, vbYesNo + vbQuestion, "New Value") = vbYes Then
'User said "Yes"
'Open recordset and add new value
Set db = CurrentDb()
Set rs = db.OpenRecordset(strSQL, dbOpenDynaset)
With rs
.AddNew
!Color = NewData
.Update
End With
'Tell Access that the value has been added
' and to requery combo box
Response = acDataErrAdded
Else 'User said "No"
'Tell Access to show default error message
Response = acDataErrDisplay
End If
End Sub
'*****EXAMPLE END
My problem seems the exact opposite of the earlier advice:
"You can use the combo's NotInList event to add values to its lookup table,
provided the value being entered is the key value (i.e. not if the bound
column is zero-width)."
The value I need to enter is not the key value as this is an ID Number (and
the bound column is zero width.
I don't know where you got that advice, but I haven't had a problem using this
scenario. In my example, above, I used a Rowsource in my combo box based on a
table that contained an autonumber ID field and the "Color" description field.
The first, and bound, column has a columnwidth of 0 inches (zero-width). When
you insert the new value into the table, a new ID is generated (if you are not
using an autonumber, your code will need to set the new ID's value - you can
prompt the user via InputBox() for a value, if needed) and then you save the
record and tell Access that the new record has been added (acDataErrAdded). When
the combo box requeries, the new ID value will be inserted into the combo box's
bound field.
