Results 1 to 13 of 13
  1. #1
    Join Date
    Aug 2006
    Posts
    126

    Unanswered: FindRecord = Not Found method

    I have a form with an unbound combobox [SelectNameBox] in the header. SelectNameBox source is SELECT DISTINCTROW LastName FROM the same query as the form itself. The AfterUpdate property of the combobox is to run Macro.FindName.

    The FindName macro is:
    GotoControl [LastName]
    GotoRecord First
    FindRecord =[SelectNameBox] direction is DOWN
    SetValue [SelectNameBox] Null 'to clean up the cbo
    GotoControl <first control on form>

    If [SelectNameBox] is not found in [LastName], the first record is shown.
    I'd like to respond to the "not found" condition with other actions rather than just going to the first record.

    Is there a way to recognize the "not found" condition of FindRecord? Or must I go to VBA and something like a DoCommand Find to get this functionality?
    Pete T.
    PGT

  2. #2
    Join Date
    Mar 2009
    Posts
    5,441
    Provided Answers: 14
    Use VBA, it's easy:

    Code:
    Private Sub SelectNameBox_AfterUpdate()
    
        Dim rst As DAO.Recordset
        Dim strSearch As String
        
        strSearch = "LastName = '" & Me.SelectNameBox.Value & "'"
        Set rst = Me.RecordsetClone
        rst.FindFirst strSearch
        If rst.NoMatch Then
            MsgBox "Not found: " & Me.SelectNameBox.Value, vbInformation, "SelectNameBox"
        Else
            Me.Bookmark = rst.Bookmark
        End If
        Set rst = Nothing
        Me.SelectNameBox.Value = Null
        
    End Sub
    Have a nice day!

  3. #3
    Join Date
    Aug 2006
    Posts
    126
    Sinndho,
    Thanks, I've been in the process of converting all but the simplest Macros into VBA as I learn more VBA.

    In the 'nomatch', I'll ask if a new record should be added. How would you set Bookmark to go to a new record? (is that EOF)?

    If rst.NoMatch then
    NewRecYN = MsgBox("Name not found. Do you want to add a new record", vbYesNo, "Name not Found")
    If NewRecYN = vbYes
    then ????? <take form to new record>
    End If
    else <etc.>

    Thanks,
    Pete T.
    PGT

  4. #4
    Join Date
    Mar 2009
    Posts
    5,441
    Provided Answers: 14
    You cannot do it that way because the recordset you created with
    Code:
    Set rst = Me.RecordsetClone
    is just a snapshot of the actual data set of the form, so it cannot go to a record that does not exist in the original. Also, it's not recommanded to mess around with this kind of recordsets (in certain circumstances, trying an rst.AddNew or something like that on such a recordset can cause Access to fail "Access has encountered an error and must be closed... etc.").

    For adding a new record in case the one you're looking for is not found, use:
    Code:
    If rst.NoMatch = True Then
        rst.Close    ' Not mandatory but the sooner the best.
        DoCmd.GoToRecord acDataForm, Me.Name, acNewRec
    Else
        Me.Bookmark = rst.Bookmark 
        rst.Close
    End If
    Set rst = Nothing
    Have a nice day!

  5. #5
    Join Date
    Aug 2006
    Posts
    126
    Excellent,
    I'm still getting my brain around the RecordsetClone idea and how to work with it.
    Thanks
    PGT

  6. #6
    Join Date
    Aug 2006
    Posts
    126
    Sinndho,
    One final question. Below is the code. The SetFocus events all work EXCEPT the one for the NO answer to the MsgBox. After selecting NO the form has no control in focus. Can you explain why just this one line doesn't work?

    Code:
    Private Sub SelectNameBox_AfterUpdate()
        Dim rst As DAO.Recordset
        Dim strSearch As String
        
        strSearch = "LastName = '" & Me.SelectNameBox.Value & "'"
        Set rst = Me.RecordsetClone
        rst.FindFirst strSearch
        If rst.NoMatch Then
            NewRecYN = MsgBox("Name not found. Do you want to add a new record", vbYesNo, "Name not Found")
            If NewRecYN = vbYes Then
                rst.Close
                Set rst = Nothing
                Me.SelectNameBox.Value = Null
                DoCmd.GoToRecord acDataForm, Me.Name, acNewRec
                Me.LastName.SetFocus
            Else
                rst.Close
                Me.SelectNameBox.SetFocus
                GoTo EndofSub
            End If
        Else
            Me.Bookmark = rst.Bookmark
            rst.Close
            Me.SelectNameBox.SetFocus
        End If
    EndofSub:
        Set rst = Nothing
        Me.SelectNameBox.Value = Null
    
    End Sub
    
    Private Sub SelectNameBox_GotFocus()
    Highlight
    End Sub
    
    Private Sub SelectNameBox_LostFocus()
    UnHighlight
    End Sub
    PGT

  7. #7
    Join Date
    Mar 2009
    Posts
    5,441
    Provided Answers: 14
    Quote Originally Posted by Pete Townsend View Post
    Can you explain why just this one line doesn't work?
    Not really. I've tested this code in one of my test databases (I just replaced "LastName" by "Customer") and it works as expected. The commented lines are useless in the procedure:
    Code:
    Private Sub SelectNameBox_AfterUpdate()
    
        Dim rst As DAO.Recordset
        Dim strSearch As String
        Dim NewRecYN As Variant
        
        strSearch = "Customer = '" & Me.SelectNameBox.Value & "'"
        Set rst = Me.RecordsetClone
        rst.FindFirst strSearch
        If rst.NoMatch Then
            NewRecYN = MsgBox("Name not found. Do you want to add a new record", vbYesNo, "Name not Found")
            If NewRecYN = vbYes Then
                rst.Close
                Set rst = Nothing
                Me.SelectNameBox.Value = Null
                DoCmd.GoToRecord acDataForm, Me.Name, acNewRec
                Me.Customer.SetFocus
            Else
                rst.Close
        '        Me.SelectNameBox.SetFocus
        '        GoTo EndofSub
            End If
        Else
            Me.Bookmark = rst.Bookmark
            rst.Close
            Me.SelectNameBox.SetFocus
        End If
        'EndofSub:
            Set rst = Nothing
            Me.SelectNameBox.Value = Null
    End Sub
    When the answer to MsgBox is No, the focus simply returns to the last control that had it before the message box, and this control is precisely the combo box.
    Have a nice day!

  8. #8
    Join Date
    Aug 2006
    Posts
    126
    I agree the GoTo line is extraneous; it and the label were added during testing various reworks. The SetFocus was added in the NO portion because the focus was not (and is still not) going back to the last control (the cbo).

    The Combo box and 2 command buttons are in the header of the form. For some reason the focus returns to one of the buttons instead of the cbo even with that SetFocus line in the code. I tried changing the tab order of the header so that the cbo was first, but that didn't help.

    One thought that just occured to me is that the YES SetFocus (which works) is to a control in the Detail part of the form. Do I need some extra qualifier (other than Me.) to address a control in the header? Other than that idea, I'm stumped.
    PGT

  9. #9
    Join Date
    Mar 2009
    Posts
    5,441
    Provided Answers: 14
    This is strange. Do you have some code in the Current, Resize or Gotfocus event of the form?

    You can compare your form to one that behaves as expected, see the attachment.
    Attached Files Attached Files
    Have a nice day!

  10. #10
    Join Date
    Aug 2006
    Posts
    126
    Sinndho,
    Finally getting back to this project from another one. I tried to open your .zip and it says corrupted. I had a similar problem in another thread.

    Just for kicks, I deleted the lines in the NO section of the If-Else and retyped them. (Once before I found an extraneous non-print character had snuck in). No luck. So I change from the SetFocus method to DoCmd GoToRecord "SelectNameBox", and it still goes back to the form, with the button highlighted instead of "SelectNameBox". As you suggested, I checked for any other event that might have snuck in, but found nothing. Googling "setfocus in header" I found 2 posts that referred to it being flaky, but with no discussion or resolution.
    Unless you or another lurker comes to the rescue, it will be left as-is; the No response will hopefully be a rarity and will just have to be lived with, at least it goes back to a header control and not one in the detail.

    Are we having fun yet? Happy Thanksgiving.
    Pete
    PGT

  11. #11
    Join Date
    Aug 2006
    Posts
    126
    Oops, just re-read. I meant to say DoCmd GoToControl not GoToRecord
    PGT

  12. #12
    Join Date
    Aug 2006
    Posts
    126
    Ah !!!! I think I've found part of the problem. I changed the tab order for the 3 header controls and, voila, the form returned to the new control that followed the SelectNameBox. It appears that when you hit Enter after typing an unknown name in the cbo, the tab control actually changes the focus to the next control, so when the MsgBox closes, it returns to that next control.
    Now the other part of the problem is why the SetFocus doesn't set it back to the cbo. We know the Me.SelectNameBox.Value = Null is working; why not the .SetFocus????
    PGT

  13. #13
    Join Date
    Mar 2009
    Posts
    5,441
    Provided Answers: 14
    Just a wild shot here: What happens when you try to transfer the focus to another control in the form?

    P.S. I uploaded the file again, see attachment.
    Attached Files Attached Files
    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
  •