access+training - Primary key with high start number

Resolved · Low Priority · Version Standard

Wed 3 Jan 2007, 08:52 Delegate Mark said...

Mark has attended:
Access Intermediate course
Access Advanced course
Access VBA course

Primary key with high start number

In a new database can I start a primary key autonumber on a number other than 1, I want to start it at 3000. If so how?

Wed 3 Jan 2007, 11:01 Trainer David said...

RE: Primary key with high start number

Hi Mark

Yes this is possible, if you follow the steps outlined below you will be able to do it.

1)Make sure that there is no primary key field set in your database table.
2)Create a new temporary table, within this table create a field with the SAME name as the id field in the original table. In this field enter a number 1 less than you require for your auto number start i.e. 2999.
3)Create a Query in design view and add both the tables.
4)Right mouse click in the grey area where the two tables are shown within the query window. From the menu select Query Type and then Append Query.
5)Within the window that appears, select the original table (the table that you want the auto number to start at 3000) from the drop down list and click OK.
6)Select the one single field that you created in the new table so that it is added to the query. To do this, double click the field.
7)Run the query
8)Open up the original table and when you create a new row, you should see that it starts the numbering from 3000.
9)Set this field as the primary key
10)Delete the temporary table that you created.

Hope this helps

David

Wed 3 Jan 2007, 12:30 Delegate Mark said...

RE: Primary key with high start number

Yes it worked, thank you.

Training courses

	98.8 % recommend us
	More live stats...

Training information:

Access tip:

Create a custom Close button on a Form

By default, Forms that you create in Access have a full set of control buttons, that is, a title bar with Minimize, Maximize and Close buttons in the top right-hand corner and a Control Menu in the top left-hand corner.

Sometimes Access needs to follow certain procedures or events as users close a Form (such as automatically opening another Form perhaps). So it is often a good habit to create a custom Close button on a Form for everyone to use, rather than the standard closing cross.

To create a custom Close button:

1) In Form Design View, draw a Command Button onto the Form.

2) Use the Command Button Wizard to select Form Operations under Categories, and Close Form under Actions.

3) Choose Finish.

Access then creates the button and code to close the form for you. You can add any extra code that needs to run as the Form closes to the same Close procedure.

However, the creation of a custom Close button is pointless if the closing X in the top right-hand corner of the Form remains active. To make the closing X inactive, open the Form Properties box then on the Format tab set the Close Button setting to No. This will grey out the closing X leaving it visible, but inactive.