Sorry...yet another question about duplicate entries

I'm sorry. I meant the custom message and not an error message.
--
Aria W.


"Aria" wrote:

Thank you *so* much to everyone for helping me with this. I received error
#3078 but I was able to correct that by using my field and table names. After
I made the changes, the error message appeared along with the "yes" or "no"
choice to add?cancel the new record. Thank you once again, everyone!
--
Aria W.


" wrote:

Aria:

You seem to be missing the underscore continuation character at the
end of the first line and a space before the first ampersand in the
second line of:

strMessage = "Employee" & Me.FirstName & " " & _
Me.LastName & " already in database. Do you want to add
another" & _
" employee with the same name?"

Just so you are sure, this is one line of code, but its entered as
three separate lines, the first two ending with the underscore
continuation character. This is to make the code more readable than
if it tailed off into the distance as a single line in the VBA window.

Ken Sheridan
Stafford, England

On May 4, 6:06 pm, "Aria via AccessMonster.com" u44643@uwe wrote:
Ken,
What an absolutely wonderful reply! Very thorough. Thank you for going
through all of that. I would *not* have been able to do that on my own.
Thanks also for explaining when to use the BeforeInsert and the BeforeUpdate
events. You taught me something new there too.

I am having a bit of a problem though.

I received a syntax error with:
== strMessage = "Employee " & Me.FirstName & " " & _
Me.LastName& " already in database. Do you want to add another"& _
" employee with the same name?"

I made some changes which may be wrong since I haven't been able to carry it
through to the end. I thought maybe there was an extra space after "Employee
" so I changed it to "Employee" but it still states that there is a syntax
error. I then added the _ to continue to the next line. This brings up other
error messages such as expected end of statement. Right now, I'm just
guessing. I decided to stop guessing and get some help.

This is what I have in the forms BeforeUpdate event:

Dim strMessage As String
Dim strCriteria As String
Dim strFirstName As String
Dim strLastName As String

strCriteria = "FirstName = """ & Me.FirstName & _
""" And Lastname = """ & Me.LastName & """"

' has first or last name been changed or new record being added
If Me.FirstName strFirstName Or Me.LastName strLastName Then
' if so does row already exist with the new names
If Not IsNull(DLookup("EmployeeID", "Employees", strCriteria)) Then
' if so get user confirmation whether to add new employee
' of same name. If user answers 'no' cancel update
' and undo changes
strMessage = "Employee" & Me.FirstName & " " &
Me.LastName& " already in database. Do you want to add another" & _
" employee with the same name?"

If MsgBox(strMessage, vbQuestion + vbYesNo, "Warning") = vbNo Then
Cancel = True
Me.Undo
End If
End If
End If

Perhaps you or someone else can direct me further. Thank you.



wrote:
You are quite right, it would need to be the form's BeforeUpdate event
procedure as the BeforeInsert event executes as soon as the user
begins to enter data in a new record, so is too early in the sequence
pf events, whereas the BeforeUpdate event executes when the user
attempts to save the record. To check whether the names entered
already exist in a row in the table when entering a new employee you
can examine the form's NewRecord property and call the DLookup
function if this is True. If the DLookup function returns Null then
the names don't exist, but otherwise they do so you can then seek the
user's confirmation as to whether to proceed, so the code would be:

Dim strMessage As String
Dim strCriteria As String

strCriteria = "FirstName = """ & Me.FirstName & _
""" And Lastname = """ & Me.LastName & """"

' is new record being inserted
If Me.NewRecord Then
' if so does row already exist with the new names
If Not IsNull(DLookup("EmployeeID", "Employees", strCriteria))
Then
' if so get user confirmation whether to add new employee
' of same name. If user answers 'no' cancel update
' and undo changes
strMessage = "Employee " & Me.FirstName & " " &
Me.LastName & _
" already in database. Do you want to add another" &
_
" employee of the same name?"

If MsgBox(strMessage, vbQuestion + vbYesNo, "Warning") =
vbNo Then
Cancel = True
Me.Undo
End If
End If
End If

where the table name is Employees, its primary key column is
EmployeeID and it contains columns FirstName and LastName, both
'required'.

If you want to check for an existing employee of the same name if a
user edits an existing record, i.e. if they change an existing name to
one which already exists in the database, as well as when they insert
a new record, then it’s a little more complex. Firstly declare two
variables in the Declarations area of the form's module to store the
current first and last names when the user navigates to a record,
using the Nz function to return zero- length strings when at a new
record:

' declare variables to store names
Dim strFirstName As String
Dim strLastname As String

Then in the form's Current event procedure assign the current names,
or zero-length strings if at a new record, to the variables:

' store names in variables, assigning
' zero-length strings if new record
strFirstName = Nz(Me.FirstName, "")
strLastname = Nz(Me.LastName, "")

The code for the form's BeforeUpdate event procedure now omits the
examination of the NewRecord property, so is:

Dim strMessage As String
Dim strCriteria As String

strCriteria = "FirstName = """ & Me.FirstName & _
""" And Lastname = """ & Me.LastName & """"

' has first or last name been changed or new record being added
If Me.FirstName strFirstName Or Me.LastName strLastname Then
' if so does row already exist with the new names
If Not IsNull(DLookup("EmployeeID", "Employees", strCriteria))
Then
' if so get user confirmation whether to add new employee
' of same name. If user answers 'no' cancel update
' and undo changes
strMessage = "Employee " & Me.FirstName & " " &
Me.LastName & _
" already in database. Do you want to add another" &
_
" employee of the same name?"

If MsgBox(strMessage, vbQuestion + vbYesNo, "Warning") =
vbNo Then
Cancel = True
Me.Undo
End If
End If
End If

Ken Sheridan
Stafford, England

Thank you both for your suggestions. I won't be able to use Dept. because
that only applies to site employees. I will try to find something additional
[quoted text clipped - 92 lines]
--
Aria W.

--
Message posted via AccessMonster.comhttp://www.accessmonster.com/Uwe/Forums.aspx/access-gettingstarted/20...