Access Form record count differs from Table record count

Posted on 2011-05-03
Last Modified: 2012-05-11
I have a DB with a table called ‘TestBooking’ to which I have a query called ‘TestBooking Query’
I have a form with the RecordSource as ‘TestBooking Query’
For test purposes I have entered some values into a field called [Works Order No].
(4 records as follows:- “79804”, “79805”, “79806”, “79807”)

I want to the user to enter a number (as string) onto my form.
Then the system finds the record and then the record can be displayed/edited.

The problem:
The form only shows ‘records 1 of 1’ whereas the DB (& therefore query) has 4 records.

This means that if I use the built-in ‘find’ it cannot find any records.
(even the < or > buttons are grayed).

I have also written some VBA event code to find a string within [Works Order No] and even this can’t find any records.

I am fairly new to Access so I must be doing something wrong.

Can anybody help?
Question by:usatrfe
    LVL 12

    Expert Comment

    If this is all test data and not confidential,  maybe you could upload your database file for the experts to have a look at.....
    LVL 11

    Accepted Solution

    Is the form set to use the "query" or table as input?
    This is not needed if you are searching to fill a form based on an entered key.
    What you should have is a drop down/combo box for the input and Select the item you want then fill the form with the data ( MOdify the Recordsource)

    Look at form  frmlToolsLookUpAfterUpdate
    I locked the data on this form so it cannot be updated
    and see the "AfterUpdate" for Tool_PK
    Less code and changes the recordsource.
    It will show the last one used when it opens.
    LVL 74

    Assisted Solution

    by:Jeffrey Coachman
    Then it looks like the forms is opened to display a specific record.

    In order for your "Search" to display another "Filtered" record, you will have to remove this filter, then apply the new filter

    Try something like this as a start, on a button on the form:

    'Clear the old filter
    'Apply the new filter
    Me.Filter="[Works Order No]"= &  "'" & me.txtWorksOrderNo & "'"


    Author Closing Comment

    Sorry about the delay in finalising this question.

    My form was open to display a specific record so removing the filter worked but prevented me from displaying data (blank fields) unless I did a search. I added a combobox to make the search more user friendly. I then created a 'default' dummy record that prevented the user from seeing empty boxes when they first use the form.

    Thanks for your kind assistance.

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Highfive Gives IT Their Time Back

    Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

    I see at least one EE question a week that pertains to using temporary tables in MS Access.  But surprisingly, I was unable to find a single article devoted solely to this topic. I don’t intend to describe all of the uses of temporary tables in t…
    The new Microsoft OS looks great, is easier than ever to upgrade to, it is even free.  So what's the catch?  If you don't change the privacy settings, Microsoft will, in accordance with the (EULA) you clicked okay to without reading, collect all the…
    In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…
    In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …

    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

    17 Experts available now in Live!

    Get 1:1 Help Now