AutoNumber in a Query - Microsoft Access Forum


	Back to Access Forum Comments List

Upload Images @Reply Bookmark Link Email Next Unseen

AutoNumber in a Query

Christopher Moll

4 years ago

Years ago (1990's) I had a way to create an autonumber in a calculated field in a query.
It involved using a stand-alone "seed" table of numbers and no relationships.

This table was added to the query with no relationships creating a type of cartesian query.

Then the calculated field in the query somehow used the numbers in the "Seed" table to create an autonumbered field in the query.

It worked slick and I for the life of me can not remember how I did it.

Does anyone here know how to do this?

Thank you for any help.

	Richard Rost @Reply 4 years ago
I'm not familiar with that exact setup, but it sounds like it would involve a DMax kinda like I do in Sequential Numbers.

	Scott Axton @Reply 4 years ago
Take a look at Customer Codes Also Custom Sequential Numbers One of those two should get you where you want to go. If not let us know.

	Christopher Moll OP @Reply 4 years ago
Thank you for the feedback Richard and Scott. I will look at the resources closer. Dmax might be the answer , Ill look at it again. Thank you!

Christopher Moll OP @Reply

4 years ago

There is an error when I try to use DMax . The form is a client and the tables are on a backend database. (split access database)
Here is the code:

Private Sub Form_BeforeInsert(Cancel As Integer)

TagNumber = DMax("TagNumber", "MasterMetricTapList") + 1

End Sub

It is pointing to a linked table.... is that the problem? maybe I need to refrence the table in a another way? since it is in the backend data base?

I'll try to attach screen shots for clarity.

any help is appreciated.

	Christopher Moll OP @Reply 4 years ago

	Christopher Moll OP @Reply 4 years ago

	Christopher Moll OP @Reply 4 years ago

Christopher Moll OP @Reply

4 years ago

ok I solved the problem.... but I don't know what caused it. I had made the form with the built-in wizard. This text box was corrupt in some way. I deleted the text box from the form, then manually added a generic one and bound it it to the proper field in the table once again. All of a sudden it started working as expected. The indicator I stumbled on was in the VBA window when the field showed up with a "?" preceding the name as the image shows. Strange.

	Richard Rost @Reply 4 years ago
Ah. Love the wizards. NOT!

This thread is now CLOSED. If you wish to comment, start a NEW discussion in Access Forum.

Next Unseen

New Feature: Comment Live View