How do you edit records from a VBA form that you want to interactively select?

Question

I have a set of ComboBox's in an MS Access 2003 DB that are all bound to fields in a single table. However, the data that they allow you to select doesn't come from that table and instead comes from various other tables. This works fine for the record creation story but now I want to be able to edit the record retroactively. The problem is that I can't figure out how to refill the form elements without writing a bunch of custom code.

My initial inclination is to provide a combo box that limits your choices to record IDs and then do a custom query and use that to set the selected values in all of different form elements. However, I feel like I should be able to do something as simple as DoCmd.GoToRecord , , , ID and the form should repopulate just fine. I'm not opposed to doing the busy work but I'm sure I'm just missing something in my relatively puny knowledge of VBA and Access.

Answer 1

I presume that you've already set up the row sources for each combo box. So long as you haven't limited the combo box to that list; it should display what you have stored in that column.

However, if your Combo Box changes its list for each row you can do something like this in the record's OnCurrent event or the field's GotFocus event:

Me.combo_box_name.Requery

Answer 2

After re-reading your question, I think I see what you are trying to achieve. You're on the right track with GotoRecord, although I would probably use OpenForm in this case, because it has a WhereCondition property that allows you to use SQL to specify exactly what record to open. It sounds like you want to implement a "jump to record" type functionality in your form, where the user selects a record ID from a list and the form changes to display the selected record.

One possibility is to switch to the new record each time the user selects an item in the ComboBox. You can handle this in the ComboBox's Click event.

I'll use a simple example: suppose you have a Students table, and a StudentForm for viewing/editing records in the Students table. The StudentForm has a ComboBox cboStudentID that is bound to the Students.ID column via it's RowSource property. When you select a student ID in the ComboBox, the StudentsForm will switch to display the corresponding student record.

In the Click event handler for the ComboBox, you can code this "jump to record" functionality with something like the following:

Private Sub cboStudentID_Click() 
    Dim recordID As Long
    'The ItemData property will return the value of the bound'
    'column at the specified index.'
    recordID = cboStudentID.ItemData(cboStudentID.ListIndex)
    'Jump to the record. This assumes we want to use the same form.'
    'You can change the form name if you want to open a different form when'
    'the user selects an ID from the ComboBox.'
    DoCmd.OpenForm "StudentForm", WhereCondition:="Student.ID=" & recordID
End Sub

As David W. Fenton points out in the comments, you can shorten the following line:

recordID = cboStudentID.ItemData(cboStudentID.ListIndex)

to this:

recordID = Me!cboStudentID

or just:

recordID = cboStudentID

since the default value of the ComboBox in this case will be the value of the bound column at the current ListIndex. In this case, you could just remove recordID altogether and code the Click event as follows:

Private Sub cboStudentID_Click() 
    DoCmd.OpenForm "StudentForm", WhereCondition:="Student.ID=" & cboStudentID
End Sub

Answer 3

Just to add to the mix, I would offer two approaches, one recommended, the other not.

Approach 1: If you've bound your form to the whole data table (this is the non-recommended approach), you can use the combo box wizard to navigate to the requested record, but I wouldn't recommend it in recent versions of Access:

a. it doesn't allow you to properly name the combo box before it creates code.

b. the code is just WRONG.

Here's the code I just produced in my test database:

Dim rs As Object

Set rs = Me.Recordset.Clone
rs.FindFirst "[InventoryID] = " & Str(Nz(Me![Combo2], 0))
If Not rs.EOF Then Me.Bookmark = rs.Bookmark

This is wrong in so many ways it's just remarkable. This is what the code should be:

With Me.RecordsetClone
  .FindFirst "[ID]=" & Me!cmbMyComboBox
  If Not .NoMatch Then
     If Me.Dirty Then Me.Dirty = False
     Me.Bookmark = .Bookmark
  Else
     MsgBox "Not Found!"
  End If
End With

There is no need to clone the form's recordset when the RecordsetClone already exists.

There is no reason to use an object variable when you can just directly use the pre-existing object.

There needs to be a check for a dirty record before departing the record because if you don't force the save, errors in the save process can lead to lost data.

But the better approach is this:

Approach 2: Use the combo box to change the form's underlying recordsource.

The AfterUpdate event of your combo box would look something like this:

If Not IsNull(Me!cmbMyComboBox) Then
   Me.Recordsource = Me.Recordsource & " WHERE [ID]=" & Me!cmbMyComboBox
End If

Now, this only works the first time, as on the second resetting of the Recordsource, you end up with two WHERE clauses, which is not good. There are two approaches:

a. assuming that the form opens without a WHERE clause, store the opening recordsource value in a module-level variable in the form's OnLoad event:

   Private Sub Form_Load()
     strRecordsource = Left(Me.Recordsource,Len(Me.Recordsource)-1)
   End Sub

And at the module level, define strRecordsource accordingly:

   Dim strRecordsource As String

Then in the combo box's AfterUpdate event, you have this instead:

   Me.Recordsource = strRecordsource & " WHERE [ID]=" & Me!cmbMyComboBox

Now, if your form opens with a WHERE clause already defined, it gets more complicated, but I'll not go into that and leave it as an exercise to the reader what the best approach might be.