Adding a Auto increment Text/Number (1 Viewer)

elliotth123 · Jan 4, 2005

I have a daabase that I am importing via excel. I want the entries to be numbered
ex: MT0001
MT0002
etc....

I would like it to promt the user for the last number(or next number in sequence) entered, then fill in the blank records with the next increment number.
The prefix will also change to so eventually the user would enter into the prompt RD0001. then autofill the 140 imported records with RD0002, rd0003... etc....

I can't really make seperate fields because the MT0001 number will become a barcode and putting them seperate causes many issues.

can this be done? Any help would be great I am still a beginner but slowly learning!
Thanks!

FLabrecque · Jan 4, 2005

This could be possible, through VBA. I'm not sure this would be the fastest or more normalize, but it should get the job done.

1. Import the Excel spreadsheet into Access with an autoincrement ID number (supposing your Excel file doesn't contain the MT001, MT002).
2. Add a column to the imported table.
3. Update the column, set NEWCOL = 'MT' & format(ID,'0000').

Et voila!

FLabrecque · Jan 4, 2005

Another possiblity could be to simply format it in the queries. For example:

SELECT 'MT' & Format(ID, '0000') as MyID
FROM ...

elliotth123 · Jan 4, 2005

Help!

I kind of understand what you are saying but, this won't allow it to prompt the user for the prefix.
What about an update query that will take the first blank record and insert an id (it will get this via a txt box on a form). Then increment the number portion until the null boxes are gone. Then for the next section that gets imported it will ask again for the first number to input tehn do the same increment the number that has been input by the user till all null vales are full.
Is this possible? ANy ideas how?

Pat Hartman · Jan 4, 2005

You'll have to do the insert with VBA rather than an append query in order to prompt and generate the appropriate numbers.

I recommend two columns also. You can concatenate the two fields in a query whenever you need them together for a barcode.

elliotth123 · Jan 5, 2005

So far,....

Here is what I have done with it so far:
I created 2 more fields IDSub and IDNum.
Then I created an update querey and form that will prompt the user for the prefix (IDSub) and fill in the newly imported records IDSub field with the prefix.
I have also created an update query to go through an make the ID number equal IDSub & IDNum.
My only question is now....how can I prompt the user to input a number, go to the first blank field under IDNum and insert that num. Then increment that number for the rest of the records that are alreday in existance.

Any ideas? I am trying!!!! Thanks everyone!

elliotth123 · Jan 6, 2005

Anyone?

Anyone on this last post?

Pat Hartman · Jan 6, 2005

You can prompt the user for input with an InputBox. Search for posts regarding autonumbering in queries or ranking. I haven't looked at the sample database recently so I'm not sure that the ranking samples can be "seeded" but take a look. Microsoft has a number of sample databases available for download. Search for the one for queries. This Post by me may have the link you need.

elliotth123 · Jan 6, 2005

Help!

Nope.... nothin there helpful unfortunatly... anyone?

Pat Hartman · Jan 7, 2005

I see the link to the queries sample db there. Did you download it and look at it?

Pat Hartman · Jan 7, 2005

In fact, here's the list of query examples.

Code:

Database of Access 2000 sample queries
The sample queries (query: A question about the data stored in your tables, or a request to perform an action on the data. A query can bring together data from multiple tables to serve as the source of data for a form, report, or data access page.) in this database demonstrate a number of querying techniques, including how to rank records within a query, how to group one set of records and sort on another, and how to use custom functions in queries.

Download the sample query database

Want to make sure this download is right for you? Here's a list of the sample queries.

Create a make-table query with a union query 
Change column headings in a crosstab query using code 
Create a crosstab query with multiple fields 
Group column headings in a crosstab query 
Use IIF() in crosstab queries to limit column headings 
Create a join on a range of values 
Create a parameter query that accepts a list of values 
Filter a query using an option group 
Return all records when a query parameter is blank 
Sort a query using an option group 
Sort and group in a query using a portion of a field's value 
Use a custom function in a query 
Use a query to filter for unique data 
Use a subquery as a criterion in a Select query 
Use the DLookup() function inside a query 
Query by form using Dynamic QueryDef 
Refer to a field in the previous or next record 
Create an SQL pass-through query using code 
Simulate parameters in an SQL pass-through query 
Create a union query 
Create running totals in a query 
Group on one field and sort on another in a query 
Extract the second-highest value from a table using a query 
Rank records within a query 
How to use the sample queries database

When you open the sample queries database, a switchboard appears. You can view all the sample queries or sort them by category. When you select a query, the switchboard displays a list of the tables and other database objects that the query uses, plus a link to a corresponding knowledge base article, if one exists. To use a query, select it and click Show Example.

elliotth123 · Jan 7, 2005

Help!

Yes I did download it and go through the queries but it was not was I was looking for, I still need a way of fiinding the first blank field of a column , insert a number (taken from an input form) and then increment that number by one till all blank fields (1 for each record) is complete.

mjain · Sep 13, 2006

Adding a Auto increment Text/Number in word

GraceGrady · May 17, 2013

I had a similar situation.

I created a table to hold the number (or next number), when I grab the number, I increment the table.

Public Function GetNextNumber() As Variant
Dim rs As Recordset

Set db = CurrentDb
Set rs = db.OpenRecordset("MyNumbers")

With rs
rs.MoveFirst
GetNextNumber = "RS-" & rs!MyNextNumb1
rs.Edit
rs!MyNextNumb1 = rs!MyNextNumb1 + 1
rs.Update
End With
Set db = Nothing
Set rs = Nothing

End Function

Adding a Auto increment Text/Number (1 Viewer)

elliotth123

Registered User.

FLabrecque

Registered User.

FLabrecque

Registered User.

elliotth123

Registered User.

Pat Hartman

Super Moderator

elliotth123

Registered User.

elliotth123

Registered User.

Pat Hartman

Super Moderator

elliotth123

Registered User.

Pat Hartman

Super Moderator

Pat Hartman

Super Moderator

elliotth123

Registered User.

mjain

New member

GraceGrady

New member

Similar threads

Users who are viewing this thread