can not locate record

Posted on 2011-10-28
Last Modified: 2012-05-12
I've tried several "wizards" to find a record I select so I can modify the information, to NO success. The attached db opens to the form I'm struggling with.
Question by:Frank Freese
    LVL 61

    Expert Comment

    It looks like the name of the event procedure you are using does not match the name of the control.

    Do the following:

    - Unbind your Store combo by removing its controlsource property (you dont want it editing the underlying field).

    - Add the following code to the combos After Update event:

    Private Sub cboStore_AfterUpdate()
    ' Find the record that matches the control.
        Dim rs As dao.Recordset
        Set rs = Me.RecordsetClone
        rs.FindFirst "[BonusEarnedID] = " & Nz(Me.cboStore, 0)
        If Not rs.EOF Then Me.Bookmark = rs.Bookmark
    End Sub

    Open in new window

    Finally - Set the form's Data Entry property to NO.
    LVL 61

    Expert Comment

    A note about the Data Entry property - setting it to 'yes' sets your form up so that the user can only enter new records.  With this setting, finding existing records simply wont work.

    Setting it to NO gives the user access to the existing records (which is what you need for this functionality)

    Author Comment

    by:Frank Freese
    stupid me....I forgot about the Data Entry property since I stole this form from another. I posted your code - data when the form opens but the combo box CboStore is empty not showing even the one record I created? Also, the control cboStore is unbounded now
    LVL 61

    Expert Comment

    What I generally do in my own forms is set up an unbound combo box in the form's header for use in searching for records, and a seperate bound control with the rest of the controls in the detail section if needed for editing.  

    In this case, I'd suggest adding a text box for store ID in the detail section of the form.
    LVL 61

    Accepted Solution

    The reason I use seperate controls like that is that I've found that using the same control for editing and searching invariably results in data entry mistakes, problems and confusion for the users.

    Author Comment

    by:Frank Freese

    Author Closing Comment

    by:Frank Freese
    thank you

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    How your wiki can always stay up-to-date

    Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
    - Increase transparency
    - Onboard new hires faster
    - Access from mobile/offline

    Suggested Solutions

    Title # Comments Views Activity
    Forms or tables Name 4 47
    C# primary key 9 46
    Access 2013 combo box not working 3 10
    Access Query concatenation 12 0
    In the previous article, Using a Critera Form to Filter Records (, the form was basically a data container storing user input, which queries and other database objects could read. The form had to remain op…
    I originally created this report in Crystal Reports 2008 where there is an option to underlay sections. I initially came across the problem in Access Reports where I was unable to run my border lines down through the entire page as I was using the P…
    Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
    In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.

    779 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    14 Experts available now in Live!

    Get 1:1 Help Now