• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 549
  • Last Modified:

Displaying & using Checkboxes in each row of a subform - MSAccess 2010

Hi

Although I have used MS-Access as a  back end for a number of applications, I am new to developing an application based on Ms-Access forms, reports, queries etc

I am faced with a very peculiar requirement and situation for which I need expert advice. Let me explain in detail

I have inherited an Access DB application that has a customer table.  This customer table has literally been plucked out of invoices in an Excel worksheet. As a result there are duplicate customers within the customer table.
 
When this customer data was created, no one thought of remediating the data and removing the inherent redundancies in the customer records and other related entities such as orders and quotes . As a result we need to filter out or mark customer records as duplicates at the time of adding new customers.  It is a pretty messed up situation and without going further into the murky details let me tell you what I have been asked to do.
 
I have an add customer form with a save and cancel button. This form has a sub form the visibility of which has been turned off in the form load event. When the user clicks the save button, the application dynamically creates a conditional SQL statement that returns the number of possible existing duplicates in customer table (depending on the information that the user has entered during data entry).
 
For instance if the user has entered the First Name, Last Name & City in the data entry form, the sub form displays the existing customers that match the First Name, Last Name & City along with other attributes of the customer.
 
Everything is working fine up to this point.
 
The problem is when I need to select a record from the sub form and mark it as a possible duplicate. I wanted to create a checkbox within each row of the sub form that could be used for marking a record as duplicate. I have tried to use an ADODB recordset with a Boolean column  and assign this recordset to the recordset property of the subform as follows
 
Set me.Subformname.Form.Recordset = AdodbRecordset
 
Now I have tried this and it doesn’t work. I see the selected column in the subform but with a value of zero instead of a checkbox.
 
I am requesting your assistance by helping me with answers to the following questions
 
1. Is is possible to introduce a virtual checkbox column in a subform by using an ADODB recordset?

2. If so, where could I be going wrong?
 
Please note that I do not want to add a Boolean (Yes/No) column in the customer table.

I am really in deep with this problem and need to get a solution to the client or tell him it cant be done.  The solution is by itself very unconventional and in my years I have never seen anything like this being done.

I hope I have explained the scenario clearly. Please ask for any clarifications or any additional info

Regards
0
Rahul_Hans
Asked:
Rahul_Hans
  • 3
  • 3
  • 3
  • +1
1 Solution
 
IrogSintaCommented:
Using the SQL statement that returns the number of possible duplicates, append the results to a Temp table instead.  You would need to empty this table each time prior to appending.  This temp table should also have a Yes/No field included with its default value set to 0.  The recordsource of your subform should use this temp table as it's record source.  Just requery this subform after the temp table has been repopulated.
0
 
Rahul_HansAuthor Commented:
Thanks for your reply.

I am aware of this possibility. If I were to output the SQL query results into a temp table, then i might as well add a Yes/no column to the customer table instead.

With a temp table there might be a performance degradation (if not now...then in the future) when say I have to search 60 K customer records and append 2000 possible duplicate records.

The fact is that using an ADODB recordset with a Boolean field as the recordset for a form works perfectly okay. This does create a checkbox column on the form. My problem is when I want to the use the ADODB recordset for a sub form.
0
 
IrogSintaCommented:
Do you have a checkbox control to your subform that is bound to the Boolean field?
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
Rahul_HansAuthor Commented:
Yes I do. If there is any additional info that may be required, then let me know. Also, would it be possible for me to zip the .accdb file and send it to you so that you could take a look at any thing else i might have missed out?
0
 
IrogSintaCommented:
Sure, you could zip it or if it's not too large, just upload it without zipping.
0
 
Rahul_HansAuthor Commented:
Okay I have uploaded the zip file. The zip file contains the following files

1. BuffetMeister_0.0.17_Development (Access DB)
2. viewthisfirst.png

Once you open the Access DB just open the form frm_customer_add and follow the instructions as per the image file

I am totally at my wits end right now with this problem. I am grateful for your help.

Also, if in worse case scnario this doesn't work out, is there any other way of selecting a record in the sub form and marking the record as selected by changing the background color or something like that? Probably a customized row context menu.

If need be I can put this in a separate question.

Thanks a lot.
BuffetMeister-0.0.17-Development.zip
0
 
Leigh PurvisDatabase DeveloperCommented:
Hi

Just to backup a little...

>> 1. Is is possible to introduce a virtual checkbox column in a subform by using an ADODB recordset?

Yes, absolutely.  However it's not trivial.  There are two example methods of doing so on the examples page here - it's the last two on the page.
Although the wrapping class should, hopefully, make it easier to implement (i.e. you don't actually need to understand what's happening beneath the surface) it's still a bit of effort.
I'd imagine the last example would be the one you'd want.

However adding a non-data checkbox is much easier with a temp table (as perhaps already mentioned).  There's a direct example of that too in the "List Select" demo. (It's the first method in that demo.)
It has the advantage that you could then also perform set based (query/update) operations on the results - whereas any recordset method is always going to be iterative.

Have a look and see if you can implement it into your example yourself.
If not, shout.

Cheers.
0
 
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
+1 for the temp table idea.

Using a Temp table would be a better solution than adding a checkbox to the underlying table if this is a mult-user app, since each user would have their own personal temp table to work with, and would not disrupt others who might also be performing similar tasks.
0
 
Leigh PurvisDatabase DeveloperCommented:
You know - I didn't even address the concept of adding a boolean field to the underlying table as I dislike the concept so profoundly. :-p

I suppose as a temporary fix as part of data-cleanup, so be it.  But this appears to be part of users' daily interaction with the application.  So it would be a permanent feature.
A field added to a data entity for functionality reasons...  Big "eurgh".
0
 
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
Agreed - tables should be designed to house data, and not to provide UI features. Temporary tables can do this, of course, but then those are buffers between the UI and the data, so some concessions would be made for that.
0
 
Leigh PurvisDatabase DeveloperCommented:
Well, far be it from me to tootle my own trumpet...
But the answer I gave, pointing to the example I cited, is exactly what they OP was asking for.  (Creating an "extra" field in an ADO recordset and being able to bind that to a form.)

It's one of the very rare occasions where that exact functionality has been asked for (of course, I'm sure the millions that will have read the actual articles have all implemented it too :-p)

https:#a38291325
0

Featured Post

Free Backup Tool for VMware and Hyper-V

Restore full virtual machine or individual guest files from 19 common file systems directly from the backup file. Schedule VM backups with PowerShell scripts. Set desired time, lean back and let the script to notify you via email upon completion.  

  • 3
  • 3
  • 3
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now