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
|
|||
|
|||
'Record does not exist in list', then 'Record already exists' messages
I'm the network guy here; pretty clueless when it comes to Access and DBs.
I'm not sure the best way to present the problem, so I'm going to throw a bunch of stuff at you guys and you tell me what further information I need to provide. We had an Access DB designed and built for us a number of years ago. Users are now having trouble entering new 'Producers' into the Project forms. When they try to enter a name into the Produce field they receive the message "[Producer name] is not a contact in this list, do you want to add this contact to the list?" They click Yes and immediately get the message "[Producer name] already exists with the title Producer. Cannot add a duplicate contact with this title." The Project form links to tables in the DB. In Design View I can see which tables are being referenced. For the Producer field I cannot see a table. It shows a ruler toolbar with a scrollbar underneath. But when entering data into the field I am able to select names from the drop-down list, which contains names from the Contacts table. The Project form also has fields for Director and Cast, which also link to the contacts table, and those are working fine as far as I know. (Not all the same names show up in the drop-down for the Producer, Director, and Cast field. I think this is because the Contact table has columns for Type (person or company) and Title (Producer, Director, etc...) and the drop-downs must be filtered for title.) One other thing, When I look at the Contact table, the highest record number is 26235. When I look at the Contact form, it says record 1 of 25676. Looking at the Table, I see the name of the producer they're trying to enter; he's record 26234. However, an actor is listed as record 26231, and I have no problem entering him in the Cast field of this project. I'm also getting a ton of application-defined or object-defined errors when trying to enter this Producer's name, or close the window after failing. Most often the error # is 3058, but I've also seen 3022, 2148 and a few others. I've tried deleting the record of the producer from the Contacts table, but that doesn't work, either. Bottom line: Why would I get the two conflicting messages (record doesn't exist, duplicate record exists) when trying to enter the new Producer. Thanks in advance |
#2
|
|||
|
|||
'Record does not exist in list', then 'Record already exists' mess
First, record numbers have no meaning in Access. Record numbers only apply
to the current open recordset, so depending on filtering and sorting, the relative position of a record will change. So that is not an issue. How long since a compact and repair has been done on this database? I would suggest you try that first. Access applications are usually installed in two parts. The Front End and the Back End. The front end contains all the forms, code, querys, reports, etc and the Back End contains all the data. The Back End resides on a shared folder or file server and each user has a copy of the Front End on their own computer. Each of these files has an mdb file extension. The Front End obtains data from the Back End through a process called Linking. This is done from the Front End using the Linked Table Manager to tell the application where the data resides. That is the correct installation. You will find some cases where everything is in one large mdb. You will also find cases where it has been properly split, but evey user is sharing the same Front End. Either of these configurations is prone to corruption. Here is what you should do first. Determine the configuration. See what file the users are opening to start the application. This will be either the Front End or the whole database, meaning it has not been split. Open the application. Often developers will keep users out by manipulating the available menus, etc. While you open the app, hold down the Shift Key. This will bypass any start up routines (the majority of the time). Once you can get into the database select Tools, Database Utilities, Linked Table Manager. Now you will be able to see the path to the data tables. That will be the Back End database. If you get a message saying there are no linked tables, that means the database is not split. Next, get every user to close their application. First (after being sure we have a good backup), we will compact and repair the Back End. Tools, Database Utilities, Compact and Repair Database. Depending on the size, it may take a while. Note, before you do that, check the size of the Back End mdb. There is a 2GB size limit to an Access Database. Once this completes, Do the same for each user's Front End database. After that is done, use the Linked table manager to refresh the links. Tools, Database Utilities, Linked Table Manager. The click Select All and Ok. Let us know if this helps any. "TOP" wrote: I'm the network guy here; pretty clueless when it comes to Access and DBs. I'm not sure the best way to present the problem, so I'm going to throw a bunch of stuff at you guys and you tell me what further information I need to provide. We had an Access DB designed and built for us a number of years ago. Users are now having trouble entering new 'Producers' into the Project forms. When they try to enter a name into the Produce field they receive the message "[Producer name] is not a contact in this list, do you want to add this contact to the list?" They click Yes and immediately get the message "[Producer name] already exists with the title Producer. Cannot add a duplicate contact with this title." The Project form links to tables in the DB. In Design View I can see which tables are being referenced. For the Producer field I cannot see a table. It shows a ruler toolbar with a scrollbar underneath. But when entering data into the field I am able to select names from the drop-down list, which contains names from the Contacts table. The Project form also has fields for Director and Cast, which also link to the contacts table, and those are working fine as far as I know. (Not all the same names show up in the drop-down for the Producer, Director, and Cast field. I think this is because the Contact table has columns for Type (person or company) and Title (Producer, Director, etc...) and the drop-downs must be filtered for title.) One other thing, When I look at the Contact table, the highest record number is 26235. When I look at the Contact form, it says record 1 of 25676. Looking at the Table, I see the name of the producer they're trying to enter; he's record 26234. However, an actor is listed as record 26231, and I have no problem entering him in the Cast field of this project. I'm also getting a ton of application-defined or object-defined errors when trying to enter this Producer's name, or close the window after failing. Most often the error # is 3058, but I've also seen 3022, 2148 and a few others. I've tried deleting the record of the producer from the Contacts table, but that doesn't work, either. Bottom line: Why would I get the two conflicting messages (record doesn't exist, duplicate record exists) when trying to enter the new Producer. Thanks in advance |
#3
|
|||
|
|||
'Record does not exist in list', then 'Record already exists' mess
Klatuu,
Thanks for the response. The problem with adding a new producer to the form was reported to me over a month ago. That night I did a compact/repair on the database (which exhausted my store of DB knowledge) the way you suggested: I had everyone log off, then compacted and repaired. I told everyone (about 15 regular users) to give it a 10-minute test-run the next day and report any problems--specifically trying to add producers to the project form. Yesterday someone told me they "still can't add producers." So while it's been a month since I last compacted/repaired, I did it the day the problem was reported, but it didn't help. It looks like you guessed correctly that our application was built poorly : ) From your description I'd say it was split, but everyone is sharing the same Front End. All the tables are listed in the Linked Table Manager, and are linked to the DB, but users create a shortcut of the MDB from the shared folder and copy that to their desktops to access the DB. (There is one companydata.mdb and one company.mdb as the front end.) This being the case, is it necessary or even possible to compact and repair each user's front end? I did the compact/repair from a workstation, and since everybody's using the same front end, I figured it would apply for everyone? Thanks again "Klatuu" wrote in message ... First, record numbers have no meaning in Access. Record numbers only apply to the current open recordset, so depending on filtering and sorting, the relative position of a record will change. So that is not an issue. How long since a compact and repair has been done on this database? I would suggest you try that first. Access applications are usually installed in two parts. The Front End and the Back End. The front end contains all the forms, code, querys, reports, etc and the Back End contains all the data. The Back End resides on a shared folder or file server and each user has a copy of the Front End on their own computer. Each of these files has an mdb file extension. The Front End obtains data from the Back End through a process called Linking. This is done from the Front End using the Linked Table Manager to tell the application where the data resides. That is the correct installation. You will find some cases where everything is in one large mdb. You will also find cases where it has been properly split, but evey user is sharing the same Front End. Either of these configurations is prone to corruption. Here is what you should do first. Determine the configuration. See what file the users are opening to start the application. This will be either the Front End or the whole database, meaning it has not been split. Open the application. Often developers will keep users out by manipulating the available menus, etc. While you open the app, hold down the Shift Key. This will bypass any start up routines (the majority of the time). Once you can get into the database select Tools, Database Utilities, Linked Table Manager. Now you will be able to see the path to the data tables. That will be the Back End database. If you get a message saying there are no linked tables, that means the database is not split. Next, get every user to close their application. First (after being sure we have a good backup), we will compact and repair the Back End. Tools, Database Utilities, Compact and Repair Database. Depending on the size, it may take a while. Note, before you do that, check the size of the Back End mdb. There is a 2GB size limit to an Access Database. Once this completes, Do the same for each user's Front End database. After that is done, use the Linked table manager to refresh the links. Tools, Database Utilities, Linked Table Manager. The click Select All and Ok. Let us know if this helps any. "TOP" wrote: I'm the network guy here; pretty clueless when it comes to Access and DBs. I'm not sure the best way to present the problem, so I'm going to throw a bunch of stuff at you guys and you tell me what further information I need to provide. We had an Access DB designed and built for us a number of years ago. Users are now having trouble entering new 'Producers' into the Project forms. When they try to enter a name into the Produce field they receive the message "[Producer name] is not a contact in this list, do you want to add this contact to the list?" They click Yes and immediately get the message "[Producer name] already exists with the title Producer. Cannot add a duplicate contact with this title." The Project form links to tables in the DB. In Design View I can see which tables are being referenced. For the Producer field I cannot see a table. It shows a ruler toolbar with a scrollbar underneath. But when entering data into the field I am able to select names from the drop-down list, which contains names from the Contacts table. The Project form also has fields for Director and Cast, which also link to the contacts table, and those are working fine as far as I know. (Not all the same names show up in the drop-down for the Producer, Director, and Cast field. I think this is because the Contact table has columns for Type (person or company) and Title (Producer, Director, etc...) and the drop-downs must be filtered for title.) One other thing, When I look at the Contact table, the highest record number is 26235. When I look at the Contact form, it says record 1 of 25676. Looking at the Table, I see the name of the producer they're trying to enter; he's record 26234. However, an actor is listed as record 26231, and I have no problem entering him in the Cast field of this project. I'm also getting a ton of application-defined or object-defined errors when trying to enter this Producer's name, or close the window after failing. Most often the error # is 3058, but I've also seen 3022, 2148 and a few others. I've tried deleting the record of the producer from the Contacts table, but that doesn't work, either. Bottom line: Why would I get the two conflicting messages (record doesn't exist, duplicate record exists) when trying to enter the new Producer. Thanks in advance |
#4
|
|||
|
|||
'Record does not exist in list', then 'Record already exists'
If they are just using the Shortcut to open the front end on the server, then
a compact and repair on that mdb will take care of everyone. How many users are sharing the front end and what is the estimated maximum concurrent users? I am wondering if some record locking is going on. Could be that a user is leaving a record open and another user can't do an update. Doesn't have to be the same record if the locking scheme is not set correctly, just has to be on the same page. I would look into how record locking is set up. Shared front ends have a tendancy to blot. I would suggest you turn on Compact On Close. (Tools, Options, General tab, about 2/3 down on left side). This will cause the front end to compact when it is closed. If multipe users have it open, it will not do the compact until the last user closes it. Compact on Close doesn't seem to work on back ends. You have to manually compact them. The aren't really "opened" in the pure sense of the word. It would be advisable, if you can, to have each person have copy of the front end on their own desk top. This will reduce network traffic. It is odd that only producers can't be entered, though. You might suspect a coding problem. One other thing you might try on both mdbs is a decompile. The decompile is non supported and should be used with caution. Here is a site with info on that: http://www.granite.ab.ca/access/decompile.htm Now, if it is not a record locking, code, or bloating problem, it could be you have some corruption going on in your database. Here is a way to fix that. Create a new mdb. Copy every object in the old database into the new database. If the back end is done correctly, there should be nothing but tables in it. Try using the new database to see if you get better results. If you get through all this and are still having problems, I suggest you engage the services of a good Access professional to help resolve your problem. Post back and let me know how it goes or if you have more questions. "TOP" wrote: Klatuu, Thanks for the response. The problem with adding a new producer to the form was reported to me over a month ago. That night I did a compact/repair on the database (which exhausted my store of DB knowledge) the way you suggested: I had everyone log off, then compacted and repaired. I told everyone (about 15 regular users) to give it a 10-minute test-run the next day and report any problems--specifically trying to add producers to the project form. Yesterday someone told me they "still can't add producers." So while it's been a month since I last compacted/repaired, I did it the day the problem was reported, but it didn't help. It looks like you guessed correctly that our application was built poorly : ) From your description I'd say it was split, but everyone is sharing the same Front End. All the tables are listed in the Linked Table Manager, and are linked to the DB, but users create a shortcut of the MDB from the shared folder and copy that to their desktops to access the DB. (There is one companydata.mdb and one company.mdb as the front end.) This being the case, is it necessary or even possible to compact and repair each user's front end? I did the compact/repair from a workstation, and since everybody's using the same front end, I figured it would apply for everyone? Thanks again "Klatuu" wrote in message ... First, record numbers have no meaning in Access. Record numbers only apply to the current open recordset, so depending on filtering and sorting, the relative position of a record will change. So that is not an issue. How long since a compact and repair has been done on this database? I would suggest you try that first. Access applications are usually installed in two parts. The Front End and the Back End. The front end contains all the forms, code, querys, reports, etc and the Back End contains all the data. The Back End resides on a shared folder or file server and each user has a copy of the Front End on their own computer. Each of these files has an mdb file extension. The Front End obtains data from the Back End through a process called Linking. This is done from the Front End using the Linked Table Manager to tell the application where the data resides. That is the correct installation. You will find some cases where everything is in one large mdb. You will also find cases where it has been properly split, but evey user is sharing the same Front End. Either of these configurations is prone to corruption. Here is what you should do first. Determine the configuration. See what file the users are opening to start the application. This will be either the Front End or the whole database, meaning it has not been split. Open the application. Often developers will keep users out by manipulating the available menus, etc. While you open the app, hold down the Shift Key. This will bypass any start up routines (the majority of the time). Once you can get into the database select Tools, Database Utilities, Linked Table Manager. Now you will be able to see the path to the data tables. That will be the Back End database. If you get a message saying there are no linked tables, that means the database is not split. Next, get every user to close their application. First (after being sure we have a good backup), we will compact and repair the Back End. Tools, Database Utilities, Compact and Repair Database. Depending on the size, it may take a while. Note, before you do that, check the size of the Back End mdb. There is a 2GB size limit to an Access Database. Once this completes, Do the same for each user's Front End database. After that is done, use the Linked table manager to refresh the links. Tools, Database Utilities, Linked Table Manager. The click Select All and Ok. Let us know if this helps any. "TOP" wrote: I'm the network guy here; pretty clueless when it comes to Access and DBs. I'm not sure the best way to present the problem, so I'm going to throw a bunch of stuff at you guys and you tell me what further information I need to provide. We had an Access DB designed and built for us a number of years ago. Users are now having trouble entering new 'Producers' into the Project forms. When they try to enter a name into the Produce field they receive the message "[Producer name] is not a contact in this list, do you want to add this contact to the list?" They click Yes and immediately get the message "[Producer name] already exists with the title Producer. Cannot add a duplicate contact with this title." The Project form links to tables in the DB. In Design View I can see which tables are being referenced. For the Producer field I cannot see a table. It shows a ruler toolbar with a scrollbar underneath. But when entering data into the field I am able to select names from the drop-down list, which contains names from the Contacts table. The Project form also has fields for Director and Cast, which also link to the contacts table, and those are working fine as far as I know. (Not all the same names show up in the drop-down for the Producer, Director, and Cast field. I think this is because the Contact table has columns for Type (person or company) and Title (Producer, Director, etc...) and the drop-downs must be filtered for title.) One other thing, When I look at the Contact table, the highest record number is 26235. When I look at the Contact form, it says record 1 of 25676. Looking at the Table, I see the name of the producer they're trying to enter; he's record 26234. However, an actor is listed as record 26231, and I have no problem entering him in the Cast field of this project. I'm also getting a ton of application-defined or object-defined errors when trying to enter this Producer's name, or close the window after failing. Most often the error # is 3058, but I've also seen 3022, 2148 and a few others. I've tried deleting the record of the producer from the Contacts table, but that doesn't work, either. Bottom line: Why would I get the two conflicting messages (record doesn't exist, duplicate record exists) when trying to enter the new Producer. Thanks in advance |
#5
|
|||
|
|||
'Record does not exist in list', then 'Record already exists'
OK, I've done a compact and repair of the DB, but no dice. (It fixed a new
problem with a report, but not entering this producer.) It would be advisable, if you can, to have each person have copy of the front end on their own desk top. This will reduce network traffic. how would I go about this? Is it as simple as copying the MDB to everyone's computer, rather than copying shortcuts to the same mdb? I'll try the other suggestions this week (hopefully) on my duplicate DB. Thanks again One other thing you might try on both mdbs is a decompile. The decompile is non supported and should be used with caution. Here is a site with info on that: http://www.granite.ab.ca/access/decompile.htm Now, if it is not a record locking, code, or bloating problem, it could be you have some corruption going on in your database. Here is a way to fix that. Create a new mdb. Copy every object in the old database into the new database. If the back end is done correctly, there should be nothing but tables in it. Try using the new database to see if you get better results. If you get through all this and are still having problems, I suggest you engage the services of a good Access professional to help resolve your problem. Post back and let me know how it goes or if you have more questions. |
#6
|
|||
|
|||
'Record does not exist in list', then 'Record already exists'
"TOP" wrote: OK, I've done a compact and repair of the DB, but no dice. (It fixed a new problem with a report, but not entering this producer.) It would be advisable, if you can, to have each person have copy of the front end on their own desk top. This will reduce network traffic. how would I go about this? Is it as simple as copying the MDB to everyone's computer, rather than copying shortcuts to the same mdb? It is almost that simple. You start there, then you need to be sure the table links are correct. Since everyone has been sharing a front end, I would expect the drive mapping is already using UNC paths. Be sure they are and then refresh the links just to be sure. Before I copied the front end to each user's computer, I would go through the same process with the front end. Try a decompile and a compact and repair on a copy of the front end and see if you get any results. If not, I think we are down to creating a new front end. That would be as I described earlier, create a new mdb and copy all the objects from the current mdb. I'll try the other suggestions this week (hopefully) on my duplicate DB. Thanks again One other thing you might try on both mdbs is a decompile. The decompile is non supported and should be used with caution. Here is a site with info on that: http://www.granite.ab.ca/access/decompile.htm Now, if it is not a record locking, code, or bloating problem, it could be you have some corruption going on in your database. Here is a way to fix that. Create a new mdb. Copy every object in the old database into the new database. If the back end is done correctly, there should be nothing but tables in it. Try using the new database to see if you get better results. If you get through all this and are still having problems, I suggest you engage the services of a good Access professional to help resolve your problem. Post back and let me know how it goes or if you have more questions. |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Count in a query | Ben | Running & Setting Up Queries | 21 | April 4th, 2006 10:12 PM |
Generating New Record in different Table when Item is not in List | jjacob@MEI | Using Forms | 1 | October 13th, 2005 03:16 AM |
Need Help In Printing Current Record in Specific Report | RNUSZ@OKDPS | Setting Up & Running Reports | 1 | May 16th, 2005 09:06 PM |
Determine if a corosponding record exist for a master record on a subreport | Jasonm | Setting Up & Running Reports | 5 | January 29th, 2005 02:59 AM |
synchronizing form and list box | Deb Smith | Using Forms | 8 | June 21st, 2004 08:15 PM |