Solved

Autonumber form selection

Posted on 2014-09-08
6
59 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 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 36

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

Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

Question has a verified solution.

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

In earlier versions of Windows (XP and before), you could drag a database to the taskbar, where it would appear as a taskbar icon to open that database.  This article shows how to recreate this functionality in Windows 7 through 10.
This article describes a method of delivering Word templates for use in merging Access data to Word documents, that requires no computer knowledge on the part of the recipient -- the templates are saved in table fields, and are extracted and install…
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

696 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