autonumbering of a field with several categories

[sonny minor]

hi guys, im new in microsoft access and i got stuck with some problems.
i created a table called inventory transaction and the fields are 'transaction No (PK)' "transaction reason', 'transaction date','items', etc.
Here's my areas of concern.
1. transaction code consist of several categories such as 'production' and 'purchases', if i select "production" as my transaction reason, transaction No will start with 1. and if i select "purchases", transaction no. should also starts with 1. in short Transaction No sequence should be separate for the 2 categories

tnx a lot

[NeoPa]

In these circumstances it is not AutoNumbering you need. It's explicit, controlled setting of the number instead.

We can help with this but could you first explain where Transaction Code fits in? You gave a list of fields, then proceeded to explain your problem with reference to a field you hadn't included. This muddies the waters rather and I don't want to proceed with a fuzzy understanding of your problem as that way leads to problems and confusions.

This will work much better if you can post the meta-data (info about the layout / structure) of the table in the same way as I use in my example. Click on the Reply button and you will have access to all the codes I've used. PK & FK stand for Primary Key & Foreign Key respectively. Please don't use any TABs in your formatted layout as this will not display correctly - Spaces only for layout.
Table Name=[tblStudent]

Field           Type        IndexInfo
StudentID       AutoNumber  PK
Family          String      FK
SName           String
University      String      FK
Mark            Numeric
LastAttendance  Date/Time

[sonny minor]

tnx neo, these are some info abt my proj.
Table=[Inventory Transaction]

Field               Type
Transaction Code    autonumber  PK
Transaction Reason  text
Transaction Date    date
Item Code           text
Transaction Qty     number

I have 2 transaction reasons which are "production" and "puchases". i want each of them to have separate transaction code numbering/sort

tnx

[NeoPa]

So. [Transaction No] doesn't exist at all. Please be more careful in future.

As for your question, the [Transaction Code] field needs to be changed to a Number / Long Integer field and something else added into your PK. A PK MUST be unique. You would probably want to add the [Transaction Reason] into the PK such as :

Field               Type
Transaction Reason  Text    PK (Composite)
Transaction Code    Number  PK (Composite)
Transaction Date    Date
Item Code           Text
Transaction Qty     Number

The new setting for each record would be determined in the code of your form as (assuming TextBox controls of txtTranCode & txtTranCode) :

txtTranCode=Nz(DMax("[Transaction Code]", _
                    "[Inventory Transaction]", _
                    "[Transaction Reason]=' & txtTranReason & "'")

I hope this clarifies things a bit for you.