473,581 Members | 2,448 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

Access 2003 form bound to a fabricated ADO recordset: problem with sorting and filtering


Here is a sample (and very simple) code that binds an Access 2003 form
to a fabricated ADO recordset:

' Create recordset...
Dim rs As ADODB.Recordset : Set rs = New ADODB.Recordset

' Append one or more fields...
Call rs.Fields.Appen d("Number", adInteger)

' Open recordset and add a few records...
Call rs.Open
Dim num as Integer
For num = 1 to 10
Call rs.AddNew
rs("Number") = num
Call rs.Update
Call rs.UpdateBatch

' Bind the form to the recordset...
Set Me.Recordset = rs

This seemed to make everything work just fine until I've discovered
that form-level sorting and filtering with such recordset does not
work. Worse yet, it makes Access 2003 fail miserably (like a GPF in
old-good days).

Accidentally, our team discovered that the abovementioned problem goes
away entirely if you do the following trick: after appending the
fields to the recordset and before opening it, assign some non-blank
string to the recordset's Source property. Interestingly enough, the
contents of this string almost does not matter. For example, if you
add the following line just before a call to rs.Open:

rs.Source = "Microsoft Access Sucks"

Tada! Now the form can be sorted and filtered without a hitch. Single
character string also works fine. (Based on some experiments, it looks
like any string would do so long as it looks like a valid SQL
statement after a very superficial syntactic check by Access.)

The abovementioned hack (or cheat) is easy and may be funny, but... it
is still looks, smells, and tastes as a hack. Although it does help
with sorting and filtering, it may leave other issues (yet unknown to
us) unresolved or maybe even induce some new issues. So I am wondering
if anyone experienced the same problem and knows a solution better
than the trick described above?

Thnak you,

Sep 27 '07 #1
0 3120

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

by: Richard | last post by:
Hi, I have a form that take some time to load due to many comboboxes and at least 8 subforms. When I filter or sort the main form I get an error message and then Access shuts down. They ask if I want to send the error report to Microsoft. Has anybody seen this type of error message and what can I do to prevent it from happening. Am I doing...
by: AAJ | last post by:
Hi all I have a listbox on a form. If I set its rowsource directly, and the query in the rowsourse returns no data, then the displayed listbox is empty (exactly as you would expect) However.... If I programatically set the rowsource, and the query returns no records then access completely crashes and switches its self off,
by: Madingo | last post by:
I have been using Access 2003 for about a year and I am trying to find out how to create a web test environment to try and transition some of my Access applications on to the web. My stumbling block is that I do not know how to create the web server environment. If anyone knows of a good starting point on how to learn this it would be most...
by: lauren quantrell | last post by:
So many postings on not to use the treeview control, but nothing recently. Is it safe to swim there yet with Access 2000-Access 2003?
by: Bob Alston | last post by:
Most of my Access database implementations have been fairly small in terms of data volume and number of concurrent users. So far I haven't had performance issues to worry about. <knock on wood> But I am curious about what techniques those of you who have done higher volume access implementations use to ensure high performance of the...
by: TD | last post by:
This is the code under a command button - Dim ctl As Control For Each ctl In Me.Controls If ctl.BackColor <> RGB(255, 255, 255) Then ctl.BackColor = RGB(255, 255, 255) End If Next ctl
by: Neil | last post by:
We are running an Access 2000 MDB with a SQL 7 back end. Our network guy is upgrading to Windows Server 2003 and wants to upgrade Office and SQL Server at the same time. We're moving to SQL Server 2005, and, since he already has licenses for Office Pro 2002, he wants to upgrade to that. I've been saying that we need to upgrade to Access...
by: Harry Haller | last post by:
Hello, I want to implement a generic list which will be used to display 7 columns in a GridView. One should be able to sort, filter and page each of the 7 columns. Ideally the filter should be implemented simultaneously for multiple columns - but the data need only be sorted by a single column at a time. Sorting should be both ascending and...
by: jsacrey | last post by:
Hello everybody, I've got a bit of a situation that I could use some guidance with if possible. I work for an auditing firm where my users audit electronic shipping data for customers to see if they've been overcharged for shipments by truck and rail carriers. 99.9% of the time, one of our auditors needs to see all data sent by a...
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main...
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language...
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For...
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the...
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert...
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in...
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.