I couldn't get amrut's code to work; probably my fault, although I'm not sure you can work a Main Form/Subform scenario backwards, like he suggested. I don't think the Subform is going to be filtered to move to a given tag number unless the Main Form is already on the Student Record that the tag belongs to.
This hack will identify the student the entered/selected tag number belongs to, using the DLookUp Function, then navigate on the Main Form to that student's Record, basically what the OP's other Combobox does when it retrieves a Record based on the student's name.
I assume that with the Subform being in Datasheet View, the auto bearing the searched for tag will then be visible.
- Place a Combobox on your Main Form
- When the Wizard comes up, hit ‘Cancel’
- Name the Combobox TagsCombo
- With the Combobox selected, go to Properties - Format and set Column Widths to 1” (or whatever you want)
- Go to the Data Tab and in the RowSource Property enter the first code example below
Code:
SELECT [tblVehicleInfo].[TagNumber] FROM tblVehicleInfo;
Now use this code in the Code Module for the Form:
Code:
Private Sub TagsCombo_AfterUpdate()
Dim rs As Object
Dim StudID As String
StudID = DLookup("StudentID", "tblVehicleInfo", "[TagNumber] ='" & Me.TagsCombo & "'")
Set rs = Me.Recordset.Clone
rs.FindFirst "[StudentID] = '" & StudID & "'"
If rs.NoMatch Then
MsgBox "No Match Found!"
Else
If Not rs.EOF Then Me.Bookmark = rs.Bookmark
End If
End Sub
You haven't given us actual names, so you'll have to substitute the ones in both of the above code examples with your actual names.
TagsCombo: Leave this, as is, in the code
[tblVehicleInfo]: Replace with your vehicle table name
[TagNumber]: Replace with the appropriate Field name from this Table
StudentID: This needs to be replaced with the name of whatever Unique Field is used to Link your Main Form and Subforms through the Link Master/Link Child Fields in the Subforms' Data Properties. It's really the Field you should be using in your other Combobox, to retrieve student records, rather than using the last names, as you said you were using for that purpose! Unless this is a truly, truly small school, the chance that no two students will have the same last name seems very unlikely!
I think that covers everything. If you have questions just holler! All of the developers who help out here are volunteers, and things tend to run slow on the weekends, but someone will wander through on Saturday and Sunday!
Linq ;0)>
The problem with making anything foolproof...is that fools are so darn ingenious!
All posts/responses based on Access 2003/2007