Table Has Had Autonumbers Removed And Need To Reinstate
Apr 8, 2008
Hi All
This is my first post so hope I get everything right :-)
Right i have three tables which are laid out as such
ID Number PK
Capacity_Band Number
Product Number
Price Number
previously the ID was a autonumber but this has been removed recently. When I try to change the ID back to Autonumber Access throws up an error. Some of the ID's occasionally get deleted which results in non consectutive numbers, normally I would strip the Id field and start again , but unfortunatly the id's are all interlinked , so this is not an option , unless i reset them all !!!!:mad:
My only thought is to set up a complicated series of lookups (probably vlookups in excel to reset the numbers) . Im hoping there is an easier way to reinstate the Autonumber on the ID field ? :confused:
New to this forum so be gentle! I'm also recently self taught on Access 2003 and developed a fairly substantial database for my company to track projects. My terminology may be off but hopefully youcan follow.
I have a number of queries created by copying existing queries and tweaking them. I was attempting to fine tune one of these by removing tables that had no bearing on the query. I was surprised to find that the number of records returned changed when I did this. I tried this with a number of queries and it seems to be common, on my database at least.
The tables removed had no fields selected for the query and had reltionships to only 1 of the remaining tables so it shouldn't have made any difference? In ever instance, the returned record set was a smaller nukber than before the unused table was removed.
Ok well I find myself in a bit of a pickle here... I created a database and allowed a friend to check it out. Well after he was finished and I try to open it I get a yellow exlamation point warning.
"You do not have the necessary permissions to use the database (It gives the whole path for the object). Have your system administrator or the person who created this object establish the appropriate permissions for you."
Now since I am the owner and creator how can this be removed? I am a bit miffed at my friend for whatever he did. Please tell me I can access this database again.
i have a query that shows 190000 records made up from related fields spread over many tables.
now i add in a new field to the query. It is related in the same way the others are, and indeed the information now appears in the query matched up correctly.
But for some reason the query now shows 670 less records?????
why is Access removing some records because of this new field??
I need to create a frontend for my database that enables average users to edit the tables. So far, I got as far as creating a tabbed form for this function, but now I would like to know if it is possible to "externalize" this form, so that the users wouldn't need to start Access to use it.
Is there any way to restore menus/editibility when they have been removed using ACCESS OPTIONS > CURRENT DATABASE then uncheck 'allow full menus'? Database was created in Access 2007. I always keep a back up of the pre-menu-disable state of my databases, but this is someone elses for which I can't find a backup.
Good afternoon! I have a database that tracks the withdraws and returns of shared tools. The withdraw form has a check box on it that is checked to show removed when the item is ordered (this keeps that item from showing up in inventory searches). When the item is returned, that check box is unchecked, showing that the item can be withdrawn again.
When a person withdraws an item their supervisors name is also logged so that we know who to chase down when the item doesn't come back.
I have a query that is supposed to show what items are still outstanding based on a date range (I got the prompt for date range to work..yea me). However, the query shows every time that the item was borrowed.
Example... I borrow a hammer and my supervisor is Bob. I return the hammer. It does not show as still withdrawn in my query.
The next I borrow the same hammer and again my supervisor is Bob. I return the hammer. It does not show as still withdrawn in my query.
The day after I borrow the same hammer and my supervisor is Joe. I return the hammer. It does not show as still withdrawn in my query.
Some other day I borrow the same hammer and my supervisor is Joe. I do not return the hammer. The two times I borrowed the hammer with my supervisor as Bob show up as well as the other time that I borrwed it with my supervisor as Joe shows up as well as the one that shows I still have it. . One of the criteria in the query is essentially only show the items that have the check box checked (criteria=true). But that apparently does nothing, or (more than likely), I am doing something wrong.
This is the SQL:
SELECT tblOrder.OrderDate, tblOrder.Supervisor, tblEmployee.EmployeeFirst, tblEmployee.EmployeeLast, tblItemSpecifics.Description, tblItemSpecifics.Serial_Number FROM (tblEmployee INNER JOIN tblOrder ON tblEmployee.GlobalID = tblOrder.GID) INNER JOIN (tblItemSpecifics INNER JOIN tblOrderInfo ON tblItemSpecifics.Serial_Number = tblOrderInfo.Serial_Number) ON tblOrder.OrderNumber = tblOrderInfo.Order_Number GROUP BY tblItemSpecifics.Removed, tblOrder.OrderDate, tblOrder.Supervisor, tblEmployee.EmployeeFirst, tblEmployee.EmployeeLast, tblItemSpecifics.Description, tblItemSpecifics.Serial_Number HAVING (((tblItemSpecifics.Removed)=True));
Hi. This is probably a stupid question but it is becoming annoying trying to figure it out.
I have created two linked tables and then created forms to enter information into them. While i was working i tested several pieces of information by entering information into the tables and forms. Now i am ready to start importing the intial data and the autonumber is at 13 for one table and around 6 or 7 for the other one.
Is there any possible 'easy' way, without creating the whole tables or forms again, that i can reset the autonumbers to start at 1 again?
I started work for a wireless company a while back, and they keep track of all the mac address's for customers. The problem was that they choose to use the serial number as the primary key.
Nothing wrong with that, but what is wrong is that the serials arnt totaly in order. They werent sure on there serial number scheme so they just kind of made it up at first, then settled into something more concrete later.
The first serial numbers go up to 999, and this is fine, its after this that things get complicated. They totaly skip the 2 thousands and start again at 3000.
What they have assigned me to do, since i am the one with the only access classes from college is fix it.
What they want to do is type the serial number in a box on a form and it bring up that record. I have already built the form and just tried adding a "find record" button but they didnt like it. They want to just type it in the form, not in a nother window.
Any suggestions? I have suggested we could add all the missing serial numbers to the list, but then we have huge sections of blank data. not a real big deal, but i cant remeber for the life of me how to just type in a few recuring numbers and have access finish it off. I can remeber in excel but its not the same.
I am hoping someone can help me here I have created a database with number of forms and queries. My problem is that my autonumbers dont start from one but start from 40,41,42...etc this started happening when I was putting some data in the tables for testing and for some reason went all strange have no idea/how it happened. How do I get it back to normal with the autonumbers in the tables to start from 1,2,3,4.....etc again?
I'm working on a login form and *not thinking* I removed access to the submenu. I had already set the form up so that you either login correctly or exit the program. I'm working in code for the form and I know it's possible to reactivate the menu in code, I just don't know how. And I can't switch to design mode. The form is set up to not allow anything to happen until the correct login has been entered, and I either had a typo when I set up my test login or the program is bugged and only says it's incorrect....
1st question and situation with 2 databases Customer and Plan
I have created two databases. In one of them I want to be able to assign each customer a Customer ID (CID). For the other I have a Plan Database where I want to include a (PID) Plan ID, this database will also contain the CID's relating to the first database.
Anyhoo, how can I add an autonumber to the database for my CID's? It says I cannot due so to an existing database? But I just want to assign the existing ones a number and in the future as I add more records to the database, I want the Autonumber function to come into play.
How can I do this for my Customer Table with the CID?
2nd question
How can I add two auto functions in my Plan Database that can give me (DATE EDITED, and DATE CREATED for each record I may add in the future)?
Also for this database, how can I tie the autofunction from my plan database together? I want to be able to have the autofunction also kick in for this database as more customers in the customer table are added, thus creating more plan ID's in my second database?
I trying to create a table where the primary key is a customerID, using the autonumber function. I would like to leave the new value setting on Random, since two people may be using database. However, I'm getting a lot of negative numbers as CustomerID's. If there a way I can limit the pool of random numbers to positive only.
I have a Client Table which has the following Primary Key: "Control ID".
The Control ID is an autonumber (ID increases by 1 with each new client).
Now, I also need to create another field called "Client ID". The Client ID is unique for each client, but needs to be a random number, in the range of between 100,000 and 1,000,000.
I've been using the Control ID for the last few months, but now need to add the Client ID. The Controld ID is mainly for internal purposes, and the Client ID will be given to the client.
I can't seem to add this new Client ID field, as tables don't allow for two autonumbers. Is there any way around this? It needs to be an autonumber field as is needs to be random, and entered automatically.
I am a little confused. I have one table with the Autonumber field and another table with a field that looks it up.
Tha problem is that I am using a Format for my Autonumber, e.g. "O0045". And the field with the foreign key refuses to accept it! It needs a number to be entered, i.e. 45, and then it can format it. So on the whole, the LookUp Feature does not work, because it suggests FormattedAutonumbers, which do not fit in (obviously the foreign key accepts numbers only).
Uh, hi. I'm new to using access, and I'm wondering if its possible to display an autonumber at the top of a form in a label box. For example, if I had a field set to autonumber and was ading a new record to the database, could I display the autonumber field on the actual form?
I have an orders database using Ac2K. Main tables are: tblOrders: Has OrderID which is autonumber, key index (no duplicates)
tblOrderDetail: Has OrderDetailID --long integer
Tables are related in one to many relationships on OrderID-OrderDetailID with both cascades set.
Database is split with tables and front end on server. I can't put front end on desktops because users can access from any of over 600 computers in the school district even tho there are not all that many simultaneous users.
Data is input on main form bound to tblOrders. When user puts in required information (Date,Vendor other etc.), I save record and autonumber generates a new OrderID. The subform window then opens and user then inputs the details of the order to the tblOrderDetail. There can be multiple entries here.
Form and subform are linked by their ID fields as master-child.
PROBLEM: System which runs on network with 2-3 simultaneous users is unstable. It works for a while (maybe 10-20 orders) and then crashes. The subform window is blank (never opens).
When I examine the tables directly I found these problems:
1) Two of the OrderID's (autonumbers) are the SAME! They are usually right above and below each other and usually tied to the same user altho other order info may be different. I don't see any data "collision" if it's the same user. I thought this was IMPOSSIBLE.
2) There is an entry or two in the tblOrderDetail with ######### in one or more fields. The OrderDetailID field is blank. I once found the ######### in the tblOrders as well.
3) The key index is removed from the OrderID.
4) When I go to the relationships window either the link is missing or the cascade and integrity are no longer active. If I try to re-establish, I get a message about missing key index or unmatched data. If I then go and DELETE the records with the ##########, I can re-establish the relationship. And it works for awhile again.
5. Compact and repair doesn't fix anything.
6. One user "thinks" she saw a quick message about "time-out". Is there some setting in Access that I need to change (Tools-Options-Advanced?)
ANY ideas would be much appreciated. There is nothing worse than trying to work with a system that works most, but not all, of the time.
Hi I have tried searching for an answer with no luck. I am writing a table to do a filing cabinet archive.
I have a table with 3 fields in one is box no, second is item no and third is a description.
I have the first 2 linked as a primary key. The description is just text. I want to be able to enter a box no and to have the second (item) to enter a number automatically following a sequential no which is relevant to that box. ie box no Item No 1...........1 1...........2 1...........3 2...........1 2...........2 3...........1 1...........4 this one takes the next no available for box no 1 2...........3 and this one does the same for box no 2
if you go back to a box and start reinputting it picks up the next number. should i sack trying to do it with the autonumber?
I am trying to create a db for software change requests. I have defined 3 tables:
PCR - Change request table. This table contains all info on the software bug. Release - Software release table. This table contains info about the release date, platform i.e. SAP Sponsor - The person funding for the change to take place. This table contains info on the sponsor such as Name.
I have identified that I need a one-to-many relationship between the tables. For example, A release will have many PCR's. A sponsor will have many PCR's they are funding.
In order to get a one to many (the many being on the PCR table) I have put two foreign keys in the PCR Table (Release_ID and BPM_ID). Both of these keys in thier own tables are autonumbers. From my undertstanding to get MS Access to relate the data I need to set the foreign keys in the PCR Table to autonumbers. Or do I? I do not want to change the primary keys in the Release and Sponsors table to datatype number as this would mean user manual input which I am trying to avoid.
Firstly I have been reading this forum for a while picking up hints and tips and am very grateful so big grats and thanks to all you guys for the help
The problem I am stuck on at the moment will hopfully be quite simple, but I should point out I have absolutely no skill or knowlede in Modules and VB language I most of my "stuff" via statements within the Design view.
Ok so here we go. I have a query which brings back the top 50 performing sales items, which is done by sorting them in descending order and using that filter thing at the top of the design view, which is set to 50. What I would like to do is insert a field in the query which gives the rank, so the best seller is given number 1, the second best 2 and so on and so forth.
Sorry if there is a topic already, I have tried to search, but couldnt find what I was looking for.
I need to generate quotation numbers for the quotes that I will be populating from my database. The process now is simply the format of date-n. The date is in standard format being "05222015" and the n is simply the number quote generated on that given date, For example the first quote generated today would be 05222015-1. Best way to generate these numbers automatically.
I have a question which hopefully someone can help me with.
I have a database that links into an Outlook email account. I get the information across via File => Get External Data => Link Tables.
However the issue I have is assigning some kind of autonumber to this table. I am not bothered what the number is, just so that I can differentiate between the records.
I have split the database I have created via the splitter-wizard and found that all tables with auto-numbers have re-set back to 1.
How do I get around this problem - I have tried running an append query but it flags up an error message. For certain functions to work in the database I need to set autonumbers at different amounts .
I'm using the "Value List" as the "Row Source Type" and data in the "Row Source" in a form. The data in the "Value List" is used in a "List Box" of this form. Also this form has a "Text Box" that is used for a date (in the "Short Date" format). When I select values from the "List Box", then enter a date in the "Text Box", the values from the "List Box" are unselected. How can I determine what is causing this ?
