Modules & VBA :: Auto Numbers When Priority Change
Jul 3, 2014
I have a very basic application to manage the tasks. In the main form "Tasks" one of the field is the priority (Order by) and I have this example:
Task Priority
B 1
A 2
C 3
E 4
D 5
If the user change one priority, I need a code to automatically change the others too respecting the new priority order. For example if the user change the priority 3 to 1 for the task C, the other numbers have to change to become:
Task Priority
C 1
B 2
A 3
E 4
D 5
There is a way to do this?
View Replies
ADVERTISEMENT
Aug 24, 2014
I have 2 fields that I would like to automate if possible
One field is called "p/o number" and another field called "line no"
These fields are part of an ordering database
Let say I have 200 items to purchase form 10 suppliers
And form example 20 items from each supplier
What I do at present is put the order number on each line item and the line number
example
p/o number line no
1 1
1 2
1 3
2 1
2 2
2 3
2 4
What I want to do is just put the first po number in the required line . Put the first line number in i.e. "1" and the macro will complete all the p/o numbers and line numbers for me as per the ones marked in red.
Example
1 1
2 2
3 3
2 1
2 2
2 3
View 5 Replies
View Related
May 20, 2005
Hello,
I am new to the world of databases, and I would like to know the easiest way to have a column with auto incrementing numbers.
I want that everytime a user enter data in a form, the new record goes into the database with a unique number.
thanks in advance
View 3 Replies
View Related
May 21, 2007
I have used priamry keys in my tables and these are set to auto numbers on the advise of fellow forumites.
One question if at the end of every year I need to upload batches of data to my tables, this would mean I would need to go through each record and manually enter the foreign keys in the linked table.
Surely Access has a better way of dealing with this issue?
View 5 Replies
View Related
Mar 30, 2005
Hi There,
I have a table that has two numbers, both require incrementing with each record.
These take the form of "00"0"/05" and "Book11 Enc"0.
I want these to update automatically but Access won't let me use two autonumbers in same form. Does anyone have a suggestion to get round it ?
Thanks.
Alex.
View 3 Replies
View Related
Jul 11, 2012
I have a field for SSN. If the SSN has a 0 or 00 as the first numbers it auto deletes those numbers. Is there a way in access that when the record is visible the 0's are relpaced so that the SSN will read the 9 digit. Also i have another fiels EIN with 7 digits an i need for there to be 2 9's in the beginning. I was goingto use vba but when i go to yp it i only get .value <
Code:
Me.SSN.Value
Value is tho only option to get. IDK y it does this or how to get it to shop if there is a ay.
View 1 Replies
View Related
Apr 2, 2012
Ineed to track Trailer security seals that are sequentially numbered with 8 digit numbers...They are physical seals and no efile with the numbers to upload into Access...is there a way I can put in a range and Access will populate the rest in a table?
View 5 Replies
View Related
Nov 21, 2012
My autonumbers are not in order for tables.As in for example for a customer
customerid customer first name customer last name
17 john parker
18 james johnson
19 alex scott
This is right at the top of the table the first sets of data,i want it to be
customerid
1
2
3
4
5
,etc
Why does it have a random number like "17" when I DO NOT have the first 17 customers !
View 6 Replies
View Related
Mar 26, 2013
I have an Access DB with a VB 6.0 front-end. I have a table with a PK that uses an auto-number. I deleted from data from this table in the past, then re-added it back in again just now. However, now when I go to enter a record in this table from the VB executable, I am getting an error for duplicate keys--as the auto-number tries to use a number that already exists. This happens when I just enter a record directly in the table in the DB also.
View 1 Replies
View Related
Jul 4, 2014
I would like to expand on this questions. I would like to generate a number based on a reference I use, a format like "14/000" where I use "14" with the last 2 digits of the year. However i want the auto number to generate the the last 2 digit automatically when the year changes i.e. when 2015 comes the field generates "15/000" without any prompt from the user.
View 1 Replies
View Related
Apr 25, 2013
I have inherited an Access DB that is supposed to be kept "alive" but not made much better. On a weekly basis I get an Excel CSV that I'm supposed to import into one massive table; that works just fine.
However, there is another query that takes values from a table named "New Users From Import Table" and appends them to the current "User" table.
But before running that query I have to manually update a field called "Display Name ID" with the next highest number from the User table but only the highest value that isn't in the 9000 number range.
For example, my next highest "Display Name ID" is 1144, which of course does not include the records that range between 9000 - 9008.
I had to manually enter 89 "Display Name ID" values yesterday and I know there has to be a way to update that field when I pull the data over from the temp table without having to do it all manually.
Here's the query that pulls the data into the "User" table (after I've manually entered the incremented values).
INSERT INTO Users ( [Display Name ID], [User Type], Organization, [Display Name], [Alias Name] )
SELECT [New Users From Import Table].[Display Name ID], [New Users From Import Table].[User Type], [New Users From Import Table].Organization, [New Users From Import Table].[Display Name], [New Users From Import Table].[Alias Name]
FROM [New Users From Import Table];
View 13 Replies
View Related
Feb 22, 2012
how to auto increment one field based on the value in another field. What I have is a table with six fields. There is a foreign key assigned to each record and linked to another table. Four fields in this table are number fields indicating accordingly a number for a category, a number for a subcategory, a number for a subject for a subcategory and a number for an instruction for each subject of subcategory. The last field is the text field for each instruction. Numbers are required to be included in the report. Also, number of categories, subcategories, subjects and instructions vary by each record in the the table which this table is linked to. What I'm trying to accomplish is to have numbers in the subcategory to auto increment starting with "1" for each number in the category field, numbers for each subject field would auto increment starting with "1" for each number in the subcategory field, and numbers in the instructions field to auto increment for each number in the subject field starting, again, with "1". I also need these numbers to restart with "1" for each new record in another table.
Basically, the table would look like this:
cat subcat subj instruction
1------1-------1------1
1------1-------2------1
1------2-------1------1
2------1-------1------1
2------1-------2------1
3------1-------1------1
3------1-------2------1
1------1-------1------1
(if the record in another table is a new/next table)and so forth..I have been looking for anything that could remotely work for me. Every VBA code I've tried is either don't work the way I need it to work, other codes making the field to continue increment numbers if the number already exists in the other field.
View 1 Replies
View Related
Apr 25, 2013
I need to create a MemberID field that autonumbers and uses a prefix.
EG: YearJoined+0001
I want this as a the PK as well.I tried a default value Year(Now())&format([ID],"0000") but it gives me errors.
View 7 Replies
View Related
Aug 8, 2007
i have a table and i want to make the first row and coloumn replace the a,b,c,d,1,2,3,4 system?
can i do this and if so how?
i have made a makeshift diagram in paint if you want to see it to get a better idea of what i mean please PM me.
cheers.
jack
View 2 Replies
View Related
Dec 29, 2014
I have the auto ID number set up set up on my Access database which gives me membership numbers. How do I have a supplementary auto numbering column to give me invoice numbers. You used to be able to do this automatically up until a few years ago. Now I have to enter them manually.
View 8 Replies
View Related
Dec 28, 2012
I have a table, at the table I'v got these fields:
ID | num1 | num2 | sum
I want that the user put numbers at 'num1' and 'num2' fields and then the 'sum' field will calculate automatically the operator (sum=num1+num2).
I've tried to put any combination at 'Default value' of the sum field (all the fields are numbers , also tried to change the sum field to text...nothing works).
I'm getting a message that 'num1' field is not recognize at the table
(I tried =[num1]+[num2], without the '=', num1.table+num2.table , ...nothing works)
I also tried to do it with SQL command but it dosn't work.
There is any way to do it, is it possible? Or other way to do it at least at Form or at Report ?
View 7 Replies
View Related
Apr 21, 2006
I know how to setup a Replica's Priority, but where do I go if I want to change it afterwards? I have searched everywhere, or am I just supposed to create another replica and set it then?
View 2 Replies
View Related
Feb 28, 2008
I found this from different fourm by searching google. This is exactly what I need to do and the answer was given; however, I can't figure it out. Can someone please post a step by step instruction on how to complete the following.
Thank you!
Question
I have a datbase full of projects that must be ranked in priority order. This was simple enough but when I add a project I will likely need to change the priority rank of all the "lesser" projects. With over 500 projects adding a new project with a rank of 50 means changing all the ranks 50 and above. I want to be able to add a project and when I determine its rank I insert the rank number and every rank equal to the project just added is the indexed by 1 digit. No two project can be ranks the same, one is always more important than another.
Answer
Create an update query that sets the priority field to:
[Priority] + 1
then set the criteria to
>= new number
Where new number is the rank of the new number.
This will increment by one each priority.
Hope this helps,
Scott<>
View 5 Replies
View Related
Sep 30, 2005
I have a form with cbo and the following code:
Private Sub cboOrderLocation_AfterUpdate()
If Me!cboOrderLocation = "Paradigm" Then
Me!txtOrderLocation = "PA"
Me!txtExpressAddress = "Paradigm Address"
Me!imgSelect = "LogoSmall.jpg"
ElseIf Me!cboOrderLocation = "Unassigned" Then
Me!txtOrderLocation = "NA"
Me!txtExpressAddress = "Not Assigned"
ElseIf Me!cboOrderLocation = "Express" Then
Me!txtOrderLocation = "EX"
Me!txtExpressAddress = "Slover Address"
Me!imgSelect = "LogoSmallParadigm.jpg"
End If
End Sub
The Me!txtOrderLocation and Me!txtExpressAddress both work great! I want to have it change the logo / image the same way. Is this possible? If so can someone tell me or show me how? I created an image on the form with the name imgSelect but when I compile it of course is giving me an error.
Thanks!
View 1 Replies
View Related
Feb 20, 2013
I have been asked if I can add a priority list to my database and I am trying to figure out how to do it.
The database already exists and this would be added code/forms. Basically this database has a number of users, all identified by a login name. Each of these users have several projects assigned to them. The projects are identified by a unique project number.
What I need to do is to be able to list all the projects that a user has, which is easy to do by simply creating a query, but then the user needs to be able to type in a priority number (1 to infinity) for each of these projects with no number repetitions. If a project priority is updated, say from 6 to 2, then the projects that had priorities 2 to 5, get a +1 added to their priority number so they go 3 to 6. Likewise if a project gets "demoted" (priority is lowered) then the ones below go up one.
View 1 Replies
View Related
Sep 23, 2005
I have just upgraded my large client database from Access 97 to Access 03. Unfortuntely all date of birth prior to 1930 are being displayed as 20??. IE date of birth 1928 displays 2028. Is there any way to automatically change this without altering the input mask and changeing each record individually. I have approx 15000 on the database, so not looking forward to doing it manually.
Any suggestions appreciated.
View 2 Replies
View Related
Jul 13, 2006
I have a form where sales can be entered. Whenever a [salesdate] is entered, I would like the [status] to automatically change to "Sold". How can I go about doing this?
View 1 Replies
View Related
Jan 27, 2015
before ACCESS Excel was used to create orders etc,we are up to order number 16223 now I have access and the auto number will start at 1.how can I get the auto number to start at 16223.
View 11 Replies
View Related
Nov 19, 2011
Can I change the primary key currently set to my products description to an auto number? I'm trying to make things right, also does the foreign key get an auto number?
View 2 Replies
View Related
Jun 3, 2015
I have a form presented in datasheet layout.
I set the initial sort by two columns, but I would like to keep highest priority for that initial sort, so when user wants to sort by some other columns that sort happens inside my initial sort.
View 13 Replies
View Related
Jan 21, 2013
I have created an access application for production system .
I have a final stock query and 2 to 3 orders based on priority and i want to create a shortage list based on priority ... here is detail :
ItemName....|Stock qty....|ORDER1....|ORDER2....|ORDER3....|
A.................|200............|40............. |90............|100...........|
B.................|500............|0.............. .|400..........|200...........|
C.................|30..............|40............ .|0..............|20............|
D.................|0...............|0............. ..|10............|0...............|
E.................|900............|400...........| 300..........|280...........|
F.................|130............|200...........| 50............|0...............|
Now I want shortage as:
ITEM NAME |O1 SHORTAGE | O2 SHORTAGE | O3 SHORTAGE|
A................|00..................|00......... ..........|30..................|
B................|00..................|00......... ..........|100.................|
C................|10..................|00......... ..........|20...................|
D................|00..................|10......... ..........|00...................|
E................|00..................|00......... ..........|80...................|
F................|70..................|50......... ...........|00...................|
What method Do I use to write a query as I have hundreds of Products and 40-50 orders????????
View 6 Replies
View Related
