Setting a Form's Recordset Order

Posted on 2011-10-08
Last Modified: 2012-05-12
I have a recordset of payments made for attending an Event.  Typically these are a deposit, a staged payment and a final payment.  The payments are displayed in a form and there are Next and Previous buttons to scroll through the payments in date of payment order.

The form fJDWEventPay is called from the form fJDWBookings with the following code snippet.

In Form_Load of fJDWEventPay I set on a filter with the passed argument - as follows:

'set filter
 Me.Filter = "jdwp_BookingRef = " & Me.OpenArgs
 Me.FilterOn = True

and I set the order - as follows:

'set order
Me.OrderBy = "jdwp_Date"
Me.OrderByOn = True

... but the first payment record to display is not always the first record by date - it's the second or third (appears to be entirely random).

I have tried setting the order and then the filter but it makes no difference

Any ideas anybody?
Dim lngNoBookings as Long
Dim stDocName As String

lngNoBookings = DCount("*", "tJDWBookings", "jdwb_Event=" & lngEventID & " And jdwb_BookingID=" & lngBookingID & " and jdwb_Status=" & Chr(34) & strStatus & Chr(34))
    If lngNoBookings > 0 Then
        stDocName = "fJDWEventPay"
        DoCmd.OpenForm stDocName, , , , , acDialog, lngBookingID  ' pass Open Args

        MsGBox "No Bookings ... " 'etc

    End If

Open in new window

Question by:MikeDTE
    LVL 61

    Accepted Solution


    It may depend on where jdwp_date is coming from (query or directly from a date field in a table).

    See if this makes any difference:

    Me.OrderBy = "cdate(jdwp_Date)"
    LVL 84
    What's the Recordsource of the Form? Is it based on a Query, a Table, or a SELECT statement (or something else)?

    Is the jdwp_Date field an actual Date datatype?

    By "entirely random", do you mean that the order changes (i.e. Record #1 is in slot #1, but if you reopen the form Record #1 is now in the Slot #3, eg), or that it's always the same, but not in the right order?

    Author Comment

    The recordsource is a table with a filter.  The jdwp_Date is a date field.

    The Me.OrderBy = "cdate(jdwp_Date)" resolves the problem.  I have legacy data and some of the Date fields in the tables are defined differently from others.  jdwp_Date was one of them.

    Thanks for the posts - now sorted

    Author Closing Comment

    Excellent advice - as usual

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Free Trending Threat Insights Every Day

    Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

    The first two articles in this short series — Using a Criteria Form to Filter Records ( and Building a Custom Filter ( — discuss in some detail how a form can be…
    In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
    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…
    Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …

    760 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

    7 Experts available now in Live!

    Get 1:1 Help Now