I have an Access 2007 form that is searchable by a combobox. When I add a new record, I need to update the combobox to include the newly added item.
I assume that something needs to be done in AfterInsert event of the form but I can't figure out what.
How can I rebind the combobox after inserting so that the new item appears in the list?
I assume your combobox is a control on a form, not a combobox control in a commandBar. This combobox has a property called rowsource, that can be either a value list (husband;wife;son;girl) or a SQL SELECT instruction (SELECT relationDescription FROM Table_relationType).
I assume also that your form recordset has something to do with your combobox recordset. What you'll have to do is, once your form recordset is properly updated (afterUpdate event I think), to reinitialise the rowsource property of the combobox control
if the recordsource is an SQL instruction:
myComboBoxControl.recordsource = _
"SELECT relationDescription FROM Table_relationType"
or if it is a value list
myComboBoxControl.recordsource = myComboBoxControl.recordsource & ";nephew"
But over all I find your request very strange. Do you have a reflexive (parent-child) relationship on your table?
The easiest way is to guarantee that the combobox is always up-to-date is to just requery the combobox once it gets the focus. Even if the recordset is then updated somewhere else, your combobox is always up-to-date. A simple TheCombobox.Requery in the OnFocus event should be enough.
I would normally use the NotInList event to add data to a combo with
Response = acDataErrAdded
To update the combo.
The Access 2007 Developers Reference has all the details, including sample code: http://msdn.microsoft.com/en-us/library/bb214329.aspx
Requery the combo box in the form's after update event and the delete event. Your combo box will be up to date whenever the user makes changes to the recordset, whether it's a new record, and change, or a deletion.
Unless users must have everybody else's changes as soon as they're made, don't requery the combo box every time it gets the focus because not only will the user have to wait (which is noticable with large recordsets), it's unnecessary if the recordset hasn't changed. But if that's the case, the whole form needs to be requeried as soon as anybody else makes a change, not just the combo box. This would be a highly unusual scenario.
After update:
Private Sub Form_AfterUpdate()
On Error GoTo Proc_Err
Me.cboSearch.Requery
Exit Sub
Proc_Err:
MsgBox Err.Number & vbCrLf & vbCrLf & Err.Description
Err.Clear
End Sub
After delete:
Private Sub Form_Delete(Cancel As Integer)
On Error GoTo Proc_Err
Me.cboSearch.Requery
Exit Sub
Proc_Err:
MsgBox Err.Number & vbCrLf & vbCrLf & Err.Description
Err.Clear
End Sub
There are two possible answers here that are efficient:
use the Form's AfterInsert event to Requery the combo box (as well as the OnDeleteConfirm event). This will be sufficient if the combo box does not display data that the user can update and that needs to be updated if the underlying record is updated.
if updates to the data need to be reflected in the combo box, then it would make sense to add a requery in the AfterUpdate events of the controls that are used to edit the data displayed in the combo box.
For example, if your combo box lists the names of the people in the table, you'll want to use method #2, and in the AfterUpdate event of Me!txtFirstName and Me!txtLastName, requery the combo box. Since you're doing the same operation in four places, you'll want to write a subroutine to do the requery. So, the sub would look something like this:
Private Sub RequerySearchCombo()
If Me.Dirty Then Me.Dirty = False
Me!MyCombo.Requery
End Sub
The reason to make sure you requery only when there is actually an update to the data displayed in the combo box is because if you're populating the combo box with the list of the whole table, the requery can take a very long time if you have 10s of 1,000s of records.
Another alternative that saves all the requeries would be to have a blank rowsource for the combo box, and populate it only after 1 or 2 characters have been typed, and filter the results that the combo displays based on the typed characters. For that, you'd use the combo box's OnChange event:
Private Sub MyCombo_Change()
Dim strSQL As String
If Len(Me!MyCombo.Text) = 2 Then
strSQL = "SELECT MyID, LastName & ', ' & FirstName FROM MyTable "
strSQL = strSQL & "WHERE LastName LIKE " & Chr(34) & Me!MyCombo.Text & Chr(34) & "*"
Me!MyCombo.Rowsource = strSQL
End If
End Sub
The code above assumes that you're searching for a person's name in a combo box that displays "LastName, FirstName".
There's another important caveat: if you're searching a form bound to a full table (or a SQL statement that returns all the records in the table) and using Bookmark navigation to locate the records, this method will not scale very well, as it requires pulling the entire index for the searched fields across the wire. In the case of my imaginary combo box above, you'd be using FindFirst to navigate to the record with the corresponding MyID value, so it's the index for MyID that would have to be pulled (though only as many index pages as necessary to satisfy the search would actually get pulled). This is not an issue for tables with a few thousand records, but beyond about 15-20K, it can be a network bottleneck.
In that case, instead of navigating via bookmark, you'd use your combo box to filter the result set down to the single record. This is, of course, extremely efficient, regardless of whether you're using a Jet back end or a server back end. It's highly desirable to start incorporating these kinds of efficiencies into your application as soon as possible. If you do so, it makes it much easier to upsize to a server back end, or makes it pretty painless if you should hit that tipping point with a mass of new data that makes the old method too inefficient to be user-friendly.
--
David W. Fenton
David Fenton Associates