?
Solved

Autonumber form selection

Posted on 2014-09-08
6
Medium Priority
?
73 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
6 Comments
 
LVL 51

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 38

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

Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

Question has a verified solution.

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

Microsoft Access is a place to store data within tables and represent this stored data using multiple database objects such as in form of macros, forms, reports, etc. After a MS Access database is created there is need to improve the performance and…
This article shows how to get a list of available printers for display in a drop-down list, and then to use the selected printer to print an Access report or a Word document filled with Access data, using different syntax as needed for working with …
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.
Suggested Courses

765 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