By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,203 Members | 1,392 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,203 IT Pros & Developers. It's quick & easy.

Auto Numbering a specific field within a database

P: 8
Good evening I have a database where I a command button that when pressed I wan to see the value from the previous record/field and then add 1 to it. I have never built a code where I had to look backwards for part of the equations to give me the value. Below is what I was playing with just to see if I could get it to work with out help, but I can not get it to look backwards for part of the value.

Should , acLast, Me.DRAWING_NO >= 105999, , acNewRec, Me.DRAWING_NO = 106000
Should , acLast, Me.DRAWING_NO = Me.DRAWING_NO <= 106000, , acNewRec, Me.DRAWING_NO = Me.DRAWING_NO + 1

I would be happy using auto number if I knew how to reset the first number it puts out instead of it being 1 I need it to start out at 10600.

Any help would be thankful.

Lloyd
Nov 2 '12 #1
Share this Question
Share on Google+
29 Replies


NeoPa
Expert Mod 15k+
P: 31,494
Your code makes no sense to me. I can't even tell if it's SQL or VBA :-(

However, if you need to determine the highest value in a table field then you could use DMax(). As the whole context you're working in is so unclear there's not much more I can say at this stage. If you start there and reply with a clearer question if you get stuck then that should work.
Nov 2 '12 #2

P: 8
What I have is a database that I want to have perform an autonumber function for each new entry. But I do not want to start out at 1. How can I change or get the autonumber feature to do this.

Above I was just playing around with different codes (VBA) trying to get my field to look at the previous entry of a specific field and then add 1 to it. I hope this helps
Nov 2 '12 #3

NeoPa
Expert Mod 15k+
P: 31,494
You cannot get AutoNumber to do this for you. AutoNumber only guarantees to provide unique values. It never stipulates even that they are provided in any order, let alone an order with a specific starting position.

As I mentioned in the earlier post you will need to use DMax() to determine the highest value contained within the table then add 1 to that.
Nov 2 '12 #4

zmbd
Expert Mod 5K+
P: 5,397
Using an insert query that duplicates the table format of the one with the autonumber field such that the related field is one less than the desired start number. Insert the record. Delete the record.

Tbl_WAutoNumberExample
[WAutoNumberExample_PK] : Autonumber, primary key
[WAutoNumberExample_someinfo] : text(50)

Tbl_NoAutoNumberExample
[NoAutoNumberExample_pk] : Numeric(Long), primary key
[NoAutoNumberExample_someinfo] : text(50)

Say you want to start:
Tbl_WAutoNumberExample.[WAutoNumberExample_PK]=2000

Then:
Tbl_NoAutoNumberExample.[NoAutoNumberExample_pk]=1999

Make a select query against Tbl_NoAutoNumberExample
Change to Insert query against Tbl_WAutoNumberExample

Delete the record with 1999 in pk
Next entry will start with 2000

There is also the method that uses the ADOX library; however, I've had issues with this when my other client pc run if this library isn't available: http://allenbrowne.com/func-ADOX.html#SetSeed
Nov 2 '12 #5

TheSmileyCoder
Expert Mod 100+
P: 2,321
The normal(most common) reason for implementing a custom id is to avoid gaps. This is one thing that the autonumber cannot do. IF someone starts a record, and then cancels it, the number has been spent. In some cases people think of this as a problem, and implement a custom numbering mimicking the autonumber behavior.


If your requirement is simply to have the autonumber starting with 10600 then you can use this to reset the autonumber counter:
Expand|Select|Wrap|Line Numbers
  1. currentdb.Execute "ALTER TABLE tbl_Test ALTER COLUMN PK_Auto COUNTER(10600,1)"
Be aware that if you have any records allready existing in the table then you will get a key violation when you try to reuse that key. So whatever number you use, must be higher then the currently largest number in the table.
Nov 2 '12 #6

NeoPa
Expert Mod 15k+
P: 31,494
Amendment:
NeoPa:
You cannot get AutoNumber to do this for you.
While it may be possible to set the value returned by AutoNumber, it is not recommended to rely on it returning sequential values in the way you want it to. Certainly it generally does, in practice, but it isn't guaranteed to and was never, ever, designed to provide that functionality. It was designed to provide a guaranteed unique value in the table. If you continue down this path you are likely to find that gaps occur and you will be frustrated trying to avoid those. Again, it was never set up to provide that functionality. What it was set up to do it does well. Using it for other purposes can work, but frequently frustrates.
Nov 2 '12 #7

TheSmileyCoder
Expert Mod 100+
P: 2,321
Just to further illustrate the point NeoPa is making, in a multiuser DB you could have user start a record in the morning "drawing" number 10687 but not saving that record for hours, in the mean time other users might "draw" and use numbers 10688 through 10712. It is important to notice that the number in this case is "drawn" or taken when the record is first STARTED and not when it is saved.

In some cases this matters little, in others its a problem. You need to find out whether its a problem or not.

Benefits of autonumber:
Its always unique.
Easy to implement.
Nov 2 '12 #8

P: 8
I see what you all are talking about and yes using autonumber would be a problem for me.

I am going to try DMax() + 1, as I have not used this function before.

I hate to admitt but I am actualy enjoying this part of my database as I am getting to learn something new.

Lloyd
Nov 2 '12 #9

NeoPa
Expert Mod 15k+
P: 31,494
LMott:
I hate to admit it, but I am actualy enjoying this part of my database as I am getting to learn something new.
That's what we like to hear Lloyd. It's what makes helping here so much fun.

PS. Let us know if you struggle (and exactly what you're struggling on of course) and we can jump in and assist.
Nov 2 '12 #10

P: 8
Well I have tried to figure out how to use and where to put the DMax function but I have come up short. Where and how do I use this function. thank you

Lloyd
Nov 9 '12 #11

NeoPa
Expert Mod 15k+
P: 31,494
It seems I wasn't clear enough when I suggested letting us know exactly what you're struggling on. Without that information it will be very hard for us to help you. Your existing code would be a very basic start to imparting it.

In very fundamental terms you would use the function at the point at which you need to add a new record. The function takes three parameters (The last of which is optional).

Expr: The value from the domain (table or query) to be summed.
Domain: The table or query to process to retrieve the information.
Criteria: If specified, define which records from the Domain are to be included in the processing.
Nov 9 '12 #12

P: 8
No you were clear I was in a big hurry as I have been swamped with several rush projects as of late.

In my database I have a form linked to a table, here is some of the fields in the form that require input from the user;
Date User Input
Project Name User Input
Project Number User Input
Created By User Input
Procedure Description User Input


Once all of the fields have been filled out then there is a Tab named "Procedure Number" that is linked to a text box who's Control is in my table. What I want to happen is once the user clikcs on the "Procedure Tab" I want the text box to display the next numerical number, but I do not want to start out at 001 or 1. I need to start out with 300 or somehing like that.

I have never created a database where it needed to perform a function that had to get its numeric value from the previous record. So I am at a loss here, but enjoying the learning experience happening with this part of the database. If I need to provide additional details what I can do is create a word document and go through the entir form and each function in it if needed.

Lloyd
Nov 15 '12 #13

NeoPa
Expert Mod 15k+
P: 31,494
OK Lloyd. Let's start with some basics.

Details need to be posted within the post rather than in attached documents. That said, I appreciate the willingness to engage indicated by the statement ;-)

In post #12 I gave you the parameter details of the function DMax() that I first introduced in post #4. I've been looking for something in any of your responses that indicates that you're responding to this information. Nothing as yet unfortunately. To progress we will need two-way communication. If we get no feedback then we can spout till the cows come home and still not say anything that works for you, as we don't know what you can and cannot understand.

Ultimately, we'll need details (mainly names) of the table, the field, the control where this pseudo-sequential data is to be held. We also need a full and exact description of the intended data, including the base number, or the number that the sequence starts with when no existing records yet exist.

In human terms we :
  1. Find in the table the maximum value of this field that matches what we're working on.
  2. Extract the sequential portion.
  3. Add 1 (or any other number) to that.
  4. Reformulate the field data using the new result.
  5. Assign it to the new record.

If we're to look at reproducing this in code then we'll clearly need all that information available to us.
Nov 15 '12 #14

P: 4
If your database table has following fields
Date
Project Name
Project Number
Created By
Procedure Description than you must have a field for "Procedure Number" with default zero (0).
At 1st instance of filling form Procedure Number will be 0.
Now you want that as 300 and subsequent number as 301, 302 and so on.
Let your Tab run a update query on field "Procedure Number"
Update to IIf([Table Name].[Procedure Number] = 0,300,[Table Name].[Procedure Number]+1)
Nov 16 '12 #15

NeoPa
Expert Mod 15k+
P: 31,494
Apca:
Let your Tab run a update query on field "Procedure Number"
Update to IIf([Table Name].[Procedure Number] = 0,300,[Table Name].[Procedure Number]+1)
This is hard to respond to without sounding disrespectful, but let me try :

This doesn't provide a solution that would be usable, as what is required should run as part of the form and be seamless for the operator.

If you were ever to run such a query then all existing records would be incremented every time a new record was entered. This means the ID of all records would change every time you added a new one. You would end up with a fairly large mess.

May I suggest that you read what has already been posted - you may learn something helpful - then, if you still feel you have something to contribute, post it. It should really be something that adds to or enhances the existing content of the thread if possible.

Don't be disheartened. The urge to contribute in some way is understandable and always appreciated. This particular one may just be a little beyond your current understanding at this time.

Welcome to Bytes :-)
Nov 16 '12 #16

P: 4
Sorry NeoPa I regret that I post a reply without going thru all the post
& in particular the 1st one. I just joined the forum & this is my 2nd attempt
to post a reply. I hope my this post will correct few things.
If I understand correctly, I have tried as follows.
1st in Access database created a Table- Table1 with fields 1) Data & 2) Data No default 0
2nd Created a Query-Query1 with fields Data & Data No, where criteria is 0 for Data No.
3rd Created a Form-Form1 based on Query1
4th Created a 2nd Query-Query2
Expand|Select|Wrap|Line Numbers
  1. SELECT IIf (DMax ("[Data No]","Table1") =0, 300, DMax ("[Data  No]","Table1") +1) AS Expr1
  2. FROM Table1;
5th In Form1 created a List Box, lookup value is from Query2 - Expr1 & stored that value in field
Data No of the Form1.
6th Created a simple Macro1 which 1st close Form1 & 2nd Open Form1
7th Created a Button in Form1 to run Macro1.
Got following results.
Expand|Select|Wrap|Line Numbers
  1. Data Data No  Data No List
  2.  
  3. A       0
  4. A       0       300
  5. A     300       300
  6.         0       301
  7. B       0       301
  8. B       301     301
  9.         0       302
Here 1st represent Form1 in 1st instant
Enter A in Data field & click Button to get
this status. Select 300 into Field Data No & click Button to get
Form ready for 2nd Entry which will be numbered as 301
Enter B
Select 301 in to Data No field & click Button &
Form1 ready to take 3rd Entry.
Previously I had solved somewhat similar situation by creating 2 tables.
Table1 contained all other data plus a field [Procedure Number]
2nd Table has only one field Number with initial value 0 & actually my last update query
Expand|Select|Wrap|Line Numbers
  1. IIf ([Table Name]. [Procedure Number] = 0, 300, [Table Name]. [Procedure Number]+1)
Updates this 2nd table’s field Number. List Box looks value from this table which in return is selected in to field Procedure Number, where criteria is 0 for [Procedure Number]
Knowing the solution I made a mistake of only quoting the update query & omitted the procedure to follow for desired result.
Nov 17 '12 #17

P: 4
Oops! I goofed again. I am attaching Table showing Data, Data No & Data No List.
At the end I made 1 more mistake. “Previously I had solved somewhat similar situation by creating 2 tables. My database’s Form did not have “Procedure Number “(in my case SrNo) as field. But my table’s field SrNo (default 0) is updated by “IIf ([Table Name]. [Number] = 0, 300, [Table Name]. [Number]+1)”, where criteria is 0 for Number.
Imott’s database’s Form has text box & "Procedure Tab", which when clicked show next no on
his form.
This can be also achieved using few more Macros, Queris & Form, but what I talked in my last post give him same result with lesser resources used.
Nov 17 '12 #18

NeoPa
Expert Mod 15k+
P: 31,494
Now I'm really confused. I have a great deal of difficulty trying to follow what you're saying. Don't feel it's your fault, but so much technical information explained in broken English is way too hard for me to piece together (trying to guess what you mean at various stages) without devoting a great deal of time to this - which I don't see as practicable in the circumstances. Again, let me repeat that this is not a criticism of you, or your post. I couldn't even do as well in a foreign language. It is just the situation I'm dealing with and assigning blame is irrelevant, so please don't feel I'm criticising you for the difficulty you have expressing technical details (always much harder than ordinary conversation) in a foreign language.

Can you just confirm that I understood you correctly to mean you still believe the UPDATE query is an appropriate way to approach this situation?

My understanding of that is that it is possible to do, but not without being more specific about which records are updated than was evident in your original post (which would clearly have updated every record in the table when only one was called for). Possible, though, is the best term you could use for it as it is an approach that is fundamentally clumsy and should never be recommended (for, among other reasons, the fact that it leaves the data in an incorrect state between saving and updating). It also involves extra objects in your database for a situation that should only need to involve the one original form.

The data posted is particularly confusing for me as the names refer to nothing I can guess. Unfortunate as the Field name is used in the SQL but there is no match in the data shown. If this is supposed to show the data after an update query has been applied to your data then either the data was different from that shown or the update query was. The query as indicated would certainly not update only the latest record added and ignore all the others (as appears to be what you're trying to say).
Nov 17 '12 #19

zmbd
Expert Mod 5K+
P: 5,397
@apca
I had to delete your last post. All of the pictures you attempted to link to referred to your C-drive and %userprofile% My antivirus and firewalls thru a fit!!!!!!

(#14:NeoPa)Details need to be posted within the post rather than in attached documents. That said, I appreciate the willingness to engage indicated by the statement ;-)
Apca, as NeoPa has indicated: Please try not to use so many pictures to show your work... if I had been at my work PC they would not only have been blocked but the entire thread more than likely would have tripped my I.T. security settings and blocked the entire site for the week!
You can describe your tables, forms, queries, and other information using text... search here on my user name for examples.

As for using an UPDATE query for what OP has asked about... Not the easiest method to implement. In-fact, this maybe the one time that VBA has the clear advantage. It'll be very interesting to see what you've come up with when you repost.

:)
@lmott
Do you need a lot number system for example:
Day 1:
(lotid_1)001
(lotid_1)002
(lotid_1)003
(lotid_1)004

Day 2:
(lotid_2)001
(lotid_2)002
(lotid_2)003
(lotid_2)004

Where in "lotid_" could be almost anything for example I have a nice fancy one that we can look at and tell what production line, which product, and which day etc...

The 001 could start anywhere you wanted... in fact, it could just keep going as a serial for the month instead of restarting each day or restart at the end of the year etc....

Second... PLEASE repost your current code. I may be that you've just misplaced the DMAX() in the code.
Nov 19 '12 #20

P: 4
In Access database,
Table1 has fields
1) Date
2) Project Name
3) Project Number
4) Created By
5) Procedure Description
6) Procedure Number (default = 0)

Query1 –
SELECT Table1.Date, Table1. [Project Name], Table1. [Project Number], Table1. [Created By],
Table1. [Procedure Description], Table1. [Procedure Number] FROM Table1
WHERE (((Table1. [Procedure Number])=0));

Query2 –
SELECT Table1.Date, Table1. [Project Name], Table1. [Project Number], Table1. [Created By], Table1. [Procedure Description], Table1. [Procedure Number]
FROM Table1
WHERE (((Table1. [Procedure Number])=DMax ("[Procedure Number]","Table1")));

Query3 –
UPDATE Table1 SET Table1. [Procedure Number] = IIf (DMax ("[Procedure Number]","Table1") =0, 300, DMax ("[Procedure Number]","Table1") +1)
WHERE (((Table1. [Procedure Number])=0));

Form1 – Data Source Query1
Date
Project Name
Project Number
Created By
Procedure Description
Click Procedure Number Tab
will run Macro1 – Close Form1, Run update Query3, Open Form2


Form2 – Data Source Query2
Date
Project Name
Project Number
Created By
Procedure Description
Procedure Number


Click OK Tab
Will run Macro2 – Close Form2, Open Form1


Form1 – Data Source Query1
Date
Project Name
Project Number
Created By
Procedure Description


Form1 – Data Source Query1
Date
Project Name
Project Number
Created By
Procedure Description


Form2 – Data Source Query2
Date
Project Name
Project Number
Created By
Procedure Description
Procedure Number
Nov 19 '12 #21

NeoPa
Expert Mod 15k+
P: 31,494
As you seem to be paying very little attention to anything I've posted to date (There was a request for information in my last post which you've ignored.) and seem to be simply repeating your original suggestion over and over again in spite of the obvious drawbacks already explained with such an approach, let me just state for the benefit of any who are reading this thread that my view, and I would expect that of any experienced Access programmer, is that what is being suggested in your posts is a very poor approach that will lead to problems for anyone that chooses to follow it.

I cannot hope to change your mind (as you clearly pay no attention to what I post), but I can at least do my best to make it clear for anyone else that using such an approach is at their own risk.

For anyone interested in the reasoning behind this view please see post #16.
Nov 19 '12 #22

P: 8
Good evening, I do apo;ogize for taking so long in replying as stated before I am deeply tied up in a off site project and have a very limited time for another week or so untill the project is sent offshore. I read through the post above and took me a min to get up to speed and lost with post from apca.

ZMBD: I have deleted the orginal code I was using and then attempted to use DMax as suggested by NeoPa which seems to be the smarter and less resource hungry direction to proceed. I could not figure out how to write the code uing DMax as I have never seen or used it before.

I am happy to provide whatever information is required as this is a very good learning oppertunity for me and when I have the chance at the moment to work on it have enjoyed it much because I am doing things I have not had to do before in Access.

Once again I do appologize for taking so long to reply to the post as I still have 1-2 more weeks in this project before I can go back to devoting more time to replies and learning from you all.

Lloyd
Nov 28 '12 #23

zmbd
Expert Mod 5K+
P: 5,397
lmott:
Glad to have you back!

I can certainly understand being "tied-up" so to speak with other job requirements. I've been dealing with a series of instrumentation failures, P.M.'s, calibrations, and audits along with the usual stuff in the labs. I truely enjoy my job and yet even something one likes to do can become tiring.

For now lets set aside APCA's suggestions. The number of queries, cross queries, tables and the like are well...

Instead, please re-read #14 by NeoPa and the bottom half of my post in #20 and we'll go from there with your answers to those posts.
Nov 28 '12 #24

NeoPa
Expert Mod 15k+
P: 31,494
No worries on the delays Lloyd. This medium is such that timing is very rarely guaranteed anyway, so we just respond to what is in front of us. As and when.

As for the next steps, I couldn't do better that what Z has already, and draw your attention to those posts where these are laid out for you.
Nov 28 '12 #25

P: 8
Well I had time to play around with the DMax command and have it working nicely now what I need to do is figure out how to get it to start out at a specific number instead of 1. Thank you for the help This is a nice learning project.

I hope you all had a Merry Christmas and have a Happy New Year
Dec 26 '12 #26

NeoPa
Expert Mod 15k+
P: 31,494
Having looked back through the thread I see nothing obvious that provides an answer for you on that. Since it was a part of the original question that is certainly a drawback.

Assigning the default is normally done in conjunction with the DMax() call. If the call finds no matching records then it will return Null. Using Nz() allows us to specify a default to be used in case no records matching are found. Something like the following code (where much is left out due to having been covered already) :
Expand|Select|Wrap|Line Numbers
  1. X = Nz(DMax(...), 10599) + 1
This will set X to the next available value unless none exist yet, in which case it will use 10600.
Dec 27 '12 #27

P: 8
aaahhh I see I was trying to put the Nz value in front of DMax like so

Expand|Select|Wrap|Line Numbers
  1. X + Nz 400(DMax(.....)) +1
I understand now why it did not work because I had it in the wrong locatio. Iwill give what you pointed out in your statement a go.

Thank you and I will let you know how it goes.

Lloyd
Dec 27 '12 #28

TheSmileyCoder
Expert Mod 100+
P: 2,321
In the spirit of saving both you and us time, I suggest you in the future take the time to look at the built in help for the functions you use. In the VBE editor highlight (Select) for example the NZ and hit F1 to open context sensitive help.
Dec 27 '12 #29

NeoPa
Expert Mod 15k+
P: 31,494
That is such a useful point Smiley made about Context-Sensitive Help. So many people could benefit enormously, simply by appreciating how much help is available at their fingertips. If you pick up only one idea from this whole thread, then that would certainly be the one I'd recommend ;-)
Dec 27 '12 #30

Post your reply

Sign in to post your reply or Sign up for a free account.