If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Adding possible new data to existing d/b
Hello, I am back with another Q.
As you may recall from my previous posts, I maintain a Youth Group's database, which was designed by my predecessors. (I am the only person who uses this database; I send reports to my boss in EXCEL format because she doesn't have access to ACCESS, lol.) I am a novice when it comes to this relational d/b stuff, so please bear with me. I hope I have provided enough information to you... I am using Access 2002 on WinXP with MS Office Prof'l. We have an existing d/b of about 3800 records. There is a new program coming up for next month, and the kids' registration forms will begin arriving in my mail next week. Before I go and add new records to our d/b, I want to know if there is a way to set the design of the d/b so that when I go and type a new namefield for a possible new record, I can be warned of a possible duplication of a previous record. Of kids who will attend this new program, it is possible that they attended prior programs and are already in our database. I don't want to duplicate records, nor do I really want to search the database for each registration form as it comes in (but I suppose I will if that's the only way to avoid duplications). Thanks in advanc, again. Max |
#2
|
|||
|
|||
You would have to set the 'Newname' field as index no duplicates. A default
msg will then appear if you enter something which is allready there. This is only a guide as I dont know the structure of your table/Newname field. If its peoples names there are usually at least two fields - FirstName, LastName maybe MidName the index would be a combination of all three "Max" wrote in message ... Hello, I am back with another Q. As you may recall from my previous posts, I maintain a Youth Group's database, which was designed by my predecessors. (I am the only person who uses this database; I send reports to my boss in EXCEL format because she doesn't have access to ACCESS, lol.) I am a novice when it comes to this relational d/b stuff, so please bear with me. I hope I have provided enough information to you... I am using Access 2002 on WinXP with MS Office Prof'l. We have an existing d/b of about 3800 records. There is a new program coming up for next month, and the kids' registration forms will begin arriving in my mail next week. Before I go and add new records to our d/b, I want to know if there is a way to set the design of the d/b so that when I go and type a new namefield for a possible new record, I can be warned of a possible duplication of a previous record. Of kids who will attend this new program, it is possible that they attended prior programs and are already in our database. I don't want to duplicate records, nor do I really want to search the database for each registration form as it comes in (but I suppose I will if that's the only way to avoid duplications). Thanks in advanc, again. Max |
#3
|
|||
|
|||
=?Utf-8?B?TWF4?= wrote in
: I want to know if there is a way to set the design of the d/b so that when I go and type a new namefield for a possible new record, I can be warned of a possible duplication of a previous record. The operative words here are "warned" and "possible". Out of 3800 records it is possible that you don't have two different George Browns, but the odds increase as the database grows. Setting a unique index (which will prevent such repeats) will not do what you want. Of kids who will attend this new program, it is possible that they attended prior programs and are already in our database. I don't want to duplicate records, nor do I really want to search the database for each registration form as it comes in (but I suppose I will if that's the only way to avoid duplications). How you handle this depends largely on your users and how often you anticipate (a) apparent duplicates and (b) real duplicates. One approach is to force the user to use a Search dialog before getting to an empty new record form -- one way to do this is to use the Load() event of the form. This is pretty painless if you are frequently coming across the same children, but a bore if you make people do searches that everyone knows are fruitless. Another approach is to look after the user has started, or finished, filling in the new record; using the Exit or BeforeUpdate events on the name field(s) or the form itself. You don't have to be quite so dismal about searching for a match: a simple DCount() expression does not take much to program, and not long to carry out as long as the table is properly indexed. Try something like ' put a filter together strWhere = "LastName = """ & txtLastName & """ " & _ "AND FirstName = """ & txtFirstName & """" ' count how many existing matches wMatches = DCount("*", "Children", strWhere) ' if there are none, don't worry about it any more If wMatches = 0 Then Exit Sub ' otherwise, you'll have to check with the user Call AskUserWhatToDoNext( boolCarryOn ) ' user is happy so don't do any special If boolCarryOn = True Then Exit Sub ' okay: we'll cancel the update or whatever Cancel = True The hardest part is knowing what to do with the matches. You probably need to show the user some kind of dialog box with the candidates and have him or her pick one or say, "no, it really is another brand new George Brown". This requires stepping right back from the computer and checking out different scenarios with your users. Another problem is picking up near matches: like "Geogre Brown" or even "Brown George". How sophisticated you ( want to | need to | are able to ) make this is up to you! Hope that helps Tim F |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Rapid input Via datasheet | RudyR_Seattle | General Discussion | 4 | January 31st, 2005 01:33 AM |
transpose | john | Using Forms | 1 | November 24th, 2004 06:16 PM |
How do I add data to multiple Excel cells that have existing data. | Paula | General Discussion | 2 | September 10th, 2004 03:23 AM |
Is this possible with Excel Chart? | q582gmzhi | Charts and Charting | 1 | September 8th, 2004 03:33 AM |