Ontario has the oldest exit number system, having started posting exit numbers sequentially in the 1960s along Highway 401; it switched to mile-based numbering before Canada went metric. Most short freeways do not have exit numbers, and until about 2000 (with Highways 11 and 69/400), incomplete freeways also did not have exit numbers. Interchanges with multiple exits are lettered A-B.
The blog is pretty much step by step. Where it may not be that specific is, because, these are decisions the developer needs to make. For example, where to put the DMax expression is a matter of your workflow so I can’t tell you where to put it. I’ve given tips in the blog to help you decide. If you are having issues, then please give me more info about your application and I can suggest things.
Anyway back to how it should work Isert any new piles that you need and number them so that the new piles fall in where you want them using decimals. Extract the number values from your drawing and reverse sort them. Use the format above and make a scr file, even doing it by hand in notepad copy and pasting shouldn't take more than 10 min for 350 piles (with excel and a bit of programming less than a minute). And if you need to remove piles then same thing but numbers run low to high. You will no doubt need to do a bit of work to get it to work with your attributes, but it is doable. If you want then post a sample of your blocks and I can probably give you a better idea.
Now, what’s the deal with the UniqueKeyVariant parameter for the RowNumber() function? We are not even using it all! True, but if we didn’t, Access will call RowNumber() only once for an entire query and thus we get “1” for the entire dataset. That is a good way to save on CPU processing – why call Now() for each row? In this case, we want to go against this CPU-saving measure and ensure that the RowNumber() is in fact called once per row to generate a new number for that row. Thus, we pass in a parameter — a primary key of a table is a good choice for this purpose. So we get a nice sequential number generated all in the query with VBA function.
To recap, you use a DMax function to return the highest number in the Sequence and increment it by 1. You assign the incremented number to a control on your form and then immediately save the record. If your identifier includes additional information from the record, you use an expression that concatenates that information with the sequence number to display the full identifier.
By omitting the dbFailOnError parameter only for the DROP TABLE statement, we won’t get a runtime error about a table not existing but get runtime errors if we can’t create the table or insert data for some reasons. I’m also not a big fan of creating multiple saved queries that are meant to be logically grouped together – it gets quite cluttered when there are several saved queries in a navigation pane like that. Keeping it all in VBA makes it clearer that the intention is to execute those statements together.
As more highways were built, states and countries began to experiment with distance-based (mile-based or kilometer-based) exit numbers. The first mile-based system known was implemented on the Garden State Parkway in New Jersey in the late 1950s. Michigan also implemented mile-based junction numbers on Interstate 94 in the 1960s. In this system, the number of miles from the beginning of the highway to the exit is used for the exit number. If two exits would end up with the same number, the numbers are sometimes modified slightly; this is often impossible and exits are given sequential or directional suffixes, just as with sequential numbers.
With this expression we check only for records that are in the same year as the Inquiry date to increment the sequence. Again, the NZ function will automatically restart the sequence with each year. Cleary, though, we cannot generate this number until the InquiryDate has been entered. Is this value is a user entry, then you may want to check that it’s filled in before generating the number. Something like:
The best way to do this is to use a primary key. In cases where a query is complex and joins several tables, we may need to use the primary key of the table that’s on the bottom of the relationship hierarchy or possible a concatenation of multiple tables’ unique key so that the expression will be unique and thus we can identify which sequential number we have assigned.
Scott, trying my best to have sequential numbering almost identical to your scenario 1 (bound control on input form which increments last number by 1). Where do I enter this code? I have a form which opens and is ready for entry of new Project Designs. I want to show in the control box the next Project ID (a number) so do I place the code, Me.txtProject ID = Nz(DMax(“[Project ID]”,”DESIGN PROJECTS”),0)+1, under On Load, After Update, or where? And also, the Me.Dirty = False or DoCmd.RunCommand
If you use the Form Wizard, controls will be named with the field name the control is bound to. But that name can be changed. This trips up a lot of people because my code samples use a naming convention that is not what is automatically generated. So you just need to make sure you use the correct name for the object. The name is shown in the Name property on the Other tab (Not the Caption property). To determine what field in your table the control is bound to check the ControlSource property. It should be bound to the PONum field.
Scott, you’ll need to be more specific to help me. When you say “put this code behind a save button” what exactly does that mean – where do I type the code you provided? Yes, I do have a Save Button, which saves the record and closes the form (but currently has no way to save the next sequential Project ID). I want show this next Project ID (number on the entry form) and have that new number flow to the table along with the other data on the form.

As more highways were built, states and countries began to experiment with distance-based (mile-based or kilometer-based) exit numbers. The first mile-based system known was implemented on the Garden State Parkway in New Jersey in the late 1950s. Michigan also implemented mile-based junction numbers on Interstate 94 in the 1960s. In this system, the number of miles from the beginning of the highway to the exit is used for the exit number. If two exits would end up with the same number, the numbers are sometimes modified slightly; this is often impossible and exits are given sequential or directional suffixes, just as with sequential numbers.
Let’s look at why we have this setup. It seems strange to put a ResetRowNumber() call in a WHERE clause, doesn’t it? However, the WHERE clause is actually resolved prior to the SELECT clause. (For those who wants to geek out on SQL internals, Itzik Ben-Gan, a SQL Server MVP has a great post that outlines the logical query processing. Though this is specific to SQL Server, the Access database engine as well the majority of RBMS engines generally follow the same outline). This gives us a convenient point to ensure that the module level variable lngRowNumber is always correctly reset at the right time (e.g. before we start returning records from a query).

I'm now trying to include page numbers on every single page.  Some of the sections are "linked" to previous sections, while others are not.   What I've found when these that aren't linked are linked to the page numbers, I'm getting weird numbers popping up.  For example, when linking section 3 to the first 2 sections, my page numbers went from 4 to 12.  

Thanks for your help @steven-g. I will definitely look through it. For now I have made @Charles_Shade's solution work but for some reason the tag rotates to 214 degrees and text height is .2. Also I set the starting number as 001 but the next numbers are coming up as 2,3... instead of 002,003 which is not the biggest problem though. If I could fix these like have the rotation to 0 degrees and play with text height that would be life saving. Thanks :)

Ontario has the oldest exit number system, having started posting exit numbers sequentially in the 1960s along Highway 401; it switched to mile-based numbering before Canada went metric. Most short freeways do not have exit numbers, and until about 2000 (with Highways 11 and 69/400), incomplete freeways also did not have exit numbers. Interchanges with multiple exits are lettered A-B.


Some sequential exits are renumbered (remaining sequential) due to added exits. For instance, the Hutchinson River Parkway in New York was renumbered so that its northernmost exit, 27, became 30. However, the Merritt Parkway, which continued the Hutchinson's exit numbers in Connecticut, was not renumbered. This means the Route 120A interchange is numbered 27 in Connecticut and 30 in New York.
Both the Collection Point ID and Artifact ID fields are bound properly and display those exact names in the property sheet under both control source and name. On the save button I have on the form, when I click on the event tab and the on click option I have event procedure and I click the […] option to open up the code builder and this is what I currently have:
Ok, generating a random 3 digit number is a whole different thing, so I’m not going to go into that. If you want your numbers to start at 100 (to insure three digits) then change the 0 to 99. The Nz function will return the value listed if the field is Null. So the first time you execute that code, the DMax should return a Null since no numbers have been generated for the PONum field. The Nz will then substitute 99 and then increment that by 1. You can accomplish something similar by just entering 100 as the PONum for one record. .
This site contains user submitted content, comments and opinions and is for informational purposes only. Apple may provide or recommend responses as a possible solution based on the information provided; every potential issue may involve several factors not detailed in the conversations captured in an electronic forum and Apple can therefore provide no guarantee as to the efficacy of any proposed solutions on the community forums. Apple disclaims any and all liability for the acts, omissions and conduct of any third parties in connection with or related to your use of the site. All postings and use of the content on this site are subject to the Apple Support Communities Terms of Use.
Anyway back to how it should work Isert any new piles that you need and number them so that the new piles fall in where you want them using decimals. Extract the number values from your drawing and reverse sort them. Use the format above and make a scr file, even doing it by hand in notepad copy and pasting shouldn't take more than 10 min for 350 piles (with excel and a bit of programming less than a minute). And if you need to remove piles then same thing but numbers run low to high. You will no doubt need to do a bit of work to get it to work with your attributes, but it is doable. If you want then post a sample of your blocks and I can probably give you a better idea.
×