Using Acsess as a Dual Key Checker

[m40wen]

Hi Guys

Just like to say that I am almost ashamed at how little I know about access, so I guess over the months I will be taking out alot more than I will be putting in! Anyway, I have the following project to do.

Basically it will be necessary to download from an ODBC database on a daily basis, and there will be around 300 products. Each product will be made up of 12 individual fields. What I would like to do is manually key in against these fields from a paper reciept and it will be only shown if the manual input matches what has been downloaded ie Say one of the fields is "Colour", and from the download it says 'Red', therefore if I manually type in any other colour an error message will come up. The basic reasoning behind this is I want access to act as a kind of manual checker from our downloads, with an audit trail of what is being checked. Any typos would be negated as the field would be highlighted, and therefore the inputter can double check any incorrect fields. The current process is done by sight and obviously this means that errors get made as well as no audit on what has been checked.

Anyways thats the theory behind it, can anyone offer me any tips to help me off as I have no idea where to begin! Indeed, is Access suitable for the job? I am presuming that every field will have to have a Vlookup when text is manually keyed in against the download?

Any help is appreciated. Literally any as I am in way over my head!

Regards

Mike

Sorry for the typo in the heading!

 

[m40wen]

Morning all

Ok, I am going to try and break this into pieces. How do I check the data of one field (lets call it Download Buy/Sell) matches what I type in (say Manual Buy/Sell)? For example a fax order comes in, I look up the reference and start typing the details in my form against what I have already downloaded. Do I have to use some kind of validation rule? In layman terms, I want to type in "BUY" in the manual field, and this will be checked against the Download field to see if this matches as "BUY", but I havent a clue what or how to do this.

Also is it possible for my downloaded fields to be hidden unless they match ie pass the vaildation rule? I dont want any clerks just typing what they see on screen to 'get through the work.' They should be typing in from the fax and Access should validate the data with what was downloaded from the system.

A few pointers would be greatly appreciated. I know I am trying to run before I can jump, but all my advanced office knowledge has been learned this way!I hope it makes sense what I am trying to achieve.

Cheers for taking the time

Mike

 

[Pat Hartman]

In order to validate the download, the download will need a unique identifier for each record.

1. the form will be bound to the shadow table in access.
2. the first field on the form will be the unique id.
3. In the AfterUpdate event of the unique id, you need to use DAO or ADO to read the associated record in the downloaded table.
4. In the BeforeUpdate event of the form, compare each form field with each recordset field. If there are errors, cancel the update, highlight the error, and display a message. The code will look something like:

If Me.fld1 = rs!fld1 Then
Else
    MsgBox "Error in fld1", vbOKOnly
    Cancel = True
    Me.fld1.SetFocus
End If
If Me.fld2 = rs!fld2 Then
Else
    MsgBox "Error in fld2", vbOKOnly
    Cancel = True
    Me.fld2.SetFocus
End If

 

[m40wen]

Thanks Pat.

Just like to say this site is awesome! Many thanks!