Auto increment when the form opens

[Lynn]

Hi,
I have a form with a subform. I don't want to use autonumber for my [ReferralNumber] field because I don't want the records to start at 1. But I would like to have the field autopopulated with a number each time I open a new form. So if I decided to have the referral number begin at 100 and increment by 1 with each new form that opens how would that look?
Thanks,


Lynn

[maximus]

Reading your thread I assume you have the following requirement:

you have a Main form with a subform you want a field on the main form to autoincrement from 100 (Not AutoNumber) and the Subform will be linked to this field.

This is What I have done:

1) I have two tables increment and sub.
2) Main form increment is opened in dataentry mode.
3) RegID is incremented from 100 onwards.
4) Subform link field = RegID Main form.

Code used:

On the OnLoad Even of the Increment Form:

intIncrementalNumber = IIf(IsNull(DMax("[RegID]", "increment")), 100, DMax("[RegID]", "increment") + 1)
Me.RegID = intIncrementalNumber

How to check:

When ever you open the incremental form the RegID is incremented.

if this solves your problem mark this thread solved.

[Lynn]

Thanks Maximus, looks great...I can't seem to open the forms in your zip file. Something about a run time error.
Lynn

[TheShabz]

I was able to open it fine (3rd party input ftw). Are you sure you extracted it first?

[pbaldy]

You can always start an autonumber field at your given number if you want. That assumes you can live with the autonumber's limitations. The DMax method is better if you can't have any skipped numbers.

[Lynn]

Hi,
Still couldn't your form--run time error keeps showing up but got it to work on my main form. Thing is, I'd like for it to only increment on new blank forms. Each time I open an existing record, it increments. Sorry, should have mentioned that earlier.

[pbaldy]

If you stay with that method, switch to the current event and include this test:

If Me.NewRecord Then

[maximus]

the trick is to import the datavase objects on to another blank access database. Glad you could make it work. As Sir Paul pointed out it you want the numbers to increment for a new record use


If Me.NewRecord Then
Followed by the code.


I had opened the main form in dataentry mode and had no navigation buttons so it work on the onload event. i am re-attaching the mdb again.

[maximus]

mark the thread solved.

[Datagopherdan]

Just something to keep in mind with this setup. You can end up with alot of blank records if you have a new record being inserted each time the form is open unless you have some type of procedure to prevent that. Just food for thought. Good luck.

Dan
Access Development

[Lynn]

You can always start an autonumber field at your given number if you want. That assumes you can live with the autonumber's limitations. The DMax method is better if you can't have any skipped numbers.

Hi Paul,
Can you tell how to set this up? I'm not worried about skipped numbers and the DMax method is just not working like I want it to. I added a switchboard and autoexec to open a blank record and it won't increment and I'd eventually like to add a command button that adds a new record and I'm sure it wouldn't increment with that either. The autonumber method seems much simpler.
Thanks,
Lynn

[pbaldy]

Once your table is set up, append a record into it, populating the autonumber field with the number previous to the one you want it to start with (99 for instance). Then delete that record, but don't compact/repair the database. The next record added to the table will have an autonumber of 100.

INSERT INTO TableName (FieldName)
VALUES (99)

[maximus]

This is how I can make an autonumber Field start from any number I want.
Suppose I have a Table1 with a autonumber Field aa. I want to start the number sequence to begin from no 100. Follow the steps carefully.

assuming that you are just starting to build the database.

Note: unjoin all relationship with the table on which you will carry out this exercise on.

Note: If your autonumber field in the original table is a primary key or any primary key, you must temporarily remove the primary key before running the append query. Also, if your original table contains fields that have the Required property set to Yes, the Indexed property set to Yes (No Duplicates), or field and/or record ValidationRule property settings that prevent Null entries in fields, you must temporarily disable these settings

1) copy Table1 structure only name it Table2.
2) Change the the autonumber Field aa to Number (set its FieldSize property to Long Integer ) in Table 2.
3) Make a Dummy entry in Table2 aa=99
4) Now make a append query. In the append to option select Table1. Select Table 2 from the table list and append aa of table 2 to aa of table 1.
5) Open Table 1 and Delete the dummy entry appended in it. Now when you start data entry you autonumber begins from 100.

This will alter the autonumber sequence and make it start from 100.

if you have data in the table make two copies only the structure. Follow the steps. after autonumber is adjusted. append the data from the original table into the adjusted table. remember while appending data append all fields other than the autonumber field. Delete original table. rename the new table with adjusted autonumber as the deleted table. Set up relationships and other properties that you had disabled.

remember if you want to compact and repair the database do it after you have entered at least one entry in the new adjusted table.


I was rather surprised that you could not get the DMax method going. From the last thread I assume that you have put up a swithchboard, if you are opening the form the Dmax code could have been put the code onto the onload event of the form. in this case the Form dataentry property has to be set to yes to ensure that a blank form opens all the time. Remember if the form open with all the entries it will modify the Field of the first entry of the form. This can be also set to the onCurrent even of the form like Sir Paul pointed out but with the if condition

If Me.NewRecord Then

in this case you may open the form to show all entries as number in the field will only be incremented if a new record is opened.


Then the same code could have been added to the new button on the form.

anyways guess I have wasted enough time on this thread

[Lynn]

Paul, Thanks much for your help.

Maximus, I apologize if my questions are too simple for you. Clearly your expertise is being "wasted" on my inquiries. I hope I misunderstood your comment.