Check to see if value previousl entered.

[Cereldine]

I would like to check to see if a previous date value has been entered for a specific record and display a message if it has. I have spent some time researching vlookup and vcount but im not quite there in what i want to achieve.

I have a data entry subform, linked to my main form through StairID. The subform is called tblEstateInspection and has a PK called estateinspectionID, stairID is a FK and links it main table.

Like i say i have been investigating what other people have done using dlookup / dcount, what i want to do is check that a specific date has not been entered for another inspection for the same stairID, i guess what i have got below at the moment just looksUp the value and because it is never null the messagebox always shows? How can i ammend it so that if it finds records that have same stairid, checks Inspectiondate field and only shows message if the new date matches old date

Private Sub InspectionDate_AfterUpdate()
Dim strFilter As String
Dim check As Variant
strFilter = Me!StairID

check = DLookup("[inspectiondate]", "tblEstateInspection", "[stairID] =" & strFilter)

If Not IsNull(check) Then
MsgBox "You have entered this exact date for this staircase before, be careful!", vbExclamation
End If
End Sub

Thanks

 

[neileg]

You could create a two field index on stairID and inspectiondate and set it to No Duplicates. This would prevent the combination of the two fields being used again.

 

[Cereldine]

would you do this in the table design by setting the default value to something like

[stairID] & [inspectiondate]

?thanks

 

[neileg]

Open your table in design view. Click the Indexes button on the tool bar. Your primary key should be there already. In the first blank line below the PK, type in an index name (anything will do), in the field colum select stairID. In the next line leave the index name blank and in the field column select inspectiondate. Now click on the name you've just chosen for your index and set the Unique property to Yes.

 

[Cereldine]

Ok that would be something im looking for, it would be a better user interface if i could then suppress the built in message and give them a message of my own.

ANy ideas how to do this?

 

[neileg]

Yes you can trap the error message and provide your own. Do a search for error trapping and handling.