Simple Method of Checking if a Record Exists

[robboll]

In MS Access 2000 I am trying to include a method that checks to see if
the Fiscal Year (FY) and Fiscal Month (FM) already exist in Table1
before it appends new data. I was thinking that it should give the
user the option to abort the action.

Table: Table1
Fields: FY, FM

So the user selects FY: 2006 and FM: 6 from a combobox on a form
and clicks a command button to execute the action. What is the
simplest way to either process the action without doing anything (i.e.,
if FY 2006 and FM 6 hasn't been entered) -- OR -- if 2006 and 6 already
exist in the was previously entered, to present a message box letting
the user know with options such as:

This period is already available. Do you want to proceed anyway? With
'Yes' or 'No' buttons.

Thanks for any suggestions,

RBollinger

 

[strive4peace]

if you want duplicates to not be allowed, make a unique
index on the combination of FY and FM

From the table design, Turn on the Indexes window (from the
menu: View, Indexes)

click on the first field in the table design (FY) and set
the Index property to
Yes (duplicates ok)

that will add a line to the Indexes window

In the row just below the index you just made, in the 2nd
column of the indexes window, click in the fieldname column
and choose the 2nd fieldname (FM)
In the row below

Now, Click on top row of that Index, the first fieldname
(row) -- set
Unique --> Yes

if you use an APPEND query to add data, duplicates won't go in

'~~~~~~~~~~~~~~~~

If you are on a form and want to know if that combination is
ok, you can do this on the BeforeUpdate event

'~~~~~~~~~~~~~~~~~~~~~
dim mRecordID as long
mRecordID = nz(dLookup(
"IDfieldname",
"Tablename",
"FY=" & me.FY_controlname _
& " AND FM=" & FM_controlname))

if mRecordID > 0 then
if msgbox("record already exists", _
vbyesno, _
"Do you want to proceed anyway?") _
=vbno then
CANCEL = true
exit sub
end if
end if


'~~~~~~~~~~~~~~~~~~~~~


Warm Regards,
Crystal
Microsoft Access MVP 2006

*
Have an awesome day

remote programming and training
strive4peace2006 at yahoo.com

*