By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
455,491 Members | 1,868 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 455,491 IT Pros & Developers. It's quick & easy.

Checking before adding a new record

P: n/a
Hi Everyone,

Does anyone know a way to "check" if a record number is in existence
when someone is adding a new record? Currently working on an awfully
designed Access 97 database (that I'm not permitted to improve the
design of, and not from lack of trying) that records projects using an
Expenditure Authorization number (made up by the powers that be). The
Expenditure Authorization (EA) number is the primary key of the main
data table and is a 6 digit number that (obviously) should be unique
but as is always the case in Local Government the left hand doesn't
know what the right hand is doing. The main data entry form has well
over a hundred fields for data entry (don't ask) and the managers here
want some kind of small modal popup form to appear when the Add button
is pressed so that when they enter a new EA number then the DB should
check first that this new EA isn't already in existence and notify the
user accordingly. If the EA is not taken then they want the popup form
to close and the new EA number to be inserted into the relevant field
on the main form.

I can't use an autonumber for the EA as it doesn't follow any specific
pattern and the EA numbers seem to be made up on the spot (really
efficient way of working, I know.)

Any help would be appreciated, many thanks.

D...

Mar 6 '06 #1
Share this Question
Share on Google+
2 Replies


P: n/a
Try a DCount in code. You should be able to lookup the value in the
table before the rest of the code executes.

E.G.
If (DCount("*","Table","Field'" & NewValue & "'") > 0) Then
'Throw out a message
Else
'Ok to go
End If

Mar 6 '06 #2

P: n/a
Excellent,

Many thanks Russell.

All the best, D...

Mar 6 '06 #3

This discussion thread is closed

Replies have been disabled for this discussion.