Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

Autonumber form selection

Posted on 2014-09-08
6
Medium Priority
?
89 Views
Last Modified: 2016-06-10
I have a table that is autonumbered and want to reautonumber a selection from a drop down box on a form.  This is all the information I have.  It is part of an interview I am going to.  What questions should I ask and can it be done?
0
Comment
Question by:leezac
5 Comments
 
LVL 52

Accepted Solution

by:
Gustav Brock earned 672 total points
ID: 40311447
You should ask if it is about renumbering an autonumber field of the source table (bad idea but can be done with SQL for most database engines), or adding a "rownumber" to the form. This is effectively a counter that sets a 1 for the first record displayed, then add 1 for each following record. What further has to be done depends on the purpose of that number - could for example be just for display or for storing a selection.

/gustav
0
 
LVL 85

Assisted Solution

by:Scott McDaniel (Microsoft Access MVP - EE MVE )
Scott McDaniel (Microsoft Access MVP - EE MVE ) earned 664 total points
ID: 40311892
Perhaps also ask the purpose of the Autonumber field.

If it's only there as a surrogate index/key, then re-autonumbering would be pointless (instead you'd modify the actual Unique Values for the record if you needed to change the data that makes that record unique).

If it's not a surrogate key, then you'd have to ask - why is it the only unique value in that record. Autonumber fields should never have any "value" to the data stored in that record, and should only be used as a pointer. They are meaningless numbers intended to make relating tables a bit easier.
0
 
LVL 40

Assisted Solution

by:PatHartman
PatHartman earned 664 total points
ID: 40312250
Sounds like a trick question to me.  As others have already mentioned, it doesn't make any sense to change an autonumber.  Its sole purpose is to provide a unique identifier for a row.  Also, if the table has "child" tables, changing the value of the primary key would break those relationships.

Given that, you occasionally want a sequence number that the user controls.  This sequence number can have a unique index so that duplicates are not allowed.  Typically, new records are generated using:

Me.SeqNum = Nz(DMax("SeqNum", "SomeTable"), 0) +10

If they are sequence numbers within a group, just add the group criteria to the DMax()

Notice the increment by 10.  You could make it a hundred if you prefer.  The point is to create gaps.  That way, the user can renumber or add rows in between existing rows.  To renumber the sequence number.  Find out how many rows in the set using DCount().  Then create a query that sorts the rows in descending order by SeqNum.  Write a code loop to read each row and assign the SeqNum starting with the Count * increment and subtracting 1* increment for each subsequent row.

The reason for assigning the new values from back to front is because you don't want to run into a duplicate SeqNum while you are reassigning numbers.

Of course, if the user can delete rows, you could still run into a duplicate issue regardless of which direction you worked from.  That becomes more complicated and I won't go into it.  The point for your interview is recognizing that there could be an issue.
0
 
LVL 85
ID: 40312507
Given that, you occasionally want a sequence number that the user controls.
Yes, and if the AutoNumber is used as a "sequence" value, then it's again misapplied, since an Access AutoNumber is intended to be unique, but not necessarily sequential (although it normally is).

I'm aware that you (Pat) know this, but was pointing this out for the benefit of the user.
0
 

Author Comment

by:leezac
ID: 40313279
Thanks- I meet with the user tomorrow.  This will help.
0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

The Windows Phone Theme Colours is a tight, powerful, and well balanced palette. This tiny Access application makes it a snap to select and pick a value. And it doubles as an intro to implementing WithEvents, one of Access' hidden gems.
Microsoft Access has a limit of 255 columns in a single table; SQL Server allows tables with over 255 columns, but reading that data is not necessarily simple.  The final solution for this task involved creating a custom text parser and then reading…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …
Suggested Courses

581 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question