Solved

Autonumber form selection

Posted on 2014-09-08
6
67 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 50

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 85

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 37

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 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

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Question has a verified solution.

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

Access custom database properties are useful for storing miscellaneous bits of information in a format that persists through database closing and reopening.  This article shows how to create and use them.
You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…
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 …

726 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