Testing for a record already in existence 
Author Message
 Testing for a record already in existence

Anyone able to point me in the right direction?

I have a fairly simple relational staff database set up which handles First
Name, Last Name, Telephone, Dept, Title, Photo etc etc.

As 3 people have access to the database for inputting new users, what I'd
like to do is to have an Event that OnUpdate for the last name field checks
to see if there's already someone called that name. At the moment we're
getting a few duplicate entries and it would help if it would pop up a
message saying "A person/People of this name already exist(s) - would you
like to cancel this new record?" or something similar.

What will I need to do to create a Boolean function which returns True if
the person exists and False if not. Sorry if this sound Basic I'm just not
that familiar with recordsets etc.

I know it's pretty much

Function StaffExists(First, Last As String) As Boolean

found = false
do loop until found=true or eof
    goto next record and compare the first and last names with the
parameters
loop

StaffExists = found

End Function

But what do i need to do in between the Function and End Function to build a
recordset!

Sorry for my utter naivety.

Nick.



Fri, 01 Nov 2002 03:00:00 GMT  
 Testing for a record already in existence
Forget looping code - that is going to be really slow, especially as the
database grows as you add records.  What you should do instead is index the
Last Name field in the table.  Then in your code run a query that gets a
Recordset of the records with that last name (using the WHERE clause) - if
the Recordset gets no records then there were no duplicates, but if it
returns some, then you can decide what to do with them.

Using this method you can also search for similar last names (using the LIKE
operator), combinations of first and last name, etc.  Take advantage of the
database engine as much as you can, that's what it is there for. ;-)

Alden


Quote:
> Anyone able to point me in the right direction?

> I have a fairly simple relational staff database set up which handles
First
> Name, Last Name, Telephone, Dept, Title, Photo etc etc.

> As 3 people have access to the database for inputting new users, what I'd
> like to do is to have an Event that OnUpdate for the last name field
checks
> to see if there's already someone called that name. At the moment we're
> getting a few duplicate entries and it would help if it would pop up a
> message saying "A person/People of this name already exist(s) - would you
> like to cancel this new record?" or something similar.

> What will I need to do to create a Boolean function which returns True if
> the person exists and False if not. Sorry if this sound Basic I'm just not
> that familiar with recordsets etc.

> I know it's pretty much

> Function StaffExists(First, Last As String) As Boolean

> found = false
> do loop until found=true or eof
>     goto next record and compare the first and last names with the
> parameters
> loop

> StaffExists = found

> End Function

> But what do i need to do in between the Function and End Function to build
a
> recordset!

> Sorry for my utter naivety.

> Nick.



Fri, 01 Nov 2002 03:00:00 GMT  
 Testing for a record already in existence
Nick,
You could set a multiple field index on the table, and set it to be unique.
The problem with this is that it IS possible that you get 2 Nick Allens
working for you, and the db would not allow this unless you included another
field to differentiate them.

You could use the persons phone number as a unique index, but again, if you
get hubby and wife with the same number, you run into trouble.

You could also use a Domain function  such as DCount to check for the
existence of a similar name. You can then allow duplicates, but flag the
user upon entry of a duplicate. Maybe bring up a modal form with a 'like'
criteria and let them select or add.

I have a similar situation where the user types into, or selects, the name
from a combo box, and I use the Not in List event to open another form to
either select an existing user or add a new one.

Another possibility is to have all the staff in a subform, with a text box
on the main form to type what you are looking for.
The On Change event of the text box has code to find a close record in the
subform. eg;
Private Sub txtFindWhat_Change()
    If Me.txtFindWhat.Text = "" Then Exit Sub
    Application.Echo False
    Me.ofsMembers.SetFocus
    Me![ofsMembers].Form![LastName].SetFocus
    DoCmd.FindRecord Me.txtFindWhat, acStart, , acDown, , acCurrent, True
    Me.txtFindWhat.SetFocus 'Set it back for the next char...
    Me.txtFindWhat.SelStart = 255   '...to the end
    Application.Echo True
End Sub
This can be slow with a big list.

Hope this is of some use to you.
Jeff Davies


Quote:
> Anyone able to point me in the right direction?

> I have a fairly simple relational staff database set up which handles
First
> Name, Last Name, Telephone, Dept, Title, Photo etc etc.

> As 3 people have access to the database for inputting new users, what I'd
> like to do is to have an Event that OnUpdate for the last name field
checks
> to see if there's already someone called that name. At the moment we're
> getting a few duplicate entries and it would help if it would pop up a
> message saying "A person/People of this name already exist(s) - would you
> like to cancel this new record?" or something similar.

> What will I need to do to create a Boolean function which returns True if
> the person exists and False if not. Sorry if this sound Basic I'm just not
> that familiar with recordsets etc.

> I know it's pretty much

> Function StaffExists(First, Last As String) As Boolean

> found = false
> do loop until found=true or eof
>     goto next record and compare the first and last names with the
> parameters
> loop

> StaffExists = found

> End Function

> But what do i need to do in between the Function and End Function to build
a
> recordset!

> Sorry for my utter naivety.

> Nick.



Sat, 02 Nov 2002 03:00:00 GMT  
 Testing for a record already in existence
FWIW, I'd use a query that references the input form as a criteria (the
format is Forms!FormName!ControlName).  Then, in your BeforeUpdate event,
open the query and see if the recordcount is greater than 1.

If it is, I would _strongly_ recommend that you give the user an option
besides cancelling the record.  Names aren't unique.  I once worked in a
fairly small office that had two men named Larry Simon and one named Lary
Simon, and all three of them had the same title.

HTH

--
Rebecca Riordan
Author of Designing Relational Database Systems
http://mspress.microsoft.com/books/3222.htm


Quote:
> Anyone able to point me in the right direction?

> I have a fairly simple relational staff database set up which handles
First
> Name, Last Name, Telephone, Dept, Title, Photo etc etc.

> As 3 people have access to the database for inputting new users, what I'd
> like to do is to have an Event that OnUpdate for the last name field
checks
> to see if there's already someone called that name. At the moment we're
> getting a few duplicate entries and it would help if it would pop up a
> message saying "A person/People of this name already exist(s) - would you
> like to cancel this new record?" or something similar.

> What will I need to do to create a Boolean function which returns True if
> the person exists and False if not. Sorry if this sound Basic I'm just not
> that familiar with recordsets etc.

> I know it's pretty much

> Function StaffExists(First, Last As String) As Boolean

> found = false
> do loop until found=true or eof
>     goto next record and compare the first and last names with the
> parameters
> loop

> StaffExists = found

> End Function

> But what do i need to do in between the Function and End Function to build
a
> recordset!

> Sorry for my utter naivety.

> Nick.



Sat, 02 Nov 2002 03:00:00 GMT  
 Testing for a record already in existence

Quote:

> Anyone able to point me in the right direction?

> I have a fairly simple relational staff database set up which handles First
> Name, Last Name, Telephone, Dept, Title, Photo etc etc.

> As 3 people have access to the database for inputting new users, what I'd
> like to do is to have an Event that OnUpdate for the last name field checks
> to see if there's already someone called that name. At the moment we're
> getting a few duplicate entries and it would help if it would pop up a
> message saying "A person/People of this name already exist(s) - would you
> like to cancel this new record?" or something similar.

> What will I need to do to create a Boolean function which returns True if
> the person exists and False if not. Sorry if this sound Basic I'm just not
> that familiar with recordsets etc.

> I know it's pretty much

> Function StaffExists(First, Last As String) As Boolean

> found = false
> do loop until found=true or eof
>     goto next record and compare the first and last names with the
> parameters
> loop

> StaffExists = found

> End Function

> But what do i need to do in between the Function and End Function to build a
> recordset!

> Sorry for my utter naivety.

> Nick.

Here's some code that does what you wanted:

### BEGIN SAMPLE CODE ###
Private Sub SURNAME_AfterUpdate()
On Error GoTo ErrPara

Dim dbCurr As Database
Dim rstCurr As Recordset

    Set dbCurr = CurrentDb()
    Set rstCurr = dbCurr.OpenRecordset("SELECT * FROM tblStaff WHERE (SURNAME =
'" & SURNAME & "')")
    If Not (rstCurr.BOF And rstCurr.EOF) Then
        If MsgBox("Another user has the same surname - are you sure you wish to
proceed", vbYesNo, "Confirm?") = vbNo Then
            'ADD YOUR CODE TO HANDLE AS REQUIRED HERE
        Else
            'ADD YOUR CODE TO HANDLE AS REQUIRED HERE
        End If
    End If

ExitPara:
    Exit Sub

ErrPara:
    MsgBox Err.Description, vbOKOnly, Err.Number
    Resume ExitPara
End Sub
### END SAMPLE CODE ###

Regards,

--
Roger E K Stout
Programmer
Database Development
Marconi Software Solutions Ltd.
Tel 01785 782339
Fax 01785 244397



Sat, 02 Nov 2002 03:00:00 GMT  
 Testing for a record already in existence
Alden, Jeff, Rebecca & Roger - Thanks very much for all your replies,
whichever one(s) I use - all the techniques are useful for future ref so
thanks a million!!

Nick.


Quote:
> Anyone able to point me in the right direction?

> I have a fairly simple relational staff database set up which handles
First
> Name, Last Name, Telephone, Dept, Title, Photo etc etc.

> As 3 people have access to the database for inputting new users, what I'd
> like to do is to have an Event that OnUpdate for the last name field
checks
> to see if there's already someone called that name. At the moment we're
> getting a few duplicate entries and it would help if it would pop up a
> message saying "A person/People of this name already exist(s) - would you
> like to cancel this new record?" or something similar.

> What will I need to do to create a Boolean function which returns True if
> the person exists and False if not. Sorry if this sound Basic I'm just not
> that familiar with recordsets etc.

> I know it's pretty much

> Function StaffExists(First, Last As String) As Boolean

> found = false
> do loop until found=true or eof
>     goto next record and compare the first and last names with the
> parameters
> loop

> StaffExists = found

> End Function

> But what do i need to do in between the Function and End Function to build
a
> recordset!

> Sorry for my utter naivety.

> Nick.



Sun, 03 Nov 2002 03:00:00 GMT  
 Testing for a record already in existence
Hi,

Ouch, the only case it could ever imagine it would have been worst is the
case if there would have been there three Rebecca Riordan <bg++>!

Vanderghast, Access MVP


(...)

Quote:
> If it is, I would _strongly_ recommend that you give the user an option
> besides cancelling the record.  Names aren't unique.  I once worked in a
> fairly small office that had two men named Larry Simon and one named Lary
> Simon, and all three of them had the same title.

> HTH

> --
> Rebecca Riordan
> Author of Designing Relational Database Systems
> http://mspress.microsoft.com/books/3222.htm



Sun, 03 Nov 2002 03:00:00 GMT  
 
 [ 9 post ] 

 Relevant Pages 

1. TEST TEST TEST TEST TEST TEST TEST TEST TEST TEST TEST

2. Testing for existence of a query

3. Testing for Existence of Table

4. Test for table existence

5. Test for the existence of a table

6. test for existence of a field

7. Testing for a queries existence

8. Testing for table existence

9. Testing for table existence & listing available tables

10. Testing for file existence

11. Test for Contact EMail1Address existence

12. test existence of style

 

 
Powered by phpBB® Forum Software