accurate error message

[Guest]

dear friend .
i have an table and form for the below
TABLE [employee membership]
field employee name
" membershipslipnumber(pk)
" date
on the form beforeup date event or on error event should tell to the
database user on violation of the primary key value that the "
membershipslipnumber[12345] was alloted to mr/ms [xyz] on date [1/1/01] " how
to do this programatically please .

 

[Guest]

In the form's BeforeUpdate event procedure put:

Dim strName As String
Dim strCriteria As String
Dim strMessage As String
Dim varDate As Variant

strCriteria = "[membershipslipnumber] = " & Me.[membershipslipnumber]

varDate = DLookup("[date]","[employee membership]",strCriteria)

If Not IsNull(varDate) Then
strName = DLookup("[employee name]","[employee membership]",strCriteria)
strMessage = "membership slip number " & Me.[membershipslipnumber] & _
" was allotted to " & strName & " on " & varDate & "."
MsgBox strMessage, vbExclamation, "Invalid Operation"
Cancel = True
End if

The above assumes that membershipslipnumber is a number data type. If its
text then wrap the value in quotes characters like so:

strCriteria = "[membershipslipnumber] = """ & Me.[membershipslipnumber] & """"

I would recommend, however, that you call the [date] field something more
explicit such [membershipslipdate] to avoid confusion with the built in Date
function. If you do use it be sure to wrap it in brackets when referring to
it: [date].

Ken Sheridan
Stafford, England