Results 1 to 5 of 5
  1. #1
    Join Date
    Feb 2014
    Posts
    2

    Unanswered: Assigning Variable to Field in Query Recordset

    I have a search form that uses a query. If there is only one record returned on the query, I would like to use code to directly open the editing form to that record. Where I need help is replacing the message box below with code assigning a variable to the value in the field named ID (first column) of that one record in the query. Currently the user has to click on the record listed in a subform and this would save a lot of time since 95% of the searches return just one record.

    Sorry, my Google Fu has been weak on this one. Thank you!

    Code:
    Private Sub CmdFind_Click()
    On Error GoTo CmdFind_Click_Err
    
    Dim RecordCount As Integer
    
        Me.Requery
        
        RecordCount = 0
        RecordCount = DCount("*", "QryFind")
        If RecordCount = 1 Then
        
            MsgBox ("Yep, just one!")
            
        End If
    
    CmdFind_Click_Exit:
        Exit Sub
    
    CmdFind_Click_Err:
        MsgBox Error$
        Resume CmdFind_Click_Exit
    
    End Sub

  2. #2
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Here's what I would use (replace the names used here by the actual names in your project):
    Code:
    Private Sub CmdFind_Click()
    
        On Error GoTo CmdFind_Click_Err
        Me.Requery    ' Is it useful ?
        IF DCount("*", "QryFind")= 1 Then
            DoCmd.OpenForm "EditForm",,, "ID = " & DLookUp("ID", "QryFind")
        End If
    
    CmdFind_Click_Exit:
        Exit Sub
    
    CmdFind_Click_Err:
        MsgBox Err.Description
        Resume CmdFind_Click_Exit
    
    End Sub
    Alternatively you could use:
    Code:
    Private Sub CmdFind_Click()
    
        Dim rst As DAO.Recordset
    
        On Error GoTo CmdFind_Click_Err
        Me.Requery                          ' Is it useful ?
        Set rst = CurrentDb.OpenRecordSet("QryFind", dbOpenSnapshot)
        With rst
            If Not .EOF Then                ' Cannot use an empty Recordset.
                .MoveLast                   ' Force the Recordset to be populated.
                If .RecordCount = 1 Then    ' Only one row in the Recordset ?
                    DoCmd.OpenForm "EditForm",,, "ID = " !ID
                End If
            End If
            .Close
        End With
        Set rst = Nothing                   ' Clean up.
    
    CmdFind_Click_Exit:
        Exit Sub
    
    CmdFind_Click_Err:
        MsgBox Err.Description
        Resume CmdFind_Click_Exit
    
    End Sub
    Have a nice day!

  3. #3
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    personally Id not much about with opening new forms and what not, I'd set a filter on the current form. and if the search terms return more than one row, who cares, the user can refine their search terms or scroll through the rows till they find what they want.

    less code to maintain, fewer forms openign and closing, less flashing in front of users.
    I'd rather be riding on the Tiger 800 or the Norton

  4. #4
    Join Date
    Feb 2014
    Posts
    2
    Quote Originally Posted by Sinndho View Post
    Code:
            NewID = DLookUp("ID", "QryFind")
    This! This was what I needed to make the whole thing work perfectly. Thank you!!!

    Quote Originally Posted by healdem View Post
    ... who cares ...
    I do. This little bit of code will save the data entry clerks from having to move their hand from the key board to the mouse multiple times. Oh, and they can work faster for the company too.

  5. #5
    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
  •