clear and delete

[cmichaud]

I have a form with primary key and first and last name. i want to stop
a user from entering the same first and last name. this is what i have
for vb code.

Private Sub FirstName_AfterUpdate()

If Not IsNull(DLookup("[LastName]", "tblPersonalInfo", "[LastName] = '"
& Me.[LastName] & "' And [FirstName] = '" & Me.[FirstName] & "'")) Then
MsgBox "The name you entered is already listed in the database. Use the
Find button to locate the record."

Me.LastName = Null
Me.FirstName = Null
Me.LastName.SetFocus
Me.Undo

End If
End Sub

This works fine in that it deletes the fields and the user can enter a
new name. However...it doesnt delete the primary key so the database
ends up have a record set with nothing in it. I want it to delete the
primary key to. So if i enter john smith (member id 47) and john smith
is in the database already....it will let me enter in sam smith....and
not bump the user id to 48.

Any ideas. Thanks

 

[Brian Wilson]

I have a form with primary key and first and last name. i want to stop
a user from entering the same first and last name. this is what i have
for vb code.

Private Sub FirstName_AfterUpdate()

If Not IsNull(DLookup("[LastName]", "tblPersonalInfo", "[LastName] = '"
& Me.[LastName] & "' And [FirstName] = '" & Me.[FirstName] & "'")) Then
MsgBox "The name you entered is already listed in the database. Use the
Find button to locate the record."

Me.LastName = Null
Me.FirstName = Null
Me.LastName.SetFocus
Me.Undo

End If
End Sub

This works fine in that it deletes the fields and the user can enter a
new name. However...it doesnt delete the primary key so the database
ends up have a record set with nothing in it. I want it to delete the
primary key to. So if i enter john smith (member id 47) and john smith
is in the database already....it will let me enter in sam smith....and
not bump the user id to 48.

Any ideas. Thanks

In a real-world database, it is not usual to prevent firstname/lastname
duplicates. As you might imagine, it's quite common for there to be two
distinct people both with the name 'John Smith'.
If you really did need to enforce this, then you should enforce it by adding
a unique index to the table made up of both of these fields. If you try to
enforce it by adding code to your form, then there is always the chance that
invalid data will end up in the database. Putting a unique index on the
table guarantees this cannot happen.

If you are worried about the id numbers being 'used up' then an autonumber
key is probably not what you want. In real-world databases, an autonumber
primary key is fine, but nobody ever cares about 'missing numbers'. A new
number is allocated as soon as you start to add a record and if you cancel,
or later delete the record then there will be gaps in your numbering system.

If you wanted to prevent a new record being started until you were sure that
there was not an existing match then you need to implement an 'Add New'
button which opens an unbound form, does the checking and if OK moves you on
to a bound form.

 

[Dirk Goldgar]

I have a form with primary key and first and last name. i want to
stop a user from entering the same first and last name. this is what
i have for vb code.

Private Sub FirstName_AfterUpdate()

If Not IsNull(DLookup("[LastName]", "tblPersonalInfo", "[LastName] =
'" & Me.[LastName] & "' And [FirstName] = '" & Me.[FirstName] & "'"))
Then MsgBox "The name you entered is already listed in the database.
Use the Find button to locate the record."

Me.LastName = Null
Me.FirstName = Null
Me.LastName.SetFocus
Me.Undo

End If
End Sub

This works fine in that it deletes the fields and the user can enter a
new name. However...it doesnt delete the primary key so the database
ends up have a record set with nothing in it. I want it to delete the
primary key to. So if i enter john smith (member id 47) and john
smith is in the database already....it will let me enter in sam
smith....and not bump the user id to 48.

Any ideas. Thanks

You should use the form's BeforeUpdate event for this, rather than the
AfterUdate event of either or both of the name fields. In the form's
BeforeUpdate event, you know that the user is attempting to save the
record, and that both fields have been filled out if they're going to
be. And in that event you can cancel the update by cancelling the
event. Your code might look like this:

'----- start of example code -----
Private Sub Form_BeforeUpdate(Cancel As Integer)

If Not IsNull( _
DLookup("LastName", "tblPersonalInfo", _
"LastName = " & Chr(34) & Me.LastName & Chr(34) & _
" And FirstName = " & Chr(34) & Me.FirstName & Chr(34)) _
) _
Then
Cancel = True ' abort update

MsgBox _
"The name you entered is already listed in the " & _
"database. Use the Find button to locate the record."

Me.Undo
Me.LastName.SetFocus

End If

End Sub
'----- end of example code -----

But this won't help you keep an unbroken sequence of user IDs. If the
UserID field is an autonumber, you can't keep a discarded entry from
incrementing it, so you will naturally develop gaps. That's the way
autonumbers work -- once you start to create a new record, the next
number is generated and even aborting that record won't recover it.
Autonumbers exist to provide a system-generated unique key, not to give
a smooth sequence of numbers. If that behavior is not acceptable, you
really shouldn't use an autonumber.

 

[cmichaud]

To me it doesnt matter if there is gaps in the numbering. I was under
the impression that these gaps were not good for the database. They
will in no way throw off my ability to count records? They dont make
the database slow? I am extemely new to this and just thought that
empty data was not good. Is this not so?
If i put it in the before update event of first name...will it allow me
to type the first name before running the check. If i create the
unique index wouldnt i still have to write the code to clear the
fields. Also...will the beforeupdate allow me to setfocus or to
undo???

Thanks for your help..

 

[Dirk Goldgar]

To me it doesnt matter if there is gaps in the numbering. I was under
the impression that these gaps were not good for the database.

No, gaps in the sequence of autonumbers are fine, and only to be
expected.

They will in no way throw off my ability to count records?

Only if you are mistakenly thinking of the autonumber field as a "record
number". It's just a key, nothing more. If you want to know how many
records there are in a table, you can:

(a) open a form and go to the last record, then look at the what the
form's navigation bar says

or

(b) use the DCount function to get the number; e.g., MsgBox
DCount("*", "MyTable")

or

(c) run a totals query with SQL like this: SELECT Count(*) FROM
MyTable;

or

(d) check the RecordCount property of the DAO TableDef object (local
tables only): MsgBox CurrentDb.TableDefs("MyTable").RecordCount

or

(e) open a DAO or ADODB recordset on the table, move to the end of
it, then check the recordset's RecordCount property.

See? There are lots of ways to count records.

They dont make the database slow? I am extemely new to this and just thought that
empty data was not good. Is this not so?

You're still thinking that there will be blank records in the database,
I think. That's a completely different thing from having gaps in the
autonumber sequence. You don't want to have blank records in your
database, because they occupy space to no purpose. You can prevent
their being saved by setting the Required property of one or more fields
(as appropriate) in the table's Design View.

But that has nothing to do with the gaps in the autonumber sequence.
That is, a sequence of autonumber keys like 1, 2, 3, 5, 6, 9 ... There
are no records with autonumbers 4, 7, and 8. That could be because
those records once existed but were deleted, or it could be because
someone started to create records which were assigned those autonumbers,
but then decided not to save them. Those particular numbers, once
assigned, won't be reused unless some special action is taken -- an
action that is, as a rule, unnecessary and undesirable.

If i put it in the before update event of first name...will it allow
me to type the first name before running the check. If i create the
unique index wouldnt i still have to write the code to clear the
fields. Also...will the beforeupdate allow me to setfocus or to
undo???

I said to use the BeforeUpdate event of the *form*, not of the FirstName
control. Consider: you have no control over the order in which the
user fills in the fields. They may fill in the first name, and then go
back to fill in the last name. They may misspell a name, and have to go
back to fix it. But you don't care *what* they do, until they try to
save the record. At that point, and not until then, you want to make
sure that this record is not a duplicate.

I agree, by the way, with Brian Wilson's comments, both on using a
unique index on to enforce your requirement for nonduplication, and on
the fact that duplicate first and last names are not at all uncommon in
the real world. If it's really important that no duplicate names be
stored in the database, then you ought to use the index to enforce that
at the database level. I would still use the form's BeforeUpdate event
to check for duplicates beforehand, though, so that I could display a
helpful message and maybe ask the user if they mean to enter a duplicate
or want to go to the existing record for that name.

 

[cmichaud]

Dick

Thanks for your time.

So the gaps i am referring to look like this when you pull up the
table.

ID Name
1 Joe
2 Dick
3
4 Jane

This is ok?

I understand brians comments. I guess there is a chance that two
people could have the same first and last names. Humm. This database
will probably store at max 600 people. I guess there still is that
chance. I just want to ensure that i am not getting multiple entries
of the same person cause when it comes time for my reporting it will
mess things up (i.e a report on club membership would not be
accurate...there would be one "extra" female thats not really there).
Also. I am collecting a lot of info on this one person. At least 8
corresponding tables. Do you still suggest putting the user through
entering all the info than telling them that this could all be a repeat
and useless. I was thinking last night. In the first name afterevent
could i run a vbyesno that tells them this is a repeat and that they
should search to make sure this user doesnt already exist. Hit
yes...and it will go back to the form so they can search. Hit no and
it will keep the repeat. Something like that. I remind you i am new.

Your time and help was greatly appreciated. Thanks!

Caleb

 

[Dirk Goldgar]

Dick

Thanks for your time.

So the gaps i am referring to look like this when you pull up the
table.

ID Name
1 Joe
2 Dick
3
4 Jane

This is ok?

No. That represents a blank record that has already been added, and you
don't want blank records. As I said before, there's a difference
between blank records and gaps in the sequence. All I'm saying is that
*this* is okay:

ID Name
--- --------
1 Joe
2 Dick
4 Jane

The fact that there's no record for ID=3 is *not* a problem or error.

I understand brians comments. I guess there is a chance that two
people could have the same first and last names. Humm. This
database will probably store at max 600 people. I guess there still
is that chance. I just want to ensure that i am not getting multiple
entries of the same person cause when it comes time for my reporting
it will mess things up (i.e a report on club membership would not be
accurate...there would be one "extra" female thats not really there).
Also. I am collecting a lot of info on this one person. At least 8
corresponding tables. Do you still suggest putting the user through
entering all the info than telling them that this could all be a
repeat and useless.

No, of course not. But if you will be storing information in related
tables, the relationship of those tables to the main "person" table
should be defined and enforced in the Relationships window. If you
enforce referential integrity, it won't be possible to create one of
those associated records unless the main or "parent" record has been
created and saved. So if you keep duplicate "person" records from being
saved, there won't be a problem with those other tables.

I was thinking last night. In the first name
afterevent could i run a vbyesno that tells them this is a repeat and
that they should search to make sure this user doesnt already exist.
Hit yes...and it will go back to the form so they can search. Hit no
and it will keep the repeat. Something like that. I remind you i am
new.

You probably want to call the same function from the AfterUpdate events
of both the LastName and the FirstName fields, in case the user modifies
them in a different order than you expect. In that function, you would
only want to check for a duplicate if both fields have been filled in.
So you might have this function defined in the General section of the
form's Module:

'----- start of (untested) code -----
Private Sub CheckForDuplicateName()

If IsNull(Me.FirstName) Or IsNull(Me.LastName) Then
' Don't bother if first and last names haven't been filled in.
Exit Sub
End If

If Not IsNull( _
DLookup("LastName", "tblPersonalInfo", _
"LastName = " & Chr(34) & Me.LastName & Chr(34) & _
" And FirstName = " & Chr(34) & Me.FirstName & Chr(34)) _
) _
Then

If MsgBox( _
"The name you entered already exists in the " & _
"database. To search for duplicates, use the " & _
"Find button. Do you want to search for " & _
"duplicates now?", _
vbYesNo+vbQuestion, _
"Duplicate Name?") _
= vbYes _
Then
Me.Undo
Me.LastName.SetFocus
End If

End If

End Sub
'----- end of code -----

With that function defined, you could call it from each of the controls'
AfterUpdate events:

'----- start of event code -----
Private Sub FirstName_AfterUpdate()

CheckForDuplicateName

End Sub

Private Sub LastName_AfterUpdate()

CheckForDuplicateName

End Sub
'----- end of event code -----

 

[cmichaud]

Dirk

How do i stop the blank record?? Do i make a unique index with Member
ID (pk), First Name, and Last name??


Thanks!

 

[Dirk Goldgar]

How do i stop the blank record?? Do i make a unique index with Member
ID (pk), First Name, and Last name??

No, that won't stop a blank record. Making a unique index is about
preventing duplicates, not about preventing blank records. For example,
a unique index on FirstName and LastName would keep a record from being
stored with the same FirstName and LastName as another record. But
discussions in our other thread suggest that you have decided not to do
that.

To keep a record from being saved with a blank LastName, set that
field's Required property to Yes, in the table's design view. If you
want to require that a first name be entered, do the same with the
FirstName field.

If you don't want to require both of these fields, but would accept a
record that has either a LastName or a FirstName, it's a bit more
complicated -- you'd have to use a Table Validation Rule. I won't
describe how to do that now, because I suspect that's not what you want.

 

[cmichaud]

Dirk

I have this in the beforeupdate event of the *form*


Private Sub Form_BeforeUpdate(Cancel As Integer)


If Not IsNull( _
DLookup("LastName", "tblPersonalInfo", _
"LastName = " & Chr(34) & Me.LastName & Chr(34) & _
" And FirstName = " & Chr(34) & Me.FirstName & Chr(34)) _
) _
Then
Cancel = True ' abort update


MsgBox _
"The name you entered is already listed in the " & _
"database. Use the Find button to locate the record."


Me.Undo
Me.LastName.SetFocus
End If
End Sub

It works fine checking after i leave the mainform and go to a subform.
But when i go back to the main form and then to another subform it
tells me the name is already in the database.

any ideas?

 

[Dirk Goldgar]

Dirk

I have this in the beforeupdate event of the *form*


Private Sub Form_BeforeUpdate(Cancel As Integer)


If Not IsNull( _
DLookup("LastName", "tblPersonalInfo", _
"LastName = " & Chr(34) & Me.LastName & Chr(34) & _
" And FirstName = " & Chr(34) & Me.FirstName & Chr(34)) _
) _
Then
Cancel = True ' abort update


MsgBox _
"The name you entered is already listed in the " & _
"database. Use the Find button to locate the record."


Me.Undo
Me.LastName.SetFocus
End If
End Sub

It works fine checking after i leave the mainform and go to a subform.
But when i go back to the main form and then to another subform it
tells me the name is already in the database.

Are you changing the data on the main form in between, so that it wants
to save it again? That would cause that to happen. Now that I think of
it, you'll only want to perform that check for duplicates when you're
adding a new record. Try this small modification:

'----- start of code -----
Private Sub Form_BeforeUpdate(Cancel As Integer)

If Me.NewRecord Then

' If we're adding a new record, check for duplicates.

If Not IsNull( _
DLookup("LastName", "tblPersonalInfo", _
"LastName = " & _
Chr(34) & Me.LastName & Chr(34) & _
" And FirstName = " & _
Chr(34) & Me.FirstName & Chr(34)) _
) _
Then
Cancel = True ' abort update

MsgBox _
"The name you entered is already listed in the " & _
"database. Use the Find button to locate the record."

Me.Undo
Me.LastName.SetFocus
End If

End If

End Sub
'----- end of code -----

 

[cmichaud]

Perfect!

 

[cmichaud]

I wonder if there is in addition that could be made.

Lets say there is 2 john smiths. Lets say the user runs the find and
discovers the john smiths are different. Can i have a cancel button to
allow the double entry????

caleb

 

[Dirk Goldgar]

I wonder if there is in addition that could be made.

Lets say there is 2 john smiths. Lets say the user runs the find and
discovers the john smiths are different. Can i have a cancel button
to allow the double entry????

Read my other messages in this thread.

 

[cmichaud]

P-e-r-fect! Thats awesome. Thanks