Results 1 to 8 of 8
  1. #1
    jeff5908 is offline Novice
    Windows XP Access 2007
    Join Date
    Aug 2012
    Posts
    4

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

    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?

  2. #2
    amrut is offline Expert
    Windows 7 64bit Access 2010 32bit
    Join Date
    Jun 2012
    Location
    Dubai
    Posts
    614
    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

  3. #3
    Missinglinq's Avatar
    Missinglinq is offline VIP
    Windows 7 64bit Access 2007
    Join Date
    May 2012
    Location
    Richmond (Virginia, not North Yorkshire!)
    Posts
    3,016
    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

  4. #4
    amrut is offline Expert
    Windows 7 64bit Access 2010 32bit
    Join Date
    Jun 2012
    Location
    Dubai
    Posts
    614
    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.

  5. #5
    jeff5908 is offline Novice
    Windows XP Access 2007
    Join Date
    Aug 2012
    Posts
    4
    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??

  6. #6
    Missinglinq's Avatar
    Missinglinq is offline VIP
    Windows 7 64bit Access 2007
    Join Date
    May 2012
    Location
    Richmond (Virginia, not North Yorkshire!)
    Posts
    3,016
    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)>
    The problem with making anything foolproof...is that fools are so darn ingenious!

    All posts/responses based on Access 2003/2007

  7. #7
    jeff5908 is offline Novice
    Windows XP Access 2007
    Join Date
    Aug 2012
    Posts
    4
    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..

  8. #8
    jeff5908 is offline Novice
    Windows XP Access 2007
    Join Date
    Aug 2012
    Posts
    4
    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??

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 1
    Last Post: 04-20-2012, 03:16 AM
  2. Search Box Requerying Subform problem
    By cbh35711 in forum Access
    Replies: 1
    Last Post: 03-30-2012, 09:37 AM
  3. Multi Field Combo search Form
    By Andyjones in forum Access
    Replies: 3
    Last Post: 03-12-2012, 02:13 PM
  4. QBF (search entire field)
    By 161 in forum Queries
    Replies: 9
    Last Post: 02-09-2011, 03:01 PM
  5. Create combo search form in subform
    By grant.smalley in forum Forms
    Replies: 6
    Last Post: 02-19-2010, 04:37 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums