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