Access 2003Validation in Tables

[bluenose83]

Afternoon everyone. Struggling with a bit of validation so ill try to explain my situation. Also apologies, I am still trying to understand Access.

I have built a simple database which has a Input screen. The user keys on a form into a load of fields the information and hits submit. This then writes to the table in the back ground. This form has validation in each of its boxes. So for example, there is a drop down which will only accept a Y or N. Another will only accept a bank account which is 8 digits long, anything more or less an error message pops up. This is ok for the user when they have a couple to input at a time. Sometimes we get requests to key say 100 into the database so I have been coming up with a upload query that will write to the table.

So the user provides a excel spreadsheet with all the information on. This is then imported into access and into another table I have called tbl_import. They click a button and it copies whats in this table to the main table.

As it stands, It will copy anything to the main table (even incorrect data) so I have been attempting to add to my query some validation that will stop the query before it copies across. My first attempts have just copied anyway and nothing has been flagged.

I have tried adding to the criteria, <>"Y" OR <>"N". Also tried just listing them going down.

The validation for bank account on my form is Len([Bank_Account])=8 but I have tried adding this to the query but again doesn't work.

First of all is this possible to do? will the query look at all the items in the columns? Or am I going to have to add code (something I have no idea about really).

 

[bluenose83]

I have also tried putting validation into the design of tbl_import which kind of works. It just removes the lines that are incorrect from the import file. Ideally, id like it to reject the whole thing with an error message

 

[stumac]

There is a lot of different ways of handling this or parts of it. In the past when adding a batch of records what I would do is add it to the import table, validate it using either one or multiple queries to check that no errors were found. If there was errors then adding them to a separate error table which fed a report and exiting the process. If there was no errors then I would append the records to the final table. I used code for this and it was fairly straight forward to your scenario. (It was adding monthly direct debits, so had to validate sort codes and accounts).

In terms of checking the length, something like below:

or SQL:

INSERT INTO TblCustomer ( CustomerNo, CustName, SortCode, AccountNo, ClaimGA )
SELECT CustomerNo, CustName, SortCode, AccountNo,ClaimGA
FROM TblImport
WHERE Len([AccountNo])=8

Also - Nice username!!

 

[welshgasman]

If I had to do that, I'd probably do this.

Import excel workbook into temp table.
Process temp table with validation. Any errors mark as Invalid. Depending on the number of fields, you could use a unique bit value to identify all the errors, or perhaps write them to another table linked by autonumber ID?
Supply a form for all records marked as invalid. Mark as Valid/Remove invalid, as errors are corrected.
Then when all corrected, add to real table.?

 

[bluenose83]

Thanks for your replies. I will make an attempt at this over the next few days. @welshgasman How would you do the validation to show errors?

 

[welshgasman]

I would read the temp table as a recordset.
Examine the fileds, and perhaps add comments into a memo fileld in the temp table, so the errors can be identified and corrected.
Have a field for flagging up as bad/good, then just show the ones marked bad.
Have them corrected by a form, then run it through your validation again.

As you will need to validate both in form and validation sub, perhaps create suitable function and call from each.?

For everything marked good (or not bad) transfer to your real table and delete from temp table, or check it does not alread exist if you do not delete.?

Break it down into small tasks.

 

[bluenose83]

I would read the temp table as a recordset.
Examine the fileds, and perhaps add comments into a memo fileld in the temp table, so the errors can be identified and corrected.
Have a field for flagging up as bad/good, then just show the ones marked bad.
Have them corrected by a form, then run it through your validation again.

As you will need to validate both in form and validation sub, perhaps create suitable function and call from each.?

For everything marked good (or not bad) transfer to your real table and delete from temp table, or check it does not alread exist if you do not delete.?

Break it down into small tasks.

Cheers Bud !

 

[welshgasman]

You might even just put the bad ones into the errors table and upload the good ones immediately.?