Bill
You should never build a relationship on anything but the primary key.
And also know that you can have only one AutoNumber per table. If you choose to use a Long instead you will have to provide a snippet of code to get its value using DMax.
Private Sub Form_BeforeInsert()
txtMyRecNumber = Nz(DMax("MyRecNumber", "MyTable"),0) +1
End Sub
The above will check the table for the highest MyRecNumber and add 1 to it for the new record.
Bill
--- In MS_Access_Professionals@yahoogroups.com, "Bill Singer" <Bill.Singer@...> wrote:
>
> Bill,
>
> Lesson learned on using the auto number as a meaningful value. I am now
> thinking it may be easier to make that change in this data base now.
>
>
>
> I may change my auto-number to number, with no duplicates, and keep it as my
> field that the relationships are built on. It will still function fine.
> Then if I put in a new Primary key it may not matter if it is the same as it
> will not be meaningful.
>
>
>
> I will bring all this home tonight and work on it.
>
>
>
> Thanks,
>
> Bill
>
>
>
>
>
> Bill Singer
>
>
>
> A.T.Group
>
> 2011 & 2012 Five Star
>
> Benefit Professional
>
> 877-902-8898
>
> <mailto:Bill.Singer@...> Bill.Singer@...
>
> cid:image006.gif@...
>
>
>
> From: MS_Access_Professionals@yahoogroups.com
> [mailto:MS_Access_Professionals@yahoogroups.com] On Behalf Of Bill Mosca
> Sent: Friday, April 06, 2012 11:13 AM
> To: MS_Access_Professionals@yahoogroups.com
> Subject: Re: [MS_AccessPros] RE: New primary key
>
>
>
>
>
> Crystal, Bill
>
> If the AutoNumber field is used as a foreign key in any other table you
> could turn your database into cottage cheese. At the very least, there would
> be a lot of clean up to do.
>
> I'd :
> 1. change the AutoNumber to a Long
> 2. change all relationships to cascading updates
> 3. Fix the numbers in the parent table which will cascade to the child
> tables
> 4. Append all fields in the parent records to a new table with an auto
> number.
> 5. Drop the relationships on the old table and delete it
> 6. Rename the new table and set up relationships again.
>
> The lesson here is DO NOT USE AN AUTONUMBER AS A MEANINGFUL VALUE. And never
> expose it to the users. Using another field with a Long data type would be
> much smarter.
>
> Regards,
> Bill Mosca, Founder - MS_Access_Professionals
> http://www.thatlldoit.com
> Microsoft Office Access MVP
> https://mvp.support.microsoft.com/profile/Bill.Mosca
>
> --- In MS_Access_Professionals@yahoogroups.com
> <mailto:MS_Access_Professionals%40yahoogroups.com> , Crystal
> <strive4peace2008@> wrote:
> >
> > Hi Bill,
> >
> > you can add records to a table with defined values for AutoNumber using an
> Append Query.
> >
> > If you want AutoNumbers back:
> >
> > 1. break relationships on current table.
> >
> > 2. make a copy of the table with no records.
> > 3. change field to AutoNumber
> > 4. set relationships
> >
> > 5. append records
> >
> >
> >
> > Warm Regards,
> > Crystal
> >
> > *
> > (: have an awesome day :)
> > *
> >
> >
> >
> > ________________________________
> > From: Bill Singer <Bill.Singer@>
> > To: MS_Access_Professionals@yahoogroups.com
> <mailto:MS_Access_Professionals%40yahoogroups.com>
> > Sent: Friday, April 6, 2012 9:38 AM
> > Subject: [MS_AccessPros] RE: New primary key
> >
> > I just want to make sure this is possible.
> >
> >
> >
> > I have table with a primary key, about 300 records. The number of the
> > Primary Key was important as the data was transferred from an excel
> > spreadsheet and the primary key relates to other storage records. After
> > the table was finished a few of the records were deleted. Now the user
> > wants those Primary Key numbers back.
> >
> >
> >
> > I know I cannot just add those numbers in because they are automatically
> > generated. I have changed the Auto-Number to Number, add in the existing
> > records. All of that works but I cannot change the field back to
> > Auto-Number, which I understand.
> >
> >
> >
> > So, I keep the table sorted by the old primary key number with the missing
> > records added, I add a new primary key auto-number field and let it
> > re-sequence and for some reason it does not sequence in the correct order.
> >
> >
> >
> > Is there a way to get the new primary key auto-number to sequence in the
> > same way that the old primary key was sequences? The auto number value is
> > what the rest of the relationships are built on and they will all be wrong
> > if the sequence is not the same.
> >
> >
> >
> > Thanks for your help.
> >
> >
> >
> > Bill
> >
> > MN
> >
> >
> >
> > [Non-text portions of this message have been removed]
> >
> >
> >
> > ------------------------------------
> >
> > Yahoo! Groups Links
> >
> >
> >
> > [Non-text portions of this message have been removed]
> >
>
>
>
> _____
>
> No virus found in this message.
> Checked by AVG - www.avg.com
> Version: 2012.0.1913 / Virus Database: 2409/4918 - Release Date: 04/06/12
>
>
>
> [Non-text portions of this message have been removed]
>
Tidak ada komentar:
Posting Komentar