Form design question

[Antonio]

I have a form that is used for data entry only. It is part of a database used
by several employees in the office and functions as a requisition tool where
each record entered represents a request for a document to be scanned into a
public folder. The user then opens the document after the copying official
updates the record as "completed".

There is a possibility that a user(s) might request the same document more
than once. This is not a mistake, though the document being requested might
have already been scanned.

So...I would like to alter my form so that, when a unique account number is
initially entered into a text box, a search is done for any previous requests
(records) that have the same account number. This would prevent the user from
entering a duplicate request and then allow them to select the previous
request to gain access to the needed document.

Example:

User enters account number into text box.
After update, a search is done for other records in "tblRequests" with the
same Account Number.
A. If a record with the same account number is not found, the data entry
proceeds. (5 fields per record indicating who, what, when is needed)
B. If the account number is found, a seperate form opens displaying the
record(s) with that account number.
The user can access those records (double clicking the account number will
open a linked PDF file containing the scanned documents) and be done.
C. If the user decides previous requests do not contain needed scanned
documents, they press a "skip" button that takes them back to step A and they
continue with the data entry.

Can anyone give me an idea of how to set this up and the coding involved?

TIA

Antonio

 

[scubadiver]

Something like the following should work in the "before update" event of the
field (I found it in another post)

If IsNull(DLookUp("Employees_ID","Employees","Employees_ID = " &
cboEmp_Name)) then
Msgbox "This Account number already exists", vbinformation
docmd.openform "YOUR FORM NAME"
else
Msgbox "This account number does not exist", vbinformation
end if