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?
1. I created a form with some search-fields which are related to a query. Then I added a Subform in which I put some more Search criteria (So that I can easily hide and unhide those additional searchfields). It sounds strange but is necessary ;-). Now I related those searchfields in the subform to the same query. When I run that query a window pops up that I should put in a value in all those searchfields which are in the subform. But I told Access that it should display all rows, if there is no value in those searchfields. Just as I did it with the Searchcriteria in the Main form. Do I have to do something special, when I have a query which is related to two Forms?
2. I want a searchfield to search in three different columns. Usually the value will just be found in one of those columns. As the Table I search is very long and has many searchfields and multiple of those will relate to more than one column, is there an easy way to do it in VBA? As I did it by using the "or" field when designing a query, but this seems very slow and unstable.
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?
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.
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.
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:
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 have a table of guests that holds all of their details such as name; address; medical info; family background; etc. What i need to do is to hold on the database if they would like to share a room and if so, who with.
I thought it would be possible to lookup the names of guests in the same table but access tells me otherwise.
I thought about creating a linked table of my guests table but when I link person A to person B then person B is not linked to person A which I would like to be the case.
I thought about creating a table called tblShare to hold 2 names from tblGuest but im not to sure how I would make this work. I think I may have the same situation as above. If I go to person A and link them to person B would this show up on Person B? I'm not too sure.
I will try this out now. If anyone has any other suggestions or some guidence on this I would be very gratefull.
I have a table with the following fields: Login ID, Description Password, Login Type, Location, Status. For every Login ID there are some old passwords that had different effective dates. I want my form or table to behave so that I can enter all the old passwords with the effective dates and keep adding to this record. I know it has something to do with subforms but I don't know how to do it. Please help. Thank you Yusuf
I have a main form, Contacts, for the user to input all the necessary data about a contact. What I'm ultimately trying to do is also include a subform which contains the name of each program (there are 12 or so) with a true/false checkbox. I'd like it to work so that if a program is added, it automatically gets added to the subform with a checkbox.
Also, I'd like the results of that subform to get dumped into tblProgram_Contacts.
Sigh...
A great vision... but I really am not sure on how to do any of this (specifically, properly defining the relationships and properly constructing the subform). Any suggestions would be greatly appreciated!
Hi all. This is my first post on this particular message board. I'm having a little problem getting Access to do what I want and hopefully someone here can help me out.
I have 2 forms. One larger form called RTV and one smaller one called Product Line. Now, most of the fields that are in the Product Line form are also in the larger RTV form. What I want, is for the product line people to enter information into the product line form. Once they're done, they exit the database and then, later, their managers look at this information in the product line, verify that it's correct, and then hit a command button that transfers all the fields that are the same on both forms, to the RTV form.
For instance, a prod line person would come in, fill out a date, part number and PO number and hit add record. Then, a manager would get on that same form, make sure everything is ok, and send the data over to the end of the RTV table.
Also, the product line people should not be able to see or edit the RTV form. Only a few people should have the priveldge of seeing both forms. Anyone have any ideas?
Hello, I have a pretty basic question about creating relationships in Access. When in the relationship window that shows all tables and associated relationships, I am a bit confused as to whether you are to drag the parent field to the child field or vice versa. When trying both, it appears to create the 1 to many relationship in the same direction no matter what way I create the relationship. So I am hoping someone will explain this a bit further to me or at least direct me to a reasource to clear up my, and possibly others, confusion. Sorry for my ignorance on this one! :o Thanks a lot, Dana S.
I am fiddling with a DB right now just to try to work out some table relationships. I have a PK, Name, it is accompanied by a year in school. I then want to relate the year in school in a different table with the requirements for that year.
such as
Year Requirement1 requirement2 requirement 3
these would be check boxes (yes/no), and I could accomplish it in this format, but I am trying to have a normalized DB so I realize it should be like this
1st year requirement 1st year requirement 1st year requirement
but I do not know how to link this back to the PK name when switching it to this format, so that I can pull up a form that shows if the person has completed the requirements for the year they are in. I am I going about this wrong? I have been looking at normalizing DBs and relationships but can't figure out the logic for my DB thought.
I'm having a bit of a problem figuring out how to do this...
I have a form (single form) and a subform (datasheet which is based on a query which includes the data elements [via outer join] in the single form). What I'm trying to do is have the data sheet form include (view only) all records from the query and have the single form display selected fields for edit/entry (will update a table that is the outer join in the query).
Example:
Table A has "Policy Number", "Line of Coverage", and "Insured ID". Table B has "Policy Number", "Insured ID", and "Insured Name".
Query 1: SELECT A."Policy Number", A."Line of Coverage", A."Insured ID", B."Insured Name" FROM "Table A" A, "Table B" B WHERE A."Policy Number" = B."Policy Number" (+) AND A."Insured ID" = B."Insured ID" (+)
(+) means outer join (sorry i'm used to using oracle).
So in the form I have a single form (FORM X) bound to Table B and a datasheet subform (FORM Y) bound to Query 1. I would like the user to be able to edit (or add if it doesn't exist) the "Insured Name" in FORM X based on the record currently selected in FORM Y.
Other things that may monkey with the process are the fact that even though Table B will have a unique key based on "Policy Number" and "Insured ID" Query 1 (and Table A) may have multiple "Line of Coverage" based on a single "Policy Number" and "Insured ID" combination.
Alternatively not relating the two forms, but simply populating the FORM X based on the record selected in FORM Y would be sufficient. Would this involve populating FORM X using the "on focus" function and how do I use vb to reference to a specific record in my subform?
Please let me know if I am not making any sense and thank you for your help.
