Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here


Microsoft: Access Forms FAQ

Combo Boxes

Five Ways to Move to a Record in a Subform based on a Selection in a Combo Box by Vie
Posted: 11 Mar 04 (Edited 11 Mar 04)

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 Private Sub cboName_AfterUpdate()
7.    Insert the following code


Private Sub cboName_AfterUpdate()
'Moves Record Selector to record in subform that corresponds to the 'selection in this combo box.

Dim varName As Integer

varName = Me.cboName.Column(0)

With Me.subformName.Form
    .RecordsetClone.FindFirst "FieldName = " & varName
    If Not .RecordsetClone.NoMatch Then
        .Bookmark = .RecordsetClone.Bookmark
    End If
End With
End Sub

Explanation of above sub:

Dim varName As Integer
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.

VarName = Me.cboName.Column(0)
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:

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

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.

With Me.subformName.Form
    .RecordsetClone.FindFirst "FieldName = " & varName

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 .

If Not .RecordsetClone.NoMatch Then
        .Bookmark = .RecordsetClone.Bookmark

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., MsgBox ôThere is no matching record in your subform.ö)

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)!

Back to Microsoft: Access Forms FAQ Index
Back to Microsoft: Access Forms Forum

My Archive

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close