Learn how to a build a cloud-first strategyRegister Now


Setting a Form's Recordset Order

Posted on 2011-10-08
Medium Priority
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
  • 2
LVL 61

Accepted Solution

mbizup earned 1000 total points
ID: 36935601

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 85
ID: 36935606
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

ID: 36935618
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

ID: 36935619
Excellent advice - as usual

Featured Post

NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
If you need a simple but flexible process for maintaining an audit trail of who created, edited, or deleted data from a table, or multiple tables, and you can do all of your work from within a form, this simple Audit Log will work for you.
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

810 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