Sequencial numbering

[Richard Setford]

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

 

[Geoff]

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]

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