Problem created combo search box to search subform field and re-populate entire form

[jeff5908]

Hi Im brand new hope you can help me.

I have created a Parking system for a college i work for, I have 3 tables Personal info, Vehicle info(each person can have few vehicles), and Ticket info, (each person can have many tickets)

I have created the form where main form is personal info with the other two displayed in subforms in datasheet view to which is needed to view all the persons vehicles and tickets.

In the Main Form I have a combo search box to look up last names and populate entire form&sub forms and it works great.
But when I try to create a combo box to search tag info inside the subform it will not populate anything. It appears I can only use fields from the main forms table.
What am I not seeing?

[amrut]

I think you are missing the reference to subform field. If the combo to filter is on main form then you need to use
With Me.[YourSubFormControlName].Form
.Filter = "[TagInfo] = """ & Me.[YourComboName] & """"
.FilterOn = True
End With
Have a look here - http://access.mvps.org/access/forms/frm0031.htm

[Missinglinq]

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

SELECT [tblVehicleInfo].[TagNumber] FROM tblVehicleInfo;

Now use this code in the Code Module for the Form:

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

[amrut]

MissingLinq, you are right. I overlooked the fact that the tag info will keep on changing based on the student Id over a period of time.Will my code work in after update event of combo if combo's rowsource is a query pulling the tag info matching the student ID field on the main form instead of all the tag info ?
I have used the above code but the combo on mainform row source had limited items which rarely changed.

[jeff5908]

Sorry i'm late getting back to you... The combo box filled in with all my tags, exactly what i want, but when you click on a tag I get a run time error
Data type mismatch in criteria expression, and when i debug it highlights
rs.FindFirst "[StudentID] = '" & StudID & "'" ( ihave replaced the fields as nessecary)

Apparently I dont know Code well at all. What did I do wrong??

[Missinglinq]

StudentID is apparently defined as a Number Datatype, which requires a different syntax.

Replace

rs.FindFirst "[StudentID] = '" & StudID & "'"

with

rs.FindFirst "[StudentID] = " & StudID

Most experienced developers usually only define a Field as a Number if there is the possibility that it will be used to do math. All non-math Fields, even those containing all digits, such as phone numbers, ID numbers, SSNs, etc, are defined as Text. An exception to this would be if you needed to Sort by the Field, as Numbers and Text sort differently.

Linq ;0)>

[jeff5908]

That worked and got rid of the error.. Thanks much.. But if I could steal just a little more of your brain.. It only is searching the current subform on the screen.. if its not the current record it returns no record as in the msgbox. Its not searching the whole table, I'm thinking my link is wrong?
I'm almost there..

[jeff5908]

Missinglink, I just removed everything and did it over just to see if I messed up somewhere. disregard my previous post.. now it drops down and no matter what i pick it says No match found.. so i think im back to my link... If this helps explain my mess, the main form ID is just an autonumber because we enter 500 or so a year, than the vehicle and ticket table are linked to that number called PersonID, ---My question would be in the Subform Link master is "ID" and the Link Child is "PersonID", the main form runs on "ID".. Could something be wrong with this??