Import spreadsheet with pop up

[Graeme at Raptup]

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

 

[Steve Schapel]

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?

 

[Graeme at Raptup]

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

 

[Steve Schapel]

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

 

[Graeme at Raptup]

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.

 

[Steve Schapel]

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.

 

[Graeme at Raptup]

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

 

[Steve Schapel]

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.

 

[Graeme at Raptup]

Thanks Steve,
that works just fine!

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.

 

[Graeme at Raptup]

Hi Steve,
another query on this subject.
It was working fine - but I wanted to see if I could have three appends on
the same spreadsheet import.
The reason for this is that there is a requirement to allow for 3 fields
that are similar. In the original I had a field called Retailer. What we want
now is Retailer1, Retailer2 and Retailer3 on the spreadsheet that then gets
brought into the database as 3 different records (with the other fields all
being the same but for Units 1,2&3 and Price 1,2&3)).
I find I am able to do this running three different append queries, but only
if I run the queries manually.
As soon as I run them through the macro it does not seem to work.

My macro looks like this:
Set Warnings
Open Query: Delete Temp (Deletes record in 'Temp Approval')
Transfer Spreadsheet: Import ('Report' from a specific folder)
Msg Box: DCount ("*","Importduplicate")>0
Open Query: Append Approval
Open Query: Append Approval2
Open Query: Append Approval3
Close

The Msg Box part seems to be where it stops working. I have joined fields
from two tables ('Artwork Approval' and 'Temp Approval'). One of the joins is
for the field 'Units'. In 'Artwork Approval' the field is 'Units' but I have
joined it to three fields in 'Temp Approval' ('Units1', 'Units2' and
'Units3').

The macro then seems to stop there as it does not want to run the appends. I
tried deleting the second two append queries but still no joy. (They do
however work when I run them manually)

As always, any suggestions much appreciated.