Full Version: Creating autonumber using ADO with unbound forms
UtterAccess Forums > Microsoft® Access > Access Forms
I was trying to create an autonumber for each record.The technics i used was a combination of unbound form + ado recordset and the technic i used is as following
1-Find the max number in the target table (which i want to insert the record to),using sql select statement.
2-Add 1 to the retrieved max value.
3-Add the code to the "before update property" of the form.
But I am getting a duplicate error message which reflects that the number i retrieved is used.I have searched the access developers resource home page but couldnt find any.
How to use ado and auto numbering....I know the way it waorks with local tables or linked tables.But slooking for using it with ado and unbound forms.
Any help will be apprecieated.
What do you mean by unbound forms? How are you adding a record to the table if the form is unbound? Post the code behind your before update event.
Here is a code that will create a sequence number.
NewID = Nz(DMax("YourField", "YourTable"), 0) + 1
Well, unbound forms refer to forms that their record source property is not based on data object like table/query.
And this technic has a lot of advantage when working with huge number of users and is recommended for multiuser applications.

Inorder to display records on such forms the on open property will include ADO/DAO record set code that will bind the forms recordsource property to the result it fetches.But keep in mind you cant bind ADO recordset to mdb reports I dont know why Microsoft couldnt solve this problem.

Your code works well if the form recordsourse is based on a local/linked data object such as table/query.but even you can add DMax("YourField", "YourTable") + 1 to the id field defaultvalue property and it will do the job.

I have attached a sample database that will clear the picture for you.

Any how, i am about to finish and get the wanted result done ... and i will publish it in some time today.

Thanks for your reply

Edited by: soma1958 on Tue Apr 19 1:01:26 EDT 2005.
I understand what unbound forms are and why to use them. I was not sure if you did (i.e. new member). Your process for getting the next number is correct (1,2,3) – but without seeing your SQL and the code for your before update event, I cannot assist you much. You mentioned “duplicate error message” – is the ID Filed you are incrementing the only index in that table? Is the field data type an AutoNumber? Is in Numeric?
Hi and welcome to UA soma1958,
You don't seem to have any autonumber field in your table tblCourses, nor any method to add records through code.
If you just want an autonumber to provide a unique ID for each record in the table you can just add an autonumber field to the table - Access will fill in the number on creation of a new record. You should not try to enter it explicitly. This will work even if you are inserting a new record using SQL.
However, if you need an incremental counter without gaps in the numbering then you can use Matt's suggestion as part of your SQL Insert statement.
Oh yah the databse example i attached was only for explaining unbound forms . As i mention in the early postingI am attaching now how to create a user defined auto number by code.
You might need such solution in a place that requires to proceed there sequential number and never start from 1.
other wise there is no need to develop such solution if the auto number will start from 1.
Ofyou want to test:-
1-Open table tblStudentRegistration in data sheet view and delete all records.
2- Open table tblStudentRegistration in design veiw and remove the studentid column.
3- Open frmstudentsRegistration and follow the instructions aas required.
Sorry for the delay and give your feed back.
Hi soma1958,
think I see what you are trying to do, though I'm not sure that you are going the best way about it.

You are not using an unbound form for your data entry - when you set the rs the form becomes bound.
If you want to use an autonumber for the StudentID field then you should let access handle it for you and not try to manipulate it. Your function 'AddAutoNumberField' will cause your users problems and will very likely mess up your tables.
There are 2 ways you can go about what you want:
1. Continue using the bound form
If you want StudentID to be an incermental number with no gaps you need to set the StudentID field to 'Number' (Long Integer or Integer) and NOT AutoNumber.
Then, in the default value property of textbox 'StudentID' (which I strongly recommend you change to 'txtStudentID') you can put:
=DMax("StudentID", "tblStudentRegistration") + 1
(See revised version of form 'frmstudentRegistration' in attachment)
2. Use a proper unbound form
A continuous form is not the best way to do this - use a single form for this with an 'Add' button.
Have all the fieds unbound (ie no ControlSource) and use ADO and SQL to write to the table.
I have done a little example for you using an unbound form to explain the process. (See frmAddContact in the attachment). It shows two methods of inserting a new record using ADO.
Hi Budha

Thanks for the help .I have come to the altimate solution I was looking for .Your solution is perfect but it is not the one i need for multiuser entry unbound form.

Odroped the autonumber concept and I came up with self incremental integer number.It works fine with continuous forms and even if there are more than one user entering the registration details on the same time.It has also the facility of self choosing the starting registartion number which also was one of my goals.

To test please the attached database:-

1- Start from "frmmain"
2- Delete all the records and start from entering student name.

Thanks and many thanks and your feed back is appreciated.
Edited by: soma1958 on Tue Apr 26 2:31:21 EDT 2005.
Hi Soma,
Looking good...
Glad you found a solution.
Good luck with the rest,
I have a similar problem to this, I have two tables 'Analogue' and 'Batch'. The relationship is each analogue has many batches. I want each batch (for each analogue), besides having a unique ID to be labelled sequentialy as records are added 1,2,3,4 etc. There is a field for this in batches labelled StockNumber.
I have tried adapting some of the code attached to eariler messages and can get it to add sequential numbers for batch, however this doesn't take account of the Analogue sie of the equation. In other words each batch isn't numbered from 1. I suspect the SQL statement in the code needs to be modified but when I add a WHERE statement I get errors.
Would appreciate an advice or alternative methods for acheiving this.
Hi Rob,
Welcome to UA!
I'm not sure what your sql statement is, but you can probably achieve what you want by adding criteria to the DMax function.
I am assuming that table 'Batch' looks something like this:
BatchID - Autonumber, Unique ID for each record in table
AnalogueFK - Long Integer, foreign key to relate to each record in table 'Analogue' (AnalogueID)
StockNumber - Integer, incrementing number for each batch record for each 'Analogue' record
You need to pass the required AnalogueID to the DMax function. Are you doing this from a form? If so, I presume there will be a textbox that contains the AnalogueID.
You could use something like:
= DMax("StockNumber", "Batch", "AnalogueFK = " & Forms!frmAddAnalogueBatch!txtAnalogueID) + 1
If you are doing it differently explain the process you are using and your sql statement, or zip up and attach your db (with sensitive data removed) and I will have a look.
Thanks ... that worked a treat. I now have it set so when you enter the Name of the Batch the stock number is calculated by the formula.
Hi Rob,
Glad you got it working thumbup.gif
This is a "lo-fi" version of UA. To view the full version with more information, formatting and images, please click here.