Results 1 to 4 of 4
  1. #1
    Join Date
    Aug 2004

    Unanswered: Find Record in subform


    I have a main ubound form that has 1 subform. On the main form I have an unbound text box that I want to use to locate names on the subform and to select the record when found.

    This is what I currently have and keep getting no record found.

    When the record is found, I just want it to be highlighed.

        Dim rstEmployees As Recordset
        Dim varWhere As Variant
        On Error GoTo cmdSearch_Err
        varWhere = Null
        If Not IsNothing(Me.txtSearchFirst) Then
            varWhere = (varWhere + " And ") & "[FirstName] LIKE '" & Me.txtSearchFirst & "*'"
        End If
        Set rstEmployees = Me.sfrmStudentInfo.Form.RecordsetClone
        rstEmployees.FindFirst varWhere
        If rstEmployees.NoMatch Then
            MsgBox "No match found"
            Me.sfrmStudentInfo.Form.Bookmark = rstEmployees.Bookmark
        End If
    Access 3

  2. #2
    Join Date
    Nov 2007
    Adelaide, South Australia
    It would be nice for you to copy and paste ALL the code.

    If that IS all the code, then I would suggest that your varWhere variable is being set incorrectly to start with " And ".
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!

    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

  3. #3
    Join Date
    Dec 2004
    Madison, WI
    I don't use the BookMark or recordsetclone method so I can't advise you there (I seem to have issues when I've tried implementing it in some settings.) Here's some code I've used when trying to go to a record on a subform (I'm recalling this from memory):

    (Bound forms - not filtered):
    Dim SearchID as variant
    SearchID = Forms!MySearchFormName!MyIDSearchFieldName
    Forms!MainFormName!SubFormName.setfocus (Note: I first go to the MainForm as it doesn't always work going directly to the subform)
    Forms!MainFormName!SubFormName!MyIDField.setfocus (Note: same reason as above. Also Note: MyIDField must be Visible and Enabled (but can be locked) on subform! - shrink box size on subform if you don't want to show this field.)
    DoCmd.FindRecord SearchID
    Forms!MainFormName!SubFormName!SomeOtherField.setf ocus

    (for unbound forms, I do it a little different (and even use this for bound forms as well)):

    1. I first find the ID (ie. autonumber or primary key field) value in the Main table. I'll do this by calling a function once the search is initiated and I know the SubTableIDField value. Just make it so the search field on your search form is combobox (or list box) which will pass the SubTableIDField value. ex: ID = retMainTableID(Forms!MySearchFormName!SubTableIDFi eld))...

    Function retMainTableID(SubTableIDField as variant) as
    Dim rs as adodb.recordset
    set rs = new adodb.recordset
    Dim strSQL as string
    strSQL = "Select MainIDField From ....." (this is where you join the MainTable and SubTable by your relational field and add Criteria such as "Where SubTableIDField = " & SubTableIDField & "" strSQL, currentproject.connection, adopenKeyset, adlockReadOnly
    if rs.eof and rs.bof then
    msgbox "Error - No record found in MainTable."
    retMainTableID = 0
    set rs = nothing
    retMainTableID = rs!MainIDField
    set rs = nothing
    end if

    and then use retMainTableID in whatever routine you use to go to the ID Field on the MainForm.

    The above will be different though if you're cycling through multiple record matches.
    Last edited by pkstormy; 12-16-08 at 22:33.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  4. #4
    Join Date
    Dec 2004
    Madison, WI
    Otherwise, you can try this....

    varWhere = (varWhere + " And ") & "[FirstName] LIKE """ & Me!txtSearchFirst & """"

    (ie. I've sometimes had luck with """ and """" versus '" and "*'") - also note the Me! versus Me.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts