VBA, Testing for a record already in existence

Testing for a record already in existence

Author	Message
Nick Alle #1 / 9	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

Alden Streete #2 / 9	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

Jeff Davie #3 / 9	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

Rebecca Riorda #4 / 9	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

Roger Eng Kiat Stou #5 / 9	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

Nick Alle #6 / 9	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

Michel Wals #7 / 9	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

Page 1 of 1

[ 9 post ]