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.

Solution

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.