Five Ways to Move to a Record in a Subform based on a Selection in a Combo Box

Microsoft's Knowledge Base gives "Four Ways to Move to a Record on a subform from a Combo Box Selection"

MSÆs four ways deal with forms in single form or continuous forms view only:

+ ôIn the AfterUpdate event of a combo box, execute code that uses the FindFirst method.ö
+ ôIn the AfterUpdate event of a combo box, call a macro that requeries the Filter property of a form.ö
+ ôUse a Form/Subform, with a combo box on the main form, and the data in the subform, bound by the LinkMasterFields and LinkChildFields properties of the subform control.ö
+ ôBase the form on a query that joins two tables, and then use the AutoLookup technique to bind a combo box to the field that controls the join.ö

Links to these articles:

Access 2000: http://support.microsoft.com/default.aspx?scid=kb;en-us;209537
Related article: http://support.microsoft.com/default.aspx?scid=kb;en-us;287658
Access 2002: http://support.microsoft.com/default.aspx?scid=kb;EN-US;287658

FIFTH WAY to Move to a Record based on a Selection in a Combo box
Subform is in Datasheet View

Frequently, subforms are shown in datasheet view (as opposed to single form or continuous forms) which is the view that basically looks just like a table. In the articles above, MS does not detail a way to move to a record in a subform that is in datasheet view from a combo box selection. Here's how:


1. Open your main form (the form with both your combo box and your subform on it) in Design View.
2. Select your combo box and open its Property window.
3. Click on the Events tab.
4. Go to After Update and enter an open bracket, [
5. Click on the ellipse that appears to the right, à
6. Now you will be in the VB IDE in a procedure [color blue]Private Sub cboName_AfterUpdate()[/color]
7. Insert the following code



Explanation of above sub:

[color blue]Dim varName As Integer[/color]
This declares an integer variable in which the value of the bound column of your combo box will be temporarily stored. The type does not need to be Integer. It could just as well be a string, long, double, etc.

[color blue]VarName = Me.cboName.Column(0)[/color]
This line sets your variableÆs value to the value of the bound column of your combobox. If your combo box has four columns, they will be numbered 0 = first column, 1 = second column, 2 = third column, and 4 = fourth column (i.e. they are ôzero-based.ö) Generally you will have only one bound column in a combo box and that column will be the value that is actually selected when you select a record from the combo box. In other words, if you have four columns:

[color purple]Column(0)[tab][tab]Column(1)[tab]Column(2)[tab]Column(3)
CharID[tab][tab][tab]LastName[tab]FirstName[tab]CharacterType
1[tab][tab][tab][tab][tab]Simpson[tab][tab]Homer[tab][tab]Loveable Buffoon
2[tab][tab][tab][tab][tab]Simpson[tab][tab]Bart[tab][tab][tab]Incorrigible Imp
3[tab][tab][tab][tab][tab]Simpson[tab][tab]Lisa[tab][tab][tab]Adorable Know-it-All
4[tab][tab][tab][tab][tab]Simpson[tab][tab]Marge[tab][tab]Gullible Humanist
5[tab][tab][tab][tab][tab]Simpson[tab][tab]Maggie[tab][tab]Infantile Enigma[/color]

CharID will be your bound column. Most likely this column will be hidden (Column Width set to 0ö) in your combo box. When you select Bart Simpson from the combo box, the value you are actually selecting and storing temporarily in your varName will be 2.

For the above code to work, the Field containing the value in your combo boxÆs bound column must also be included in your subformÆs RecordSource. BUT, if itÆs an ID field, you probably donÆt want it to show in your subform any more than you want it show in your combo box. In order to not show the ID field and still be able to find the record based on that field, set your subformÆs RecordSource to the table or a query that includes the field BUT donÆt include the field in the design of your subform. Remember, the subformÆs actual appearance is just an interface. The RecordSource of your subform is what Jet searches, not the graphic interface. An alternative method if you absolutely must have the ID field on the subform but donÆt want it to show, place the ID field in the formÆs footer. Datasheet view will not show a footer or its controls at run-time.

[color blue]With Me.subformName.Form
.RecordsetClone.FindFirst "FieldName = " & varName[/color]
These lines create a clone of your subformÆs recordset and then use the FindFirst method to search for the value in your FieldName that matches the value in your varName .

[color blue]If Not .RecordsetClone.NoMatch Then
.Bookmark = .RecordsetClone.Bookmark[/color]
These lines bookmark the matching record once it is found. If it is not found, it will End If, End With and End Sub. If you want a message box to appear you could include an Else line with a MsgBox function just beneath these lines (e.g., [color blue]MsgBox ôThere is no matching record in your subform.ö[/color])

Finally, save your new sub. Close the VB Editor and open your form. Select a record from your combo box and voila! ItÆs that simple (more simple, that is, than my long-winded explanation might make it seem)!