Access VB Code to assign AutoNumbering Field to a Table.

[KPR1977]

Does anybody know a VB code that would populate the "AutoNumber" field each time it is run for my "Transpo" table in the manner depicted below?

I need the AutoNumber field to reset at the begining of each new group under the "Maker" field. It should be in ascending order by Type.Transpo, Maker.Transpo, Model.Transpo

• Table: Transpo
• Field1: Type
• Field2: AutoNumber
• Field3: Maker
• Field4: Model

Type___AutoNumber______Maker______Model
Car_________1__________ Chevy______Beretta
Car_________2__________ Chevy______Corsica
Car_________3__________ Chevy______Malibu
Car_________1__________ Ford_______Mustang
Car_________2__________ Ford_______Probe
Car_________1__________ Hyundai____Azera
Car_________2__________ Hyundai____Elantra
Car_________3__________ Hyundai____Sonata
Car_________4__________ Hyundai____XG
Truck_______1__________ Nissan______Titan
Truck_______1__________ Toyota______Tundra


Hope this makes sense. I'm pretty much a dummy when it comes to VB coding so I would greatly appreciate any help you can give me. Thanks so much!!!

[ADezii]

Does anybody know a VB code that would populate the "AutoNumber" field each time it is run for my "Transpo" table in the manner depicted below?

I need the AutoNumber field to reset at the begining of each new group under the "Maker" field. It should be in ascending order by Type.Transpo, Maker.Transpo, Model.Transpo

• Table: Transpo
• Field1: Type
• Field2: AutoNumber
• Field3: Maker
• Field4: Model

Type___AutoNumber______Maker______Model
Car_________1__________ Chevy______Beretta
Car_________2__________ Chevy______Corsica
Car_________3__________ Chevy______Malibu
Car_________1__________ Ford_______Mustang
Car_________2__________ Ford_______Probe
Car_________1__________ Hyundai____Azera
Car_________2__________ Hyundai____Elantra
Car_________3__________ Hyundai____Sonata
Car_________4__________ Hyundai____XG
Truck_______1__________ Nissan______Titan
Truck_______1__________ Toyota______Tundra


Hope this makes sense. I'm pretty much a dummy when it comes to VB coding so I would greatly appreciate any help you can give me. Thanks so much!!!

You cannot have a pure AutoNumber Field in Access act in this fashion.

[bdockery]

Seems to me like you would use multiple tables, relationships, and foreign keys to do what you want to do.

[KPR1977]

Seems to me like you would use multiple tables, relationships, and foreign keys to do what you want to do.

Yes, but I'm trying to avoid that by using a VB code.

[MMcCarthy]

Yes, but I'm trying to avoid that by using a VB code.

The autonumber field can't be reset.

If you have duplicate values this field can't be used as a primary key.

It is not a good idea to try to bypass in any way the correct table structure design for a database.

Mary

[NeoPa]

Does anybody know a VB code that would populate the "AutoNumber" field each time it is run for my "Transpo" table in the manner depicted below?

I need the AutoNumber field to reset at the begining of each new group under the "Maker" field. It should be in ascending order by Type.Transpo, Maker.Transpo, Model.Transpo

• Table: Transpo
• Field1: Type
• Field2: AutoNumber
• Field3: Maker
• Field4: Model

Type___AutoNumber______Maker______Model
Car_________1__________ Chevy______Beretta
Car_________2__________ Chevy______Corsica
Car_________3__________ Chevy______Malibu
Car_________1__________ Ford_______Mustang
Car_________2__________ Ford_______Probe
Car_________1__________ Hyundai____Azera
Car_________2__________ Hyundai____Elantra
Car_________3__________ Hyundai____Sonata
Car_________4__________ Hyundai____XG
Truck_______1__________ Nissan______Titan
Truck_______1__________ Toyota______Tundra


Hope this makes sense. I'm pretty much a dummy when it comes to VB coding so I would greatly appreciate any help you can give me. Thanks so much!!!

It seems like you're confusing the Autonumber field type with a simple numeric field which happens to be populated automatically.
The Access Autonumber field doesn't behave wrongly. It actually works perfectly as designed. It was designed that way because the designers knew what they were about.
Your field won't be done automatically by Access (as I'm sure you've realised by now) but you can put in some VBA code to handle this in a form if you like.