By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,486 Members | 2,061 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,486 IT Pros & Developers. It's quick & easy.

Access VB Code to assign AutoNumbering Field to a Table.

P: 23
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!!!
Feb 23 '07 #1
Share this Question
Share on Google+
5 Replies


ADezii
Expert 5K+
P: 8,597
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.
Feb 23 '07 #2

P: 39
Seems to me like you would use multiple tables, relationships, and foreign keys to do what you want to do.
Feb 23 '07 #3

P: 23
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.
Feb 23 '07 #4

MMcCarthy
Expert Mod 10K+
P: 14,534
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
Feb 24 '07 #5

NeoPa
Expert Mod 15k+
P: 31,186
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.
Feb 26 '07 #6

Post your reply

Sign in to post your reply or Sign up for a free account.