Creating a Msgbox

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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 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.
 
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.
Click to expand...

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] & "'")
 
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


fredg said:
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.
Click to expand...

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
Click to expand...
Click to expand...
 
Back
Top