CODEWITHSUNDEEP
ms-accessvba
Great Crosby
Great Crosby

Reputation: 126

Move to another record via combobox doesn't work in some circumstances

I have two forms: ‘frmClient’, (which has a subform that lists applicants), and ‘frmDisclosure’, which shows details of applicants. On frmClient there is a command button that opens a specified record in frmDisclosure. The procedure is Private Sub Command10_Click() - see below. This works.

The problem is that once in frmDisclosure via frmClient, it is not possible to move to another record. The procedure for opening another record in frmDiscloure is in a combobox control: Private Sub ComboFind_AfterUpdate(). This normally works, but it never works if frmDiscloure has been opened via frmClient. I have tried ‘requery’ and ‘refresh’ in various situations, and have tried closing frmClient once frmDisclosure is open. None of this works. If I want to get to a different record, the only solution I have at present is to close frmDisclosure and reopen it.

\\\\\\\\\\\\\\\\\\\\\\\\\\\\
Private Sub Command10_Click()
If NumForms > 0 Then
    DoCmd.OpenForm "frmDisclosure"
    Forms!frmDisclosure.FilterOn = False
    DoCmd.OpenForm "frmDisclosure", acNormal, "", "[DiscPK]=" & Me.DiscPK, , acNormal
Else
    DisplayMessage ("No form ref for this application.")
    Exit Sub
End If
End Sub
\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\

\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\
Private Sub ComboFind_AfterUpdate()
    Dim rs As Object
    Set rs = Me.RecordsetClone
    rs.FindFirst "[DiscPK] = " & Str(Nz(Me![ComboFind], 0))
    If Not rs.EOF Then Me.Bookmark = rs.Bookmark
End Sub
\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\

Upvotes: 0

Views: 37

Answers (1)

June7
June7

Reputation: 21370

frmDisclosure is opened to a single record, there are no other records to navigate. The RecordsetClone has only one record, so of course code won't find any others. Turn off the filter first: 

Private Sub ComboFind_AfterUpdate()
    Me.FilterOn = False
    With Me.RecordsetClone
        .FindFirst "[DiscPK] = " & Nz(Me.ComboFind, 0)
        If Not .NoMatch Then Me.Bookmark = .Bookmark
    End With
End Sub

As you can see, declaring and setting a recordset object variable is not required. .EOF would probably work just as well, I have just always used NoMatch. This will set focus to record, not filter the form.

If you prefer to display single record, then set the Filter property.

Private Sub ComboFind_AfterUpdate()
    Me.Filter = "DiscPK=" & Nz(Me.ComboFind, 0)
End Sub

Upvotes: 1

Related Questions