For a college assignment, I'm currently creating a database in MS Access to be used in a Veterinary Surgery. I know how to put in the fields, text type, know how to change the fields etc. But what I want to know is how to change the ID field so that it starts at 1001 instead of 1. Can anyone help?How do I change ID numbers in MS Access 2007?
In design view, click on the ID field, then down the bottom to the format row. type 1000 then switch view back to datasheet view and save table as prompted. Your autonumbers will now become 1001 onwards.How do I change ID numbers in MS Access 2007?
Best way to do this would be to create a query
Use this in the in the field line of the query
Replace id with your ID field name
Format([id],"1000")
Add any other fields to the query that you need for your form
Now just use this query as the record source to your form.
Hope this helps
For FREE Access ebook and videos click here
http://access-databases.com/ebookHow do I change ID numbers in MS Access 2007?
For a new table that contains no records, you can change the starting value of an AutoNumber (AutoNumber data type: In a Microsoft Access database, a field data type that automatically stores a unique number for each record as it's added to a table. Three kinds of numbers can be generated: sequential, random, and Replication ID.) field whose NewValues property is set to Increment to a number other than 1. For a table that contains records, you can also use this procedure to change the next value assigned in an AutoNumber field to a new number.
If your original table contains property settings that prevent Null (Null: A value you can enter in a field or use in expressions or queries to indicate missing or unknown data. In Visual Basic, the Null keyword indicates a Null value. Some fields, such as primary key fields, can't contain Null.) values in fields, you must temporarily change those properties. These settings include:
The Required field property set to Yes
The Indexed field property set to Yes (No Duplicates)
A field and/or record ValidationRule property that prevents Null values in fields
Create a temporary table with just one field: a Number field. Set its FieldSize property to Long Integer and give it the same name as the AutoNumber field in the table whose value you want to change.
In Datasheet view (Datasheet view: A window that displays data from a table, form, query, view, or stored procedure in a row-and-column format. In Datasheet view, you can edit fields, add and delete data, and search for data.), enter a value in the Number field of the temporary table that is one (1) less than the starting value you want for the AutoNumber field. For example, if you want the AutoNumber field to start at 100, enter 99 in the Number field.
Create and run an append query (append query: An action query that adds the records in a query's result set to the end of an existing table.) to append the temporary table to the table whose AutoNumber value you want to change.
Create a query that contains the table whose records you want to append to another table.
How?
In the Database window (Database window: The window that appears when you open an Access database or an Access project. It displays shortcuts for creating new database objects and opening existing objects.), click Queries under Objects, and then click New on the Database window toolbar.
In the New Query dialog box, click Design View, and then click OK.
In the Show Table dialog box, click the tab that lists the tables or queries whose data you want to work with.
Double-click the name of each object you want to add to the query, and then click Close.
Add fields to the Field row in the design grid (design grid: The grid that you use to design a query or filter in query Design view or in the Advanced Filter/Sort window. For queries, this grid was formerly known as the QBE grid.), and if you want, specify criteria (criteria: Conditions you specify to limit which records are included in the result set of a query or filter.) and a sort order.
To view the query's results, click View on the toolbar.
In query Design view (Design view: A window that shows the design of these database objects: tables, queries, forms, reports, macros, and data access pages. In Design view, you can create new database objects and modify the design of existing ones.), click the arrow next to Query Type on the toolbar, and then click Append. The Append dialog box appears.
In the Table Name box, enter the name of the table you want to append records to.
Do one of the following:
If the table is in the currently open database, click Current Database.
If the table is not in the currently open database, click Another Database and type the path of the database where the table is stored or click Browse to locate the database. You can also specify a path to a Microsoft FoxPro, Paradox, or dBASE database, or a connection string to an SQL database (SQL database: A database that is based on Structured Query Language (SQL).).
Click OK.
Drag from the field list (field list: A window that lists all the fields in the underlying record source or database object, except in data access page Design view. In data access page Design view, it lists all the record sources and their fields in the underlying database.) to the query design grid (design grid: The grid that you use to design a query or filter in query Design view or in the Advanced Filter/Sort window. For queries, this grid was formerly known as the QBE grid.) the fields you want to append and any fields you want to use for setting criteria.
If all the fields in both tables have the same names, you can just drag the asterisk (*) to the query design grid. However, if you're working in a database replica (replica: A copy of a database that is a member of a replica set and can be synchronized with other replicas in the set. Changes to the data in a replicated table in one replic
