CODEWITHSUNDEEP
ms-accessms-access-2010
Marc
Marc

Reputation: 16512

Find record with ID

I'm trying to move to a record that has a certain ID.

I tried the solution on this post: MS Access search for record by textbox instead of dropdown

but no success

Here's my code

Private Sub btnShowPrevious_Click()
    Dim rs As DAO.Recordset
    Set rs = Me.RecordsetClone
    rs.FindFirst "[ID]=" & ParentID
    If rs.NoMatch Then
        MsgBox "Sorry, no such record '" & ParentID & "' was found.", _
               vbOKOnly + vbInformation
    Else
        Me.Recordset.Bookmark = rs.Bookmark
    End If
    rs.Close
End Sub

It always hit no match but parentID = 1 and there's one record with ID = 1..

Anyone has an idea of what is wrong?

Thank you

The record source is this table:

CREATE TABLE [dbo].[ProposalFollowUp](
[ID] [int] IDENTITY(1,1) NOT NULL,
[ProposalID] [int] NOT NULL,
[MillID] [int] NULL,
[ClientID] [int] NULL,
[Comment] [nvarchar](max) NULL,
[Method] [nvarchar](128) NULL,
[Contact] [int] NULL,
[ContactDate] [datetime] NULL,
[Done] [bit] NOT NULL,
[CreatedBy] [nvarchar](50) NULL,
[CreatedDate] [datetime] NULL,
[ModifiedBy] [nvarchar](50) NULL,
[ModifiedDate] [datetime] NULL,
[EAIEmployee] [nvarchar](50) NULL,
[PersonInCharge] [nvarchar](50) NULL,
[ParentID] [int] NULL,

here's a screenshot of the form properties

enter image description here

Finaly, if I show the navigation bar, I can see there's a filter. Probably because I open the form like this

DoCmd.OpenForm "ProposalsFollowUp", , , "[ID] = " & txtID, acFormEdit, acDialog

If I remove the filter, it works.

Alright Here's the final code

Private Sub btnShowPrevious_Click()
    Dim parent As Integer
    parent = ParentID
    Me.Filter = ""
    Dim rs As DAO.Recordset
    Set rs = Me.RecordsetClone
    rs.FindFirst "[ID]=" & parent
    If rs.NoMatch Then
        MsgBox "Sorry, no such record '" & parent & "' was found.", _
               vbOKOnly + vbInformation
    Else
        Me.Recordset.Bookmark = rs.Bookmark
    End If
    rs.Close
End Sub

Upvotes: 1

Views: 5989

Answers (1)

Fionnuala
Fionnuala

Reputation: 91376

After checking everything obvious such as ensuring the recordset includes the data you are searching for and that there are no filters, you can consider a problem with the form. A filter is shown at the bottom of the form in Access 2010 and can be removed in VBA by:

Me.FilterOn = False

Or by clicking the filter button:

form filter access 2010

Odd things happening in a front-end are often due to corruption of some sort. You need to regularly back-up, compact and repair and decompile when you are developing. If you have linked tables, it is often a good idea to refresh the links.

If you create a form you do not want to lose and it becomes corrupt, you can copy to a new form with cut and paste or you can export to text and import.

Decompile:

"c:\Program Files\Microsoft office\office\msaccess.exe" /decompile "c:\My Documents\MyDatabase.mdb"

-- http://allenbrowne.com/ser-47.html

Save As Text:

Application.SaveAsText acForm, "FormName", "z:\docs\tmp.txt"
Application.LoadFromText acForm, "restoredForm", "z:\docs\tmp.txt"

Upvotes: 1

Related Questions