ACCESS FEED: Re: [MS_AccessPros] Autonumber or not?

Selasa, 27 November 2012

Re: [MS_AccessPros] Autonumber or not?

Adam-

Oh, I'm sure you will hear from them.

On second thought, having a separate type code is a good idea. You need
the TypeCode to be able to look up the "next" number. You can force the
user to enter that on a new record, then use that to do your DMax.

Private Sub Form_BeforeUpdate(Cancel As Integer)
Dim lngNextCode As Long

If IsNull(Me.TypeCode) Then
MsgBox "You must enter a Type Code."
Cancel = True
Exit Sub
End If

If Me.NewRecord Then
lngNextCode = NZ(DMax("SeqNo", "MyTable", "TypeCode = '" &
Me.TypeCode & "'"), 0) + 1
Me.SeqNo = lngNextCode
End If

End Sub

John Viescas, Author
Microsoft Access 2010 Inside Out
Microsoft Access 2007 Inside Out
Microsoft Access 2003 Inside Out
Building Microsoft Access Applications
SQL Queries for Mere Mortals
http://www.viescas.com/
(Paris, France)

-----Original Message-----
From: AdamF <runuphillracing@yahoo.com>
Reply-To: <MS_Access_Professionals@yahoogroups.com>
Date: Tuesday, November 27, 2012 8:41 PM
To: <MS_Access_Professionals@yahoogroups.com>
Subject: Re: [MS_AccessPros] Autonumber or not?

I'd like to hear what the non-exceptions say about this.

John,

The reason to use two fields is that it's then easier to write the sql
without "left" and "right" functions. Lazy?

Adam

--- In MS_Access_Professionals@yahoogroups.com, John Viescas <JohnV@...>
wrote:
>
> Adam-
>
> Well, I'm an exception to the "rule" about using AutoNumber. If you have
> a "natural" Primary Key, then use it! But why have two fields? You can
> have one Text field that does the trick. It's not hard to strip off the
> number part to add 1. I would also force leading zeroes in the number
> part so that they sort correctly. E.G. W000019
>
> Use Format(lngNewNumber, "000000") to get the leading zeroes you need.
>
> John Viescas, Author
> Microsoft Access 2010 Inside Out
> Microsoft Access 2007 Inside Out
> Microsoft Access 2003 Inside Out
> Building Microsoft Access Applications
> SQL Queries for Mere Mortals
> http://www.viescas.com/
> (Paris, France)
>
>
>
> -----Original Message-----
> From: AdamF <runuphillracing@...>
> Reply-To: <MS_Access_Professionals@yahoogroups.com>
> Date: Tuesday, November 27, 2012 8:08 PM
> To: <MS_Access_Professionals@yahoogroups.com>
> Subject: [MS_AccessPros] Autonumber or not?
>
> I know you (the experts) usually prefer using an autonumber to a custom
> ID. However ....
>
> This is for a jewelry company. They identify each inventory item by a one
> letter indicating type (e.g., W=watch, R=ring), and a number. The numbers
> increment for each type of item (e.g., W1, W2, W3, ...; R1, R2, R3, ...).
> Thus, the primary keys are a combination of two fields:
> ItemType - 1 char text
> InvNo - long integer. I will be* adding these using dMax + 1.
>
> *I say "will be" because the database I took over had the different types
> of items in separate tables (with an autonumber). I initially brought
>them
> together via union queries (didn't have the time budgeted to do a full
> redesign until now).
>
> So, do I have a good reason to drop the autonumber?
>
> Adam
> Denver, CO
>
>
>
> ------------------------------------
>
> Yahoo! Groups Links
>

------------------------------------

Yahoo! Groups Links