trying to warn data entry user of a particularly odd error

[joe789]

Hey Folks,

I noticed a particularly odd error with a database that I have been given the chance to update with a new version. I have pretty much updated the new version with everything I can think of except this one:

Sometimes, for whatever reason, a client will be admitted prior to the previous discharge for that same client - and that's impossible! For instance, John Q. Citizen is admitted on 1/5/2011 and discharged on 1/15/2011 yet he may have another admission record of 1/10/2011, which obviously cannot be possible as he was never discharged from the previous admission! Another odd/special error would be a new admission prior to the discharge of a previous admission, an example would be John Q. Citizen being admitting on 2/17/2011 with that record having no discharge date, yet another admission for the same client on say 3/18/2011, again impossible as this client has yet to be discharged from a previous admission to have another one!

I am lost as to what I can do in real-time/on-the-fly with this front-end database where if a condition such as the two described occur ... any ideas or suggestions? I know how to create reports that would show and indicate the two odd errors (I wish there was a more automated way to do it thou), but as to develop perhaps a message box that alerts the person entering the record of that special error and/or a way to force the user to correct the error before moving forward, I just have no idea where to start with that or if it isn't possible due to the processing and lag that would occur on a before update event module? Just sorting some of this is a mess because although most clients may have only one admission some have dozens and they must all be subjected to the above error catching technique for this error trap to be successful.


Thank you for any help and suggestions!

Joe

 

[vbaInet]

You will check two things in the following order:

1. The last entry for that client to see whether a discharge date was entered. Use DCount(). If this condition is satisfied, then:
2. Is the admission date >= discharge date of last entry. Use DateDiff(). If it meets this condition then it's ok to proceed.

Logic:

If [COLOR=Blue][B]1.[/B][/COLOR] = True Then
    If [COLOR=Blue][B]2.[/B][/COLOR] = False Then
        Msgbox "Fix"
    End If
Else
    Msgbox "Fix"
End If