Results 1 to 6 of 6
  1. #1
    Join Date
    Aug 2011
    Posts
    6

    Unanswered: Procedure to open a form and search using criteria

    Hi Everyone,

    I'm looking for an event procedure to do the following.

    From Form1, open Form2, and search for a record with criteria entered in Form1.

    This is what I have right now. It's opening the second form, but it's just showing me the first record, not the record searched for.

    ------------------------------------------
    Dim stDocName As String

    stDocName = "Homes"

    DoCmd.OpenForm "Homes"
    Forms!Homes.FilterOn = False
    Lot_Number.SetFocus
    DoCmd.FindRecord Me!LotNumberSelect.Value, acEntire, , acSearchAll
    ------------------------------------------

    I'm new at MsAccess VB, so bare with me.

    Thanks,

    Dan
    Last edited by dannyp; 12-16-11 at 16:45.

  2. #2
    Join Date
    Jun 2005
    Location
    Richmond, Virginia USA
    Posts
    2,763
    Provided Answers: 19
    Here's some sample code.

    If the Criteria is Numeric:
    Code:
    Dim stDocName As String
    Dim stLinkCriteria As String
    
        stDocName = "FormToBeOpened"
        
        stLinkCriteria = "[numID]=" & Me![numID]
       
        DoCmd.OpenForm stDocName, , , stLinkCriteria
    If the Criteria is Text:
    Code:
    Dim stDocName As String
    Dim stLinkCriteria As String
    
        stDocName = "FormToBeOpened"
        
        stLinkCriteria = "[txtID]=" & "'" & Me![txtID] & "'"
      
        DoCmd.OpenForm stDocName, , , stLinkCriteria
    Linq ;0)>
    Hope this helps!

    The problem with making anything foolproof...is that fools are so darn ingenious!

    All posts/responses based on Access 2003/2007

  3. #3
    Join Date
    Aug 2011
    Posts
    6
    Code works, great but I forgot to mention that I need the form that is opened to be not filtered. I need it to go to a certain record, but still have access to the other records without the user trying to figure out how to unfilter the form.

    For example, I have this code working within the form;

    --------
    If IsNull(LotNumSelect.Value) = False Then
    Forms!Homes.FilterOn = False
    Lot_Number.SetFocus
    DoCmd.FindRecord LotNumSelect.Value, acEntire, , acSearchAll
    End If
    --------

    Basically, need that to happen but get initiated from another form. A control panel type form.

  4. #4
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Try this:

    In the "Main" form:
    Code:
    DoCmd.OpenForm "Homes", , , , , , Me!LotNumberSelect.Value
    In the module of the form 'Homes":
    Code:
    Private Sub Form_Load()
      
        Dim rst As Dao.RecordSet
        Dim strCriteria as String
    
        If Not IsNull(Me.OpenArgs) Then
           '
           ' Un-comment one of the two next lines.
           '
           ' strCriteria = "Lot_Number = " & Me.OpenArgs          ' If 'Lot_Number' is numeric.
           ' strCriteria = "Lot_Number = '" & Me.OpenArgs & "'"  ' If 'Lot_Number' is text.
            Set rst = Me.RecordSetClone
            rst.FindFirst strCriteria
            If rst.NoMatch = False Then Me.Bookmark = rst.Bookmark
            rst.Close
        End If
        Set rst = Nothing
    
    End Sub
    Have a nice day!

  5. #5
    Join Date
    Aug 2011
    Posts
    6
    Worked perfectly. Thanks so much. Glad I found this forum.

  6. #6
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    You're welcome!
    Have a nice day!

Posting Permissions

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