i have a access database that has several tables in it and one of it is called NewOrder. i have to use Visual Studit 2002 in a visual basic .net environement to test several functions including one known as PlaceOrder.

in the NewOrder table, there are 4 fields, which are OrderNo, OrderCustNo, OrderPRodCode and OrderQty.

the system is supposed to be a 3 tier application but i am just asked to test a data layer which i had been provided and then create a business layer component (dll) and then test that my business layer component works. the problem is the function of PlaceOrder has all the parse in parameters for the 4 fields

function PlaceOrder (ByVal OrderNo as String, ByVal OrderCustNo as String, ByVal OrderProdCode as String, ByVal OrderQty as Integer)


but the input data that i am asked to do as in keyboard read from user input are just OrderCustNo, OrderProdCode and OrderQty

i was told OrderNo is to be autogenerated by the system by values like O1, O2 O3, O4 and so on. and OrderNo is the primary key for this table

how am i supposed to do that in my coding or using SQL to trigger an auto generate OrderNo value to its field by that format without a user layer?

i know i can do that if i have a User Interface which i could use the user layer to add macro to a button that once clicked generate an event procedure to do Before Insert but since i am not provided with a user layer and user interface, and i am not supposed to create one; so how is it possible to trriger the database to auto generate a new row of Order with a autogenerate OrderNo???

i know in Oracle in can do that by something called Before Insert that is when the user is typing the CustNo, ProdCode and OrderQty, the OrderNo is generated in the background.

but i checked with Access help, it can only do Before Insert, Before Update, After Insert and After Update all by using a Graphic User Interface with macro and event procedure.

i had attached the orders.mdb in a zip file here

- PLEASE ENLIGHTEN ME ! -

I presume that you are using ADO to add the record so after the .Update, you can simply reference the generated autonumber. At least that's the way it works when using ADO from VBA.