Error Check Help?

[newone]

I need to perform some sort of error check to test whether data that I am importing into a table has already been imported. I know very little VB and need help if someone is willing to point me in the right direction.

For simplicity sake, I will only refer to the three tables in my .mdb that will be affected. I have "tblInvoice," "tblProductDetails," & "Temp" tables. My process imports a .CSV file into the "Temp" table and from there cleans the data and then moves it via queries to the "tblInvoice" and "tblProductDetails" tables. The Invoice and ProductDetails tables have a primary key "InvoiceNum" with a one-to-many relationship. I want to be able to check and determine if a particular InvoiceNum has already been imported and if so, stop the entire process and alert the user without continuing the import process. I currently use MACROS to import each Invoice, "clean" the data and perform calculations, and then move the data to the appropriate tables.

I know that if I have warnings turned ON that the user will be alerted that there is a duplicate key but I would rather keep the warnings OFF and alert the user some other way.

I have converted the macro to code if that will help anyone who is willing to help me. The code is:

'------------------------------------------------------------
' mcrImportProcess
'
'------------------------------------------------------------
Function mcrImportProcess()
On Error GoTo mcrImportProcess_Err

DoCmd.SetWarnings False
' Imports Invoice Text File in tblTemp
DoCmd.RunMacro "mcrImportInvoice", , ""
' Runs query to populate the tblInvoice table
DoCmd.OpenQuery "qryInvoice,Date,& CustomerID", acNormal, acEdit
' Runs query to populate tblProduct Details table
DoCmd.OpenQuery "qryProductDetails", acNormal, acEdit

'There are a series of other queries that run here but I did not include them in this example.
'I want the code to stop the processing without populating any tables, via queries, once it has found
' the InvoiceNum already entered in the "tblInvoice" table.

DoCmd.SetWarnings True
Exit Function


mcrImportProcess_Exit:
Exit Function

mcrImportProcess_Err:
MsgBox Error$
Resume mcrImportProcess_Exit

End Function

 

[David Fletcher]

Why do you want the process to stop if the user double-submits the same invoice? If your table structure in your database is set up so that only the one copy of an invoice can exist, you can just bypass the second copy can't you? Unless of course the use is double-submitting a whole file - even then, as long as the data gets into the system correctly ......

To write such verification routines in any programming language can imvolve a fair amount of time and detailed work. IF you are green to VBA and programming the import routine you have using macros will work, in that it will get the data in according to the rules and structure of relational and normalised data - given of course that your DB design is sound.

If you were to program it, I would write code to either:
1) vet the work table against the database, return a list of problems to the user, and clear the the work table. Only update to the database when the CSV file is clean. the user will mutter things under his breath about you however!

or:
2) vet the work table against the database, update records that have no errors or are the first copy of records you find, and return a list of errors (duplicates rejected)to the user so that he can see what has been sucessful and how problems have been handled. he will still mutter hings under his breath about you but not so ominously!

There is a lot of work in coding his. And you need to produce a report, probably from another temp table designed for the purpose.

Because of the detail involved it is a bit of a task to suggest the code. However, before you do get into that you need to consider your design and approach, possibly in consultation with your user. then he will mumble under his breath even less!!

I hope you find this helpful to some extent.

Cheers.
DavidF