Creating a Msgbox

[How1]

Can someone provide me with code that will prompt the user with a msgbox
telling them that the entered project no. has already been used.

I assume that code would be entered into the on_enter property of the control.

I have already set-up a primary key which prevents duplicate entry. However
I would like to prompt the user.

Thanks in advance.

 

[Daniel]

If you setup your table properly an error message will be displayed when the
user enters a duplicate record. You could easily setup an error handler to
trap this error and then display your custom message rather than the default
message.

 

[fredg]

Can someone provide me with code that will prompt the user with a msgbox
telling them that the entered project no. has already been used.

I assume that code would be entered into the on_enter property of the control.

I have already set-up a primary key which prevents duplicate entry. However
I would like to prompt the user.

Thanks in advance.

If you have the [Project No] field (in your table) set to No
Duplicates, Access will tell you when you try to enter a duplicated
value.

Alternatively, code the [Project No] control's BeforeUpdate event (on
your form):

If DCount("*","TableName","[Project No] = " & Me![Project No]) > 0
Then
MsgBox "This number is already in use."
Cancel = True
End If

The above assumes [Project No] is a Number datatype.
However, if it is a Text datatype, then use:

If DCount("*","TableName","[Project No] = '" & Me![Project No] & "'")

 

[Klatuu]

fredg,

I would suggest you consider using DLookup rather than DCount for this. The
reason being that with a DCount, you will always have to search the entire
recordset, but with a DLookup, it will stop as soon as it finds the first
match. Just test for a non Null value:

If Not IsNull(DLookup("[ProjectNo]","TableName","[Project No] = " &
Me![Project No])) Then
--
Dave Hargis, Microsoft Access MVP

Can someone provide me with code that will prompt the user with a msgbox
telling them that the entered project no. has already been used.

I assume that code would be entered into the on_enter property of the control.

I have already set-up a primary key which prevents duplicate entry. However
I would like to prompt the user.

Thanks in advance.

If you have the [Project No] field (in your table) set to No
Duplicates, Access will tell you when you try to enter a duplicated
value.

Alternatively, code the [Project No] control's BeforeUpdate event (on
your form):

If DCount("*","TableName","[Project No] = " & Me![Project No]) > 0
Then
MsgBox "This number is already in use."
Cancel = True
End If

The above assumes [Project No] is a Number datatype.
However, if it is a Text datatype, then use:

If DCount("*","TableName","[Project No] = '" & Me![Project No] & "'")

0 Then