Assigning numbers

[Fred]

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!

 

[Anne Troy]

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

 

[Guest]

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

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!

.

 

[John Vinson]

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