Option Groups - help!!

[hermanko]

Hi all,

I would like some help on my problem:

I have a database used to import periodic data from Excel on quarterly
(for example) data on a particular Fund. Each time i import data, the
fields are identical, and the data MAY or MAY NOT be different....and i
have queries that will extract the information that duplicates. Keeping
in mind that duplication isn't "bad" or "wrong" because it might mean
the newer info has been updated. for example, two of the fields
involved are Fund Name, and Proxy. when i receive new incoming data, i
will append the new data to the underlying Table, and there will most
certainly be duplicates in Fund Name...but the odd time, I might have a
different Proxy used associated with that Fund Name (or perhaps it
remains identical). the point is a user needs to view the duplications
to determine what should be done (which to delete from Table).

I have queries set up to extract this info. What i would like to have
is a form that could display the query results and have Option Groups
so the user can select which of the two duplicates he/she wants to
remove/keep. The point is, the number of duplicates will vary on each
import.

I am trying to figure out what the best approach is to solve this. Can
anyone suggest anything? Thanks very much
Herman

 

[Guest]

I don't think option groups are the best approach for this. I would
seriously consider using a List Box control with multi select set to Yes.
Then present the records in the list box and allow the user to select which
to keep or delete.

 

[hermanko]

Ok, I have set up a list box control with multiselect to SIMPLE. Once
the user has selected all the records he wishes to delete from the
underlying query (user-selected), I have a cmd button that should
delete all records selected.

How do I pass all the selected records to a delete query?

 

[Guest]

Herman,

Another approach is to add a Yes/No field to your table, and use a
continuous subform based on a query that displays the multiple records that
includes a checkbox. A query run from a command button can then delete the
selected (or unselected) records.

Sprinks

 

[hermanko]

Hi, thanks Sprinks, that approach sounds more efficient.

However, I am a newbie at Access... and I was wondering if you could
elaborate a little on how to have a delete query remove all the
selected checkboxes? I assume once a checkbox is selected there is a
value stored? So, do i just call all those values to be deleted in my
delete query?

And is there a way to have another checkbox at the top where then user
can select to "Select all old entries" of each duplicate (that would be
the default action.... to get rid of all the older duplicate records as
the new ones are likely the most uptodate, but could still require user
intervention on special cases. This way if i have 200 duplicates, the
user saves a lot of time with the "Select all old entries" first, then
modifying it as he/she pleases.

Thanks
Herman

 

[Guest]

I noticed in your response to Spinks you are a newbie. Given that, his
solution would be easier to implement. Mine takes a pretty good knowledge of
VBA and SQL to work well. Perhaps you should take his approach.

If you decide you want to try the List Box approach, post back and I will
post some sample code.

 

[Guest]

Hi, Herman.

I'm a little confused. I don't mean that the delete query would remove the
*checkboxes*, it would rather delete the records that were checked. Maybe
that's what you meant.

When you check a checkbox bound to a YesNo field, the field's value becomes
True (-1). The SQL for the Delete query would be:

DELETE YourTable.*
FROM YourTable
WHERE (((YourTable.YourYesNoField)=True));

As to a master "Select All Old Entries" checkbox, you could certainly use
the AfterUpdate event procedure of an unbound checkbox on your main form
(with your data records in a continuous subform) to run an Update query on
the old records, providing you have criteria by which you know they're *old*.
The SQL would be:

UPDATE YourTable
SET YourTable.[YourYesNoField] = True
WHERE <your criteria that determines *old* records>

However, it seems that you could run this query yourself when you add the
new data. Then the form is ready to go when it opens.

Hope that helps.
Sprinks