Solved

Strategy for handling two separate queries/recordsets in Access

Posted on 2012-04-11
11
360 Views
Last Modified: 2012-08-14
I'm looking for input as to how to best implement some required business functionality in MS Access.  (Environment details below)

From a form, a user needs to browse and edit rows in a table. There are two modes they can operate in:  1. Browse ALL, and 2. Browse Unprocessed (or "raw").

While in Browse ALL,  all rows are visible. Any row can be edited and updates saved.  When saved, userid and date/timestamp are saved into two columns of the row (USER_SAVE, and SAVE_DT). If the table has 100 rows, all 100 are visible and available for update.

In Browse Unprocessed mode, only a subset of the data is visible and available .. only those rows which have not been edited and saved before (i.e. USER_SAVE and SAVE_DT both NULL).

Users switch between these modes via radio buttons on the form.  When they switch, the following must occur:

1. switching from Browse Unprocessed to Browse ALL - the position within the table remains the same, i.e. if row ID=32546 was the current record in Browse Unprocessed mode, it will remain the current row once switched to Browse ALL mode.

2. switching from Browse ALL to Browse Unprocessed - if the current row is Unprocessed, it remains the current row after the switch.  If the current row has been processed, then the next unprocessed row will become the current row, i.e. current row ID=32544 has been processed, and so has ID=32545, while ID=32546 has not been processed.  When the mode is switched, row ID=32546 should become the current row.

Those are the business requirements. Here are the approaches/strategies I've considered and toyed with.  My inexperience with MS Access leaves me not knowing what approach is best suited.  Considerations are ..
simplest to implement and maintain,
least likely to have unforeseen side effects,
is aligned with most common approaches.

I created the form with Access GUI, so fields were bound to the underlying recordset, which was handled under the covers by Access.  I was uncertain how to create the subset of rows needed for the Browse Unprocessed mode.

One approach was to apply a filter to create the subset.
   
 sSQL = "([USER_EDIT] IS NULL) AND ([USER_SAVE] IS NULL)"
    DoCmd.ApplyFilter whereCondition:=sSQL

Open in new window

(I know this isn't applied to the recordset, because setting rst.Filter only takes effect for a second recordset that's created from the first with Filter defined. The question is, what object is the ApplyFilter method invoked on?)

Another approach would be to create two separate recordsets, one which queries all the rows and a second with a WHERE clause to select only unprocessed rows, then swap them when the mode changes. Swapping is done by changing the form.Recordset attribute.  When I tried this, the datasheet view showed the row selection worked properly, but the first row was always made the current row.  I even used the FindFirst method to position the cursor, but it didn't work (..or it did work, but then was overridden by something else..)  When I tried this, the binding of the fields to the recordset seemed to be affected, and I'm not sure how to rebind them, or if it's even possible.  As a result, the events associated with the fields were affected and bizarre behaviors appeared.

Another possible approach would be to use only one recordset, but change the RecordSource queries .. one to select all rows, the other select with WHERE clause for unprocessed rows only.  I haven't tried this yet, but it seems promising.

Before I venture out on that path, I would like some input from the Experts.  Please let me know your take on the viability of these approaches, and suggest others which I haven't explored.  It's very possible that there is a simple approach that I'm not aware of.  

All suggestions and analysis of approaches is most appreciated.
At this point, I'm all ears.

Environment:

Using Access 2007, with libraries for DAO:
 - Microsoft Office 12.0 Access database engine Object Library
 - Microsoft Jet and Replication Objects 2.6 Library

NOTE: For development, I'm running this against a local Access database.  For production, it must run against a remote SQL Server database.  I recall some citations along the way that stated there would be some limitations for some database connections.
0
Comment
Question by:MiltonLClark
  • 6
  • 5
11 Comments
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 37833967
?

Can you simplify this possibly?
0
 

Author Comment

by:MiltonLClark
ID: 37835877
I'd be glad to.. how do you suggest?
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 37836000
Shorten it to one distinct question....

Or simply post a sample of the DB and state exactly what you want to do...

Sample database notes:
1. Back up your database(s).
2. Combine the front and back ends into one database file.
3. Remove any startup options, unless they are relevant to the issue.
4. Remove any records unless they are relevant to the issue.
5. Delete any objects that do not relate directly to the issue.
6. Remove any references to any "linked" files (files outside of the database, Images, OLE Files, ...etc)
7. Remove any references to any third party Active-x Controls (unless they are relevant to the issue)
8. Remove, obfuscate, encrypt, or otherwise disguise, any sensitive data.
9. Compile the code. (From the VBA code window, click: Debug-->Compile)
10. Run the compact/Repair utility.
11. Remove any Passwords and/or security.
12. If a form is involved in the issue, set the Modal and Popup properties to: No
    (Again, unless these properties are associated with the issue)
13. Post the explicit steps to replicate the issue.
14. Test the database before posting.

In other words, ...post a database that we can easily open and immediately see and/or troubleshoot the issue.
And if applicable, also include a clear graphical representation of the *Exact* results you are expecting, based on the sample data.

JeffCoachman
0
 

Author Comment

by:MiltonLClark
ID: 37839586
To boil it down to one distinct question, it would be:  what approach should I take to achieve the mode swapping I defined.

Secondly, as far as posting a database goes, that seems to be a moving target.  I code one approach and get most of the functionality working, but then it "breaks" a different part of the application.  I can post the database with the latest problem if that's what's needed.

Thanks everyone.
0
 
LVL 74

Accepted Solution

by:
Jeffrey Coachman earned 500 total points
ID: 37839823
Still confused.

Why are you using queries/recordsets in the first place?
It seems to me like you could just have a yes/no field in the table: IsProcessed
Then check this box when the record is processed.
Then just filter the Form accordingly:

Browse Unprocessed:
me.filter="IsProcessed=False"
me.filterOn=True

Browse All:
me.filter=""
me.filterOn=False

JeffCoachman
0
Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

 

Author Comment

by:MiltonLClark
ID: 37842886
That's precisely the kind of input I'm looking for.  Seems like it's gotten overly complicated.
I think that's what I did at one point.  I'll give that a try and let you know how it works.

I'll get back to you.

Thanks.
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 37843325
OK
0
 

Author Comment

by:MiltonLClark
ID: 37843389
Right on target, JeffCoachman .. worked like a charm, and greatly simplifies the app structure.

Thanks again!!
0
 

Author Closing Comment

by:MiltonLClark
ID: 37843405
This is exactly what I was hoping for.  This is why my subscription to Experts Exchange is so valuable.

Thanks again, boag2000/JeffCoachman.
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 37854514
;-)
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 37854523
Remember, I only posted the design change and the basic code.

You took the initiative and adapted this to work in your database on your own with no help from me...
So give your self some credit too!
;-)

Jeff
0

Featured Post

How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

Join & Write a Comment

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…
My experience with Windows 10 over a one year period and suggestions for smooth operation
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 …
The viewer will learn how to simulate a series of coin tosses with the rand() function and learn how to make these “tosses” depend on a predetermined probability. Flipping Coins in Excel: Enter =RAND() into cell A2: Recalculate the random variable…

746 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

10 Experts available now in Live!

Get 1:1 Help Now