Sequencial numbering

Hi there,

Bit of an Access/Excel newbie here in need of some help. First time in here
so please be gentle with me.

Here's what I'm trying to achieve. I want to build a database which records
the changes on a project with each project having it's own unique code based
on the year so for example:

05666
04555
03444

I've build the database itself and created an Excel front end form to input
the data to the database. What I would like to achieve is for each project
number to have it's own sequencial record number for example:

05666-1
05666-2
04555-1
04555-2
04555-3
03444-1
03444-2

you get the idea. How do I do this? It's really stumped me. I guess I really
need to know what VBA to put into the Excel form to look at teh last number
allocated to a project and just add one. If this can be done automagically on
selecting the required project then all the better.

I can get Access to allocate a sequential number to each new entry but not
to each new entry for an individual project. Does the make sense?

Your thoughts and help would be greatly appreciated.

Just let me nkow if you need any more information.

Rich

Hi Richard
This might point you in the right direction
The core of the solution I used recently doing a similar thing was Instr

Set tbl = Range("A1:A10")
For each c in tbl
'Get number of items in list and first and last row number
If Left(c, InStr(c, "-") - 1) = Left(orderID, InStr(orderID, "-") - 1) Then
sameidcntr = sameidcntr + 1
If sameidcntr = 1 Then firstsameid = c.Row
lastsameid = c.Row
End If

firstsameid to lastsameid is the range of the outer index you are seeking.
Then do:

Set tbl = Cells(lastsameid + rwcntr, Range("A1:A10").Column)
'Now add 1 to last inner index
newID = Left(tbl, InStr(tbl, "-") - 1) & "-" & Right(tbl, Len(tbl) -
InStr(tbl, "-")) + 1
neworderrow = lastsameid + 1 + rwcntr
rwcntr = rwcntr + 1

I was inserting a new line for each new id generated but this gives you the
general idea.

HTH

Geoff


"Richard Setford" wrote:

> Hi there,
>
> Bit of an Access/Excel newbie here in need of some help. First time in here
> so please be gentle with me.
>
> Here's what I'm trying to achieve. I want to build a database which records
> the changes on a project with each project having it's own unique code based
> on the year so for example:
>
> 05666
> 04555
> 03444
>
> I've build the database itself and created an Excel front end form to input
> the data to the database. What I would like to achieve is for each project
> number to have it's own sequencial record number for example:
>
> 05666-1
> 05666-2
> 04555-1
> 04555-2
> 04555-3
> 03444-1
> 03444-2
>
> you get the idea. How do I do this? It's really stumped me. I guess I really
> need to know what VBA to put into the Excel form to look at teh last number
> allocated to a project and just add one. If this can be done automagically on
> selecting the required project then all the better.
>
> I can get Access to allocate a sequential number to each new entry but not
> to each new entry for an individual project. Does the make sense?
>
> Your thoughts and help would be greatly appreciated.
>
> Just let me nkow if you need any more information.
>
> Rich

Hi Geoff,

That certainly give me somthing to ponder. Thanks for that, I can see where
your going with it.

I'll let you know how I get on.

many thanks,

Richard

"Geoff" wrote:

> Hi Richard
> This might point you in the right direction
> The core of the solution I used recently doing a similar thing was Instr
>
> Set tbl = Range("A1:A10")
> For each c in tbl
> 'Get number of items in list and first and last row number
> If Left(c, InStr(c, "-") - 1) = Left(orderID, InStr(orderID, "-") - 1) Then
> sameidcntr = sameidcntr + 1
> If sameidcntr = 1 Then firstsameid = c.Row
> lastsameid = c.Row
> End If
>
> firstsameid to lastsameid is the range of the outer index you are seeking.
> Then do:
>
> Set tbl = Cells(lastsameid + rwcntr, Range("A1:A10").Column)
> 'Now add 1 to last inner index
> newID = Left(tbl, InStr(tbl, "-") - 1) & "-" & Right(tbl, Len(tbl) -
> InStr(tbl, "-")) + 1
> neworderrow = lastsameid + 1 + rwcntr
> rwcntr = rwcntr + 1
>
> I was inserting a new line for each new id generated but this gives you the
> general idea.
>
> HTH
>
> Geoff
>
>
> "Richard Setford" wrote:
>
> > Hi there,
> >
> > Bit of an Access/Excel newbie here in need of some help. First time in here
> > so please be gentle with me.
> >
> > Here's what I'm trying to achieve. I want to build a database which records
> > the changes on a project with each project having it's own unique code based
> > on the year so for example:
> >
> > 05666
> > 04555
> > 03444
> >
> > I've build the database itself and created an Excel front end form to input
> > the data to the database. What I would like to achieve is for each project
> > number to have it's own sequencial record number for example:
> >
> > 05666-1
> > 05666-2
> > 04555-1
> > 04555-2
> > 04555-3
> > 03444-1
> > 03444-2
> >
> > you get the idea. How do I do this? It's really stumped me. I guess I really
> > need to know what VBA to put into the Excel form to look at teh last number
> > allocated to a project and just add one. If this can be done automagically on
> > selecting the required project then all the better.
> >
> > I can get Access to allocate a sequential number to each new entry but not
> > to each new entry for an individual project. Does the make sense?
> >
> > Your thoughts and help would be greatly appreciated.
> >
> > Just let me nkow if you need any more information.
> >
> > Rich