Import spreadsheet with pop up

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi,
I have a macro that I run through my switchboard that imports data from one
specific file. Unfortunately this can cause duplicates if a user clicks the
swithboard button without updating the spreadsheet.
I would like a pop-up that identifies if a record is duplicated. Something
like "This record already exists, do you still want to import?"
Is this feasible and if so what is the best way to do this?
Thanks,
Graeme
 
Graeme,

Do you mean you only import one record at any given time?

If you are importing a record that already exists, how would we "know"
that it already exists? What aspect of the data would identify it as a
duplicate? The data in one of the fields?
 
Thanks Steve,
usually there is only one record at a time but there is the possibility that
there be more.
The fields that would determine duplicity are: Style, Units, Price,
Character, Description. If all of these fields are the same then the record
is a duplicate.

Cheers,

Graeme
 
Graeme,

One approach here would be to set a Unique Index on that particular
combination of 5 fields. Then, when you try to import the duplicate
record, it simply will not be added.

By the way, I would personally always import to a temporary table, and
then run an Append Query to move it from the temporary table to the main
table.

If you do like this, and if you really want a warning prompt as you
suggested before, this would be possible by having a query set up that
joins the main table with the temporaty table on all 5 of the fields you
mentioned. Then, after importing the data to the temporary table, you
could test to see if the query returns any records, and if so, it means
it's a duplicate, and then you can show the message box. If you were
doing this in a macro, you would use a MsgBox action, with a Condition
something along these lines:
DCount("*","YourQuery")>0
 
Thanks Steve,
from what I understand the Unique Index requires that each and every one of
the 5 fields is unique.
What I need is a unique combination of those 5 fields.
For example, Units could be 500 for many different records. However if each
of the 5 fields is exactly the same as another record then it is a duplicate.

Or perhaps I'm not setting up the Unique Index correctly?
I have called the index "StyleDuplicate" and set the Unique property box to
yes.
 
Graham,

No, a unique index applies to the combination of fields, as you wanted.
Duplicates can exist in any of the individual fields. It sounds like
you have done it correctly. In the Indexes dialog, you just put the
name of the index in the first row, and then enter all 5 fields in the
second column.
 
Thanks Steve,
that seems to work now.
I want to automate the suggestion that you made. Not sure what I need to do
to create a temporary table. Or is this not feasible?
Also, in the MsgBox action I can't find a condition function (Like you'd
find in OpenForm for example).
Cheers,
Graeme
 
Graeme,

Regarding the temporary table idea, you can make a table in the frontend
database, with the structure you want for the imported data. Then you
can use a Delete Query (which can be run from within the macro using the
OpenQuery action), to clear any residual data from this table prior to
your import. I am not sure how you are importing the data, or where
it's coming from, I don't think you mentioned, but I assume you have
this part of it under control. So you just import to this now empty
table. Then, based on this table, make an Append Query to run the data
into your main table, and once again, an OpenQuery action in the macro
will do this. Once you have this all working, you might want to put a
SetWarnigs/No action at the beginning of the macro to suppress the
action query confirmation prompts.

The way we have discussed this so far, using the unique index, if the
imported data already exists, the Append Query will simply not append,
end of story. If you want a message box to inform the user that this is
what has happened, you can put a MsgBox action in the macro prior to the
OpenQuery for the Append. You would put a Condition in here... if you
can't see the Condition column in the macro design window, select it
from the View menu. The Condition will look like this:
DCount("*","YourQuery")>0

What is YourQuery? You will make a query that includes the "temporary
table" and the main data table, joined on all 5 of the fields that
comprise the unique index. And add any field from the main table to the
query design grid. Get the idea? If there are any duplicates, this
query will show it, whereas if there are no duplicates, this query will
return no records. So yopu macro condition means the MsgBox will only
run if there are records returned by this query, i.e. if there is a
duplicate.
 
Thanks Steve,
that works just fine!

Steve Schapel said:
Graeme,

Regarding the temporary table idea, you can make a table in the frontend
database, with the structure you want for the imported data. Then you
can use a Delete Query (which can be run from within the macro using the
OpenQuery action), to clear any residual data from this table prior to
your import. I am not sure how you are importing the data, or where
it's coming from, I don't think you mentioned, but I assume you have
this part of it under control. So you just import to this now empty
table. Then, based on this table, make an Append Query to run the data
into your main table, and once again, an OpenQuery action in the macro
will do this. Once you have this all working, you might want to put a
SetWarnigs/No action at the beginning of the macro to suppress the
action query confirmation prompts.

The way we have discussed this so far, using the unique index, if the
imported data already exists, the Append Query will simply not append,
end of story. If you want a message box to inform the user that this is
what has happened, you can put a MsgBox action in the macro prior to the
OpenQuery for the Append. You would put a Condition in here... if you
can't see the Condition column in the macro design window, select it
from the View menu. The Condition will look like this:
DCount("*","YourQuery")>0

What is YourQuery? You will make a query that includes the "temporary
table" and the main data table, joined on all 5 of the fields that
comprise the unique index. And add any field from the main table to the
query design grid. Get the idea? If there are any duplicates, this
query will show it, whereas if there are no duplicates, this query will
return no records. So yopu macro condition means the MsgBox will only
run if there are records returned by this query, i.e. if there is a
duplicate.
Click to expand...
 
Back
Top