A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Access » Database Design
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Assigning numbers



 
 
Thread Tools Display Modes
  #1  
Old April 28th, 2004, 07:01 PM
Fred
external usenet poster
 
Posts: n/a
Default Assigning numbers

Greetings,
I have a project where I have to assign a three-part ID
number to each new entry. The first two parts to
the "number" are typed in by the user. Access is to assign
the numeric part (e.g. 1,2,3...)
e.g. Fred - Ford - 1
Fred - Ford - 2
Fred - Toyota - 1
Freda - Ford - 1
etc.

Has anyone been able to do this?
Please help!

  #2  
Old April 28th, 2004, 07:56 PM
Anne Troy
external usenet poster
 
Posts: n/a
Default Assigning numbers

Hi, Fred. Use an autonumber field in the table. For the part number, use an
expression like =([name]&"-"&[model]&"-"&[autoIDnumber]

-*--*--*--*--*--*--*--*-
Hope this helps!
Anne Troy (better known as Dreamboat)
Author: Dreamboat on Word
Email: Com.Piersontech@Dreamboat
(Reverse it!)
Web: www.TheOfficeExperts.com
-*--*--*--*--*--*--*--*-
"Fred" wrote in message
...
Greetings,
I have a project where I have to assign a three-part ID
number to each new entry. The first two parts to
the "number" are typed in by the user. Access is to assign
the numeric part (e.g. 1,2,3...)
e.g. Fred - Ford - 1
Fred - Ford - 2
Fred - Toyota - 1
Freda - Ford - 1
etc.

Has anyone been able to do this?
Please help!



  #3  
Old April 29th, 2004, 03:55 AM
external usenet poster
 
Posts: n/a
Default Assigning numbers

Hi,
Does this reset the "number" part back to the lowest in
the combination?



-----Original Message-----
Hi, Fred. Use an autonumber field in the table. For the

part number, use an
expression like =([name]&"-"&[model]&"-"&[autoIDnumber]

-*--*--*--*--*--*--*--*-
Hope this helps!
Anne Troy (better known as Dreamboat)
Author: Dreamboat on Word
Email: Com.Piersontech@Dreamboat
(Reverse it!)
Web: www.TheOfficeExperts.com
-*--*--*--*--*--*--*--*-
"Fred" wrote in

message
...
Greetings,
I have a project where I have to assign a three-part ID
number to each new entry. The first two parts to
the "number" are typed in by the user. Access is to

assign
the numeric part (e.g. 1,2,3...)
e.g. Fred - Ford - 1
Fred - Ford - 2
Fred - Toyota - 1
Freda - Ford - 1
etc.

Has anyone been able to do this?
Please help!



.

  #4  
Old April 29th, 2004, 06:30 AM
John Vinson
external usenet poster
 
Posts: n/a
Default Assigning numbers

On Wed, 28 Apr 2004 11:01:27 -0700, "Fred"
wrote:

Greetings,
I have a project where I have to assign a three-part ID
number to each new entry. The first two parts to
the "number" are typed in by the user. Access is to assign
the numeric part (e.g. 1,2,3...)
e.g. Fred - Ford - 1
Fred - Ford - 2
Fred - Toyota - 1
Freda - Ford - 1
etc.

Has anyone been able to do this?
Please help!


I'd suggest that you keep this ID in three fields, not one; note that
a Primary Key can consist of up to TEN fields. Concatenating it into
one is neither necessary nor desirable.

You can do your data entry with a Form (tables don't have any usable
events for the purpose). Let's say you have fields Who, What, and Num
as above, with controls txtWho, txtWhat, and txtNum; you could put
code in the AfterUpdate event of What like this:

Private Sub txtWhat_AfterUpdate()
If IsNull(Me!txtWho) Then
MsgBox "Fill in a name please!", vbOKOnly
Me!txtWho.SetFocus
End If
If IsNull(Me!txtWhat) Then
MsgBox "Fill in a car please!", vbOKOnly
End If
Me!txtNum = NZ(DMax("Num", "yourtable", "[Who] = '" _
& Me!txtWho & "' AND [What] = '" & Me!txtWhat & "'")) + 1
End Sub

An Autonumber will not really be suitable since it's one series (and
may have gaps at that).

John W. Vinson[MVP]
Come for live chats every Tuesday and Thursday
http://go.compuserve.com/msdevapps?loc=us&access=public
 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 10:44 AM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.