I'm trying to take a simple database I created to track barcode #s and make
it so when a new record is created, a specific barcode number is assigned to
the new record. I have a list of barcode #'s (18 digits, the first 8 are
actual numbers and the following 10 are all zeros). What I'd like to do is
somehow tie this list to the main table so that when a new record is created,
it pulls the next available barcode number and assigns it to this new record.
From the Access Help file:
For a new table that contains no records, you can change the starting
value of an AutoNumber 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
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 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 to append the temporary table to the
table whose AutoNumber value you want to change.
How?
Create a query that contains the table whose records you want to
append to another table.
How?
In the Database window 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 and if you want,
specify criteria and a sort order.
To view the query's results, click View on the toolbar.
In query Design view 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.
Click OK.
Drag from the field list to the query design 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 you'll need to add all the fields
instead.
If you have a field with an AutoNumber data type, do one of the
following:
Add AutoNumber values automatically
To have Microsoft Access add AutoNumber values automatically, don't
drag the AutoNumber field to the query design grid when you create the
query.
With this method, Access appends records and automatically inserts
AutoNumber values. The first record appended has a value that is one
larger than the largest entry that was ever entered in the AutoNumber
field (even if the record that contained the largest AutoNumber value
has been deleted).
Use this method if the AutoNumber field in the table you're appending
to is a primary key and the original table and the table you're
appending to contain duplicate AutoNumber values.
Keep the AutoNumber values from the original table
To keep the AutoNumber values from the original table, drag its
AutoNumber field to the query design grid when you create the query.
If the fields you've selected have the same name in both tables,
Microsoft Access automatically fills the matching name in the Append
To row. If the fields in the two tables don't have the same name, in
the Append To row, enter the names of the fields in the table you're
appending to.
In the Criteria cell for the fields that you have dragged to the grid,
type the criteria on which additions will be made.
To preview the records that the query will append, click View on the
toolbar. To return to query Design view, click View on the toolbar
again. Make any changes you want in Design view.
Click Run on the toolbar to add the records.
Delete the temporary table.
Delete the record added by the append query.
If you had to disable property settings in step 1, return them to
their original settings.
When you enter a record in the remaining table, Microsoft Access uses
an AutoNumber field value one (1) greater than the value you entered
in the temporary table.
HTH.
Regards,
Ross
