Solved

Autonumber form selection

Posted on 2014-09-08
6
40 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
6 Comments
 
LVL 49

Accepted Solution

by:
Gustav Brock earned 168 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 84

Assisted Solution

by:Scott McDaniel (Microsoft Access MVP - EE MVE )
Scott McDaniel (Microsoft Access MVP - EE MVE ) earned 166 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 34

Assisted Solution

by:PatHartman
PatHartman earned 166 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 84
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

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

In the previous article, Using a Critera Form to Filter Records (http://www.experts-exchange.com/A_6069.html), the form was basically a data container storing user input, which queries and other database objects could read. The form had to remain op…
I see at least one EE question a week that pertains to using temporary tables in MS Access.  But surprisingly, I was unable to find a single article devoted solely to this topic. I don’t intend to describe all of the uses of temporary tables in t…
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…

708 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

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now