autonumber question

[Guest]

I have a table called tblProject with a primary key JobNumber. Another
table, called tblPunchItems has fields JobNumber and PunchID on the "many"
side of a one-to-many relationship with tblProject. So there can be many
PunchID's for one JobNumber. Example:

JobNumber PunchID
06-5788 001
06-5788 002
06-5788 003
06-5788 004
06-5790 001
06-5790 002
06-5790 003
....and so on...

Is it possible to utilize the AutoNumber property for the PunchID field and
have it start at 001 for each JobNumber? If not, is there perhaps some code
that can be written to create the next available PunchID? As it currently
stands, users enter the next PunchID manually, but it would be nice to have
the database assign the next available number autmatically.

Any feedback is greatly appreciated!

 

[jahoobob via AccessMonster.com]

Autonumber is a number and numbers in Access don't have leading zeroes. You
can autonumber the PunchID and add the leading zeroes in queries, forms, or
reports.

 

[Guest]

That certainly makes sense now! But what about my question about starting
from 1 for each JobNumber?
Thanks again!

 

[jahoobob via AccessMonster.com]

You will probably be adding this data in a form for the order and the parts
would be in a subform linked by the OrderID
You parts table fields should be at least OrderID, PartNo, PartDescription,
LineNo, etc.
In the subform, add a field called LinCount in the header and set Visible to
No
In you forms On Load property place this code:
Private Sub Form_Load()
Me!LinCount = 0
End Sub

Place this code:
Private Sub LineNo_GotFocus()
Me!LinCount = Me!LinCount + 1
Me!LineNo = Me!LinCount
End Sub
in the On Got Focus property of the LineNo field. You can dress it up to add
the leading zeroes. Whenever you add a new part to the order when you get to
LineNo it will increment by one.

Hope this helps,
Bob

That certainly makes sense now! But what about my question about starting
from 1 for each JobNumber?
Thanks again!

Autonumber is a number and numbers in Access don't have leading zeroes. You
can autonumber the PunchID and add the leading zeroes in queries, forms, or

[quoted text clipped - 22 lines]